Click here for important information about

Copy specific columns from multiple csv and merge to one csv

April 28, 2020 at 11:49:18
Specs: Windows 7
I have multiple CSV files that have upwards of 150+ columns; but I only need 8 columns from each file combined into one csv file. The header of the 8 fields are the same in all of the files. I would like to do this via Batch script. I need to maintain as CSV as some of the files have row counts that are beyond 1 mil.

See More: Copy specific columns from multiple csv and merge to one csv

April 29, 2020 at 01:56:40
I'll work on it. It may be beyond the reach of a batch.



message edited by Mechanix2Go

Report •

April 29, 2020 at 06:06:48
"some of the files have row counts that are beyond 1 mil."

do they exceed 2^31-1 OR 2147483647 to be specific? that would be batch's limit.

set/a 2147483647+1


Report •

April 29, 2020 at 10:34:45
I was able to modify nbrane's script and got it to work. But i did notice that if any rows of a column were null, then the next column would populate in that column; basically shifting the row out of line.
I'd like to solve that issue: like a validation check.

Here is my code (generic):

@echo off & setlocal
SET mypath=C:\path01\
SET myexppath=C:\path02\
set /p x=<C:\path01\myInfile.csv
set c=1
call :split %x%
echo %field01% : %field02% : %field03% : %field04% : %field05% : %field06% : %field07% : %field08%
(for /f "tokens=%field01%,%field02%,%field03%,%field04%,%field05%,%field06%,%field07%,%field08% delims=," %%a in (%mypath%myInfile.csv) do echo %%a,%%b,%%c,%%d,%%e,%%f,%%g,%%h)>%myexppath%myOutfile.csv
goto :eof
REM pause
if "%~1" equ "" goto :eof
set %~1=%c%
set /a c+=1
goto :split

Report •

Related Solutions

April 29, 2020 at 13:37:46
Ha.. ;-) no. None of them are that huge. I think the largest single file maybe 2-3 milion rows.

Report •

April 29, 2020 at 14:17:20
Have you tried using powershell?

Report •

April 30, 2020 at 14:28:37
Here's a version that tries to deal with null elements in the file. This bat would be called
for each csv file to be included in the 8-column extraction. f/e:
for %%a in (*.csv) do call csv_x "%%a" "colum x" "column y" "test one" "wat Ever"
:: csv_x.bat
@echo off & setlocal enabledelayedexpansion
if "%1" equ "" (
echo usage: %0 csv_filename "column a" "column b" ...
echo The items that are found in the column header of the csv file are used
echo as the basis for extracting the data from those columns to a new
echo csv file 'temp.csv'
goto :eof
set cols=
set out=temp.csv
set inn="%~1"
set /p q=<%inn%

set heads=%heads%%~1,
for /f "tokens=1 delims=:" %%z in ('^(for %%a in ^(%q%^) do @echo %%a^) ^| findstr /n /i "\<%1\>"') do set cols=!cols! %%z
if "%1" neq "" goto :next

(set cols=%cols% )
if not exist %out% >%out% echo %heads:~0,-1%
for /f "skip=1 tokens=*" %%a in (%inn%) do call :xx %%a
goto :eof

set c=0
set lineout=
set z=%*
:: fill in any null elements with "". could be zero or a flag-item
set z=%z:,,=,"",%
echo %z% | findstr "^,"&&set z=""%z%
echo %z% | findstr ",$"&&set z=%z%""
call :aa %z%
goto :eof

set /a c+=1
echo %cols% | find " %c% ">nul||goto :shft
rem if you want quotes removed: set lineout=%lineout%%~1,
set lineout=%lineout%%1,
if "%1" neq "" goto :aa
>>%out% echo %lineout:~0,-1%
:: end script

message edited by nbrane

Report •

May 10, 2020 at 14:33:08
Just wondering if you found a solution to this, and if not I might have a PowerShell script that can help:

$csv1 = import-csv "c:\file1.csv"
$csv2 = import-csv "c:\file2.csv"
$csv3 = import-csv "c:\file3.csv"

$Column1 = $csv1.Column1
$Column2 = $csv2.Column2
$Column3 = $csv3.Column3

$counter = 0

$result =
Column1 = $Column1[$counter]
Column2 = $Column2[$counter]
Column3 = $Column3[$counter]
}until($counter -eq $Column1.length)

$result | export-csv "c:\Final-File.csv" -NoTypeInformation -Force

Report •

June 18, 2020 at 09:34:42
I can't run powershell because I dont have admin rights to my machine.

Report •

June 18, 2020 at 10:01:30
nbrane I want to use your suggestion, but I think I'm missing how to use it. Also, I'm not sure where the script begins. Should I have 2 scripts or is it one?

Report •

June 18, 2020 at 23:03:42
It was kind of muddled, looking back on it. My bad! should have noted "begin script csv_x.bat" and put a blank line. Sorry.
It's really one main script, csv_x.bat or whatever name. It operates on one csv file per call. The arguments are the column-headers, in text, and enclosed in quotes, for each column you want to extract from the input-csv. Best is example. The input CSV file,call it "A.csv" is:

The first line is the column headers. You can see that the data-field can have nulls either quoted or not, but sep by commas. You call csv_x.bat with the input csv filename, followed by the headers whose columns you want to extract, whether null or not. Col head specs are non-case-sensitive:
call csv_x "A.csv" "zz" "ax" "y"
result, hardwired to 'temp.csv':
zz,ax,y --------- headers

So it can be two scripts, using a mass driver, or one stand-alone. All the driver script does is deliver a list of csv filenames or wildcarded, and quoted column-heads.. f/e:
for %%a in (*.csv) do (
call csv_x %%a "zz" "ax" "y"
move temp.csv
::------ end example
note that since temp.csv is hardwired, you have to save it in another name for each rep., or you'll only get one file at the end. So here saved to ''. That's all just typical mop-up stuff. Hope this helps.
Oh, this is start of main script csv_x:

:: csv_x.bat
@echo off & setlocal enabledelayedexpansion

message edited by nbrane

Report •

Ask Question