?
Solved

Batch file to combine CSV files

Posted on 2011-05-11
33
Medium Priority
?
717 Views
Last Modified: 2012-08-14
i need a script to merge a bunch of csv files, but i need part of the file name to be inserted into the final file.  Example:

file001.csv
aaa,bbb,ccc
ddd,eee,fff
ggg,hhh,iii

file002.csv
aaa,bbb,ccc
ddd,eee,fff
ggg,hhh,iii

file003.csv
aaa,bbb,ccc
ddd,eee,fff
ggg,hhh,iii

the resulting file would read:

final.csv
001,aaa,bbb,ccc
001,ddd,eee,fff
001,ggg,hhh,iii
002,aaa,bbb,ccc
002,ddd,eee,fff
002,ggg,hhh,iii
003,aaa,bbb,ccc
003,ddd,eee,fff
003,ggg,hhh,iii
0
Comment
Question by:darrennelson
  • 16
  • 9
  • 4
  • +1
33 Comments
 
LVL 10

Expert Comment

by:ReneGe
ID: 35741266
Working on it.
0
 
LVL 6

Expert Comment

by:Dangle79
ID: 35741336
this should do it
Const ForReading = 1
Const ForWriting = 2

'file names
inFile1 = "C:\csvfiles\file1.csv"		'replace file1 with your file name
inFile2 = "C:\csvfiles\file2.csv"		'replace file2 with your file name
inFile3 = "C:\csvfiles\file3.csv"		'replace file3 with your file name

'Path to output file
outFilePath = "C:\csvfiles\final.csv"


set fso1=createobject("scripting.filesystemobject")
Set inFile1 = fso1.OpenTextFile(inFile1, ForReading)

set fso2=createobject("scripting.filesystemobject")
Set outFile2 = fso2.OpenTextFile(inFile2, ForReading)

set fso3=createobject("scripting.filesystemobject")
Set outFile3 = fso2.OpenTextFile(inFile3, ForReading)

set fso4=createobject("scripting.filesystemobject")
Set outFile = fso2.OpenTextFile(outFilePath, ForWriting)



Do Until inFile1.AtEndOfStream
 inLine = inFile1.ReadLine
 outLine = "file1," & inLine		'replace file1 with your file name
 outFile.WriteLine(outLine)
Loop

Do Until inFile2.AtEndOfStream
 inLine = inFile2.ReadLine
 outLine = "file2," & inLine		'replace file2 with your file name
 outFile.WriteLine(outLine)
Loop

Do Until inFile3.AtEndOfStream
 inLine = inFile3.ReadLine
 outLine = "file3," & inLine		'replace file3 with your file name
 outFile.WriteLine(outLine)
Loop


inFile1.Close
inFile2.Close
inFile3.Close
outFile.Close
set fso1 = NOTHING
set fso2 = NOTHING
set fso3 = NOTHING
set fso4 = NOTHING

Open in new window

0
 
LVL 10

Expert Comment

by:ReneGe
ID: 35741362
Enjoy

 
@ECHO OFF

SETLOCAL enabledelayedexpansion

SET Output=Final.csv
IF EXIST "%Output%" DEL "%Output%"

FOR /L %%A in (1,1,1000) DO (
	CD
	IF %%A LSS 10 SET File=Final00%%A.csv
	IF %%A GEQ 100 SET File=Final0%%A.csv
	IF %%A GEQ 1000 SET File=Final%%A.csv
	IF EXIST "!File!" (
		ECHO APPENDING: "!File!" TO "Output"
		FOR /F "usebackq delims=" %%B IN ("!File!") DO ECHO %%A,%%B>>"%Output%"
	) ELSE (
		PAUSE
	)
)


PAUSE

Open in new window

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 10

Expert Comment

by:ReneGe
ID: 35741383
Oups... Fixed

Enjoy!

 
@ECHO OFF

SETLOCAL enabledelayedexpansion

SET Output=Final.csv
IF EXIST "%Output%" DEL "%Output%"

FOR /L %%A in (1,1,1000) DO (
	CD
	IF %%A LSS 10 SET File=Final00%%A.csv
	IF %%A GEQ 100 SET File=Final0%%A.csv
	IF %%A GEQ 1000 SET File=Final%%A.csv
	IF EXIST "!File!" (
		ECHO APPENDING: "!File!" TO "Output"
		FOR /F "usebackq delims=" %%B IN ("!File!") DO ECHO %%A,%%B>>"%Output%"
	) ELSE (
		EXIT
	)
)

Open in new window

0
 
LVL 6

Expert Comment

by:Dangle79
ID: 35741386
oooooh, which one's he gonna pick!
0
 
LVL 10

Expert Comment

by:ReneGe
ID: 35741401
Whats wrong with me?

Updated

 
@ECHO OFF

SETLOCAL enabledelayedexpansion

SET Output=Final.csv
IF EXIST "%Output%" DEL "%Output%"

FOR /L %%A in (1,1,1000) DO (
	CD
	IF %%A LSS 10 SET File=Final00%%A.csv
	IF %%A GEQ 10 SET File=Final0%%A.csv
	IF %%A GEQ 100 SET File=Final%%A.csv
	IF EXIST "!File!" (
		ECHO APPENDING: "!File!" TO "Output"
		FOR /F "usebackq delims=" %%B IN ("!File!") DO ECHO %%A,%%B>>"%Output%"
	) ELSE (
		EXIT
	)
)

Open in new window

0
 
LVL 10

Expert Comment

by:ReneGe
ID: 35741438
@Dangle79: The suspense is killing me ;-)
@darrennelson: Why don't you just try both and equally split points for the working scripts?

Cheers,
Rene
0
 

Author Comment

by:darrennelson
ID: 35741482
lol, thanks for the quick response.  I'm going to test both out.

@Dangle79: One thing I did notice - the file names won't always be the same, though they will always have the same structure.  ei file001.csv through roughly file650.csv.  At any given time when this is ran, the directory may contain 400 files, arbitrarily numbered between 001-650.

Testing both now, brb
0
 
LVL 6

Expert Comment

by:Dangle79
ID: 35741515
bah, my solution was more literal and for a one time thing. i could retool it to run as an automated thing, but we'll suspend that until we see which you prefer
0
 

Author Comment

by:darrennelson
ID: 35741812
a little more clarification:

here are what my files names will actually be:

Upload001.csv thru Upload650.csv (at any given time there may be only 20 files or any number of files up to around 650)

sample values of a file:

04/25/2011,858.21,85
04/26/2011,893.04,85
04/27/2011,839.28,74
04/28/2011,1023.32,97
04/29/2011,1316.17,111
04/30/2011,1136.54,100

I need the script to be able to do the following:

-find 1st file (hypothetically Upload004.csv)
  |_copy contents to final.csv
     004,04/25/2011,858.21,85
     004,04/26/2011,893.04,85
     004,04/27/2011,839.28,74
-find 2nd file (hypothetically Upload059.csv)
  |_copy(append) contents to final.csv
     059,04/25/2011,755.26,59
     059,04/26/2011,795.69,68
     059,04/27/2011,801.25,80
-find 3rd file (hypothetically Upload125.csv)
  |_copy(append) contents to final.csv
     125,04/25/2011,656.45,59
     125,04/26/2011,158.26,48
     125,04/27/2011,569.69,78
      
when the script finished, the directory would look like:

C:\CSV\Upload004.csv
C:\CSV\Upload059.csv    
C:\CSV\Upload125.csv
C:\CSV\final.csv
C:\CSV\merge.bat

and C:\CSV\final.csv will look like:

004,04/25/2011,858.21,85
004,04/26/2011,893.04,85
004,04/27/2011,839.28,74
059,04/25/2011,755.26,59
059,04/26/2011,795.69,68
059,04/27/2011,801.25,80
125,04/25/2011,656.45,59
125,04/26/2011,158.26,48
125,04/27/2011,569.69,78
0
 
LVL 6

Expert Comment

by:Dangle79
ID: 35742149
slightly more elegant
just indicate the output file location and the folder containing the files.
will parse the folder finding all files beginning with "Upload", peel the ### from the name, append it to the beginning of each line and write the whole mess out to a new csvfile. if you wanted we could even add a date/time stamp to the end of the new csv file name and run it as a scheduled task. or add a bit in there to delete the files it's already done with to keep your folder clean
Dim FSO
outFilePath = "C:\csvfiles\final.csv"
DataLocation = "c:\csvfiles\"
set fso = CreateObject("Scripting.fileSystemObject") 
set fold = fso.getFolder(DataLocation)
Set outFile = fso.CreateTextFile(outFilePath, True)

for each file in fold.files
  If Left(file.name,6) = "Upload" Then
    inFileNum = Left(Right(file.name,7),3)
    inFileName = DataLocation & file.name
    Set inFile = fso.OpenTextFile(inFileName, 1)
    Do Until inFile.AtEndOfStream
        inLine = inFile.ReadLine
        outLine = inFileNum & "," & inLine
        outFile.WriteLine(outLine)
    Loop
    inFile.Close
  End If
Next
outFile.Close
set fso = NOTHING

Open in new window

0
 
LVL 10

Expert Comment

by:ReneGe
ID: 35742180
Little insignificant modif.
I'll also keep the max at 1000, in case you have more than 650.

@Dangle79: Do you still plan to complete your vbscript version?

Cheers,
Rene

@ECHO OFF

SETLOCAL enabledelayedexpansion

SET Output=Final.csv
IF EXIST "%Output%" DEL "%Output%"

FOR /L %%A in (1,1,1000) DO (
	CD
	IF %%A LSS 10 SET File=Final00%%A.csv
	IF %%A GEQ 10 SET File=Final0%%A.csv
	IF %%A GEQ 100 SET File=Final%%A.csv
	IF EXIST "!File!" (
		ECHO APPENDING: "!File!" TO "Output"
		FOR /F "usebackq delims=" %%B IN ("!File!") DO ECHO %%A,%%B>>"%Output%"
	)
)

Open in new window

0
 
LVL 10

Expert Comment

by:ReneGe
ID: 35742195
@Dangle79
You were faster than me   ;-)
0
 
LVL 6

Expert Comment

by:Dangle79
ID: 35742208
@ReneGe
We're running about neck and neck. Mine's done and tested, only hangup to mine is it will barf if the numbers in the file name exceed three digits.
0
 
LVL 10

Assisted Solution

by:ReneGe
ReneGe earned 1000 total points
ID: 35742238
Oups, the files does not start with "Final" but rather "UploadXXX..."

 
@ECHO OFF

SETLOCAL enabledelayedexpansion

SET Output=Final.csv
IF EXIST "%Output%" DEL "%Output%"

FOR /L %%A in (1,1,1000) DO (
	CD
	IF %%A LSS 10 SET File=Upload00%%A.csv
	IF %%A GEQ 10 SET File=Upload0%%A.csv
	IF %%A GEQ 100 SET File=Upload%%A.csv
	IF EXIST "!File!" (
		ECHO APPENDING: "!File!" TO "Output"
		FOR /F "usebackq delims=" %%B IN ("!File!") DO ECHO %%A,%%B>>"%Output%"
	)
)

Open in new window

0
 
LVL 10

Expert Comment

by:ReneGe
ID: 35742248

@ECHO OFF

SETLOCAL enabledelayedexpansion

SET Output=Final.csv
IF EXIST "%Output%" DEL "%Output%"

FOR /L %%A in (1,1,1000) DO (
	IF %%A LSS 10 SET File=Upload00%%A.csv
	IF %%A GEQ 10 SET File=Upload0%%A.csv
	IF %%A GEQ 100 SET File=Upload%%A.csv
	IF EXIST "!File!" (
		ECHO APPENDING: "!File!" TO "Output"
		FOR /F "usebackq delims=" %%B IN ("!File!") DO ECHO %%A,%%B>>"%Output%"
	)
)

Open in new window

0
 
LVL 6

Expert Comment

by:Dangle79
ID: 35742249
i should say, it will do that because it's hard-coded for that naming pattern. as much as i love scripting i wasn't going to code a numeric string search to account for variations. maybe if i was the only one chasing this but it's a safe assumption we'll split points on this one. not that i'm lazy or anything
0
 
LVL 10

Expert Comment

by:ReneGe
ID: 35742269
@Dangle79

I know. Almost feels like a competition here!!

It's all cool, having fun ;-)
0
 
LVL 6

Expert Comment

by:Dangle79
ID: 35742313
@ReneGe
lol
yeah, fun looking at two different approaches to the same problem. mine takes about 50% more code than yours!
0
 
LVL 10

Expert Comment

by:ReneGe
ID: 35742380
@Dangle79
Maybe for the caracter count but you have only one more word. So We'r kind a close!
The competition is very tight here ;-)
0
 
LVL 43

Accepted Solution

by:
Steve Knight earned 1000 total points
ID: 35742408
Just to throw it into the mix, why not read the dir listing instead of looping through all possibles:

@echo off
setlocal enabledelayedexpansion
(for /f %%a in ('dir /b /a-d /on Upload*.csv') do (
  set file=%%~na
  for /f "tokens=*" %%x in (%%a) do echo !file:Upload=!,%%x
)) > total.csv

which produces

004,04/25/2011,858.21,85
004,04/26/2011,893.04,85
004,04/27/2011,839.28,74
004,04/28/2011,1023.32,97
004,04/29/2011,1316.17,111
004,04/30/2011,1136.54,100
059,04/25/2011,858.21,85
059,04/26/2011,893.04,85
059,04/27/2011,839.28,74
059,04/28/2011,1023.32,97
059,04/29/2011,1316.17,111
059,04/30/2011,1136.54,100
125,04/25/2011,858.21,85
125,04/26/2011,893.04,85
125,04/27/2011,839.28,74
125,04/28/2011,1023.32,97
125,04/29/2011,1316.17,111
125,04/30/2011,1136.54,100

Steve
0
 
LVL 43

Expert Comment

by:Steve Knight
ID: 35742431
Oh yeah if the files are likely to be written while it is working it might be worth doing alng the lines of:

rename Upload*.csv *.process
Change the DIR command to be *.process
Then You can del *.process say if needed and know those are the ones that ended up in the total.csv

Steve
0
 
LVL 10

Expert Comment

by:ReneGe
ID: 35742480
Inspired by Dangle79's script, here's a fitter version.

 
@ECHO OFF

SET FileName=Upload
SET Output=Final.csv
IF EXIST "%Output%" DEL "%Output%"

FOR /F "delims=" %%A in ('DIR /b /A-d %FileName%*.csv') DO (
	ECHO APPENDING: "%%A" TO "%Output%"
	FOR /F "usebackq delims=" %%B IN ("%%A") DO ECHO %%A,%%B>>"%Output%"
)

Open in new window

0
 
LVL 43

Expert Comment

by:Steve Knight
ID: 35742517
ReneGe - think you need to strip off the "Upload" bit of the filename like above.  I just did one file redirect operation with > rather than possibly many hundreds of individual lines and files appended, though I don't suppose it would make a massive difference to the time taken unless they are huge / lots of files.

Steve
0
 
LVL 10

Expert Comment

by:ReneGe
ID: 35742518
Hey Steve ;-)
Your script rock!!!
0
 
LVL 10

Expert Comment

by:ReneGe
ID: 35742533
@Steve: Thanks for explaining!
0
 

Author Closing Comment

by:darrennelson
ID: 35742673
Thanks for all the help guys.  ReneGe and Dragon-it's scripts both accomplished the same thing, Dragon's was a little more streamlined.  Thanks for the input too Dangle, but im too retarded to know what to do with a vbscript ;P
0
 
LVL 43

Expert Comment

by:Steve Knight
ID: 35742755
thanks. Glad it helped, and sorry ReneGe for jumping in at the end again!  Better try and get some sleep in now mind ...

Steve
0
 
LVL 10

Expert Comment

by:ReneGe
ID: 35742773
@Steve

Don't be sorry, It's always nice and educative having you arround.

Sleeeep!  Sounds like a good plan to me!

Cheers,
Rene
0
 
LVL 10

Expert Comment

by:ReneGe
ID: 35742790
@Dangle79

Until next time  ;-)
0
 
LVL 6

Expert Comment

by:Dangle79
ID: 35745962
@darrennelson
lol, really just needed to change the folder name and run it as a scheduled task. nice part about it is it can be automated, i've never had any luck getting batch files to run unless the server's logged in. hence my leaning. oh well :)

@ReneGe
sword sheathed!!  for now.....  =o0
0
 

Author Comment

by:darrennelson
ID: 35746571
so do you just put the script in a batch file an run?  I tried doing that and it errored on the DIM declaration
0
 
LVL 6

Expert Comment

by:Dangle79
ID: 35746592
nope, you save it as SCRIPTNAME.VBS
the icon will change to a little paper scroll looking thing. then you can run it by just double-clicking or set it up as a scheduled task
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
If you are a mobile app developer and especially develop hybrid mobile apps then these 4 mistakes you must avoid for hybrid app development to be the more genuine app developer.
Loops Section Overview
Screencast - Getting to Know the Pipeline

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question