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

Reply ↓  Report •

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

=====================

M2

message edited by Mechanix2Go


Reply ↓  Report •

#2
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.

example:
set/a 2147483647+1
-2147483648

specs: https://www.userbenchmark.com/UserR...


Reply ↓  Report •

#3
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%
pause
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
:split
if "%~1" equ "" goto :eof
set %~1=%c%
set /a c+=1
shift
goto :split


Reply ↓  Report •

Related Solutions

#4
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.

Reply ↓  Report •

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

https://chris.koester.io/index.php/...


Reply ↓  Report •

#6
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%
shift

:next
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
shift
if "%1" neq "" goto :next

:xit
(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

:xx
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

:aa
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,
:shft
shift
if "%1" neq "" goto :aa
>>%out% echo %lineout:~0,-1%
:: end script

message edited by nbrane


Reply ↓  Report •

#7
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 =
do{
[pscustomobject]@{
Column1 = $Column1[$counter]
Column2 = $Column2[$counter]
Column3 = $Column3[$counter]
}
$counter++
}until($counter -eq $Column1.length)

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


Reply ↓  Report •

Ask Question