Batch -combining data from multiple csv files

March 1, 2011 at 22:29:42
Specs: Windows Vista
Hi,

I am trying to automate process of combining data from two sets of csv files that each have same number of rows but different number of columns. I would like to copy data from Column 60 of fileA_YYYYMM.csv to Column 5 of fileB_YYYYMM.csv

Specifically I have 100 monthly data files. Each with 18 rows of data. I would like to take data from column 60 of fileA and add to column 5 of fileB for each monthly period.

I found the following script on an older post that copys data from one column of a csv file and creates a new file. I tested it and it works well, but seems to be a problem when I enter "tokens=60"... a new file is created but it is empty.


:: EX3.BAT Usage: ex3 "Path_Name\File_name"
@echo off > "%~dpn1.new"
for /F "tokens=60 delims=," %%j in ('type "%~1"') do echo.%%j>> "%~dpn1.new"
:: End_Of_Batch


A further clarification is that the data if fileB, is the same for each period. Ie.
RowHeader1,0,1
RowHeader2,0,1
RowHeader3,0,1
....
Row18

Thanks in advance,


See More: Batch -combining data from multiple csv files

Report •

#1
March 1, 2011 at 22:44:48
The maximum number of tokens available is 31 (tokens=31) . You could split the input file into manageable sizes using For loops and extract the value in column 60 then proceed to add it to file B. What do you mean by add it? Is this a mathematical add or add data to the existing data in col 5?

Did you Google for an answer before asking the question?


Report •

#2
March 2, 2011 at 03:46:16
:: ==========================================
::
:: toksixty.bat Wed 02-03-2011 18:39:28.21
@echo off & setLocal enableDELAYedeXpansion

for /f "tokens=* delims= " %%a in (long) do (
call :sub1 %%a
echo.!S!
goto :eof
)

:sub1
for /L %%i in (1 1 59) do shift
set S=%1
goto :eof


=====================================
Life is too important to be taken seriously.

M2


Report •

#3
March 2, 2011 at 04:49:26
Wahine,

Thanks. I didn't know about the token limit, that explains the empty file. I have imported the files into Access and created a query with only the data from the 60th column. I could then export the queries into separate csv files and then do something like the script above.. but was hoping to skip that process and do it directly from a batch script....

By "add" I meant not an mathematical function but to add data to column 4 in FileB.

Currently File B looks like:
RowHeader1,0,1
RowHeader2,0,1
RowHeader3,0,1

After result would like file B to be:
RowHeader1,0,1, Column 60 Row 1 data from FileA
RowHeader2,0,1, Column 60 Row 2 data from FileA
RowHeader3,0,1, Column 60 Row 3 data from FileA
...


Mechanix2Go,

Thanks!

Works well, not exactly what I was looking for but my fault for not being clear. This looks handy to use in future as I have another project where I would need to do a multiplication function on 2 columns of a csv file and create a new file that copies the resulting values for each record.


Report •

Related Solutions

#4
March 2, 2011 at 05:36:59
:: ==========================================
::
:: toksixty.bat Wed 02-03-2011 18:39:28.21
@echo off > newfile & setLocal enableDELAYedeXpansion

set N=
for /f "tokens=* delims= " %%a in (fileA) do (
set /a N+=1
call :sub1 %%a
set A!N!=!S!
)

set N=
for /f "tokens=* delims= " %%a in (fileB) do (
set /a N+=1
call :sub2 !N!
>> newfile echo.%%a,!S!
)
goto :eof

:sub1
for /L %%i in (1 1 59) do shift
set S=%1
goto :eof

:sub2
set S=!A%1!
goto :eof


=====================================
Life is too important to be taken seriously.

M2


Report •

#5
March 2, 2011 at 23:14:36
Mechanix2Go,

Awesome! works like a charm.

Now if I wanted to loop this to perform this operation on multiple initial files (FileAxxyy.csv)in the same directory so that column 60 would be added to the 4th column in FileB and create a new file that has the save "FileA" prefix but different "xxyy" values depending on FileA name, could I do something like the below (sorry for butchering your code):

::
:: toksixty.bat Wed 02-03-2011 18:39:28.21
@echo off > newfile & setLocal enableDELAYedeXpansion
:TOP

IF (%%a)==() GOTO END
set N=
for /f "tokens=* delims= " %%a in (fileA****.csv) do (
set /a N+=1
call :sub1 %%a
set A!N!=!S!
)

set N=
for /f "tokens=* delims= " %%a in (FileB.csv) do (
set /a N+=1
call :sub2 !N!
>> "newfile****.txt" echo.%%a,!S!
)
goto :eof

:sub1
for /L %%i in (1 1 59) do shift
set S=%1
goto :eof

:sub2
set S=!A%1!
goto TOP
:END


Report •

Ask Question