• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 747
  • Last Modified:

Batch file to combine CSV files

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
darrennelson
Asked:
darrennelson
  • 16
  • 9
  • 4
  • +1
2 Solutions
 
ReneGeCommented:
Working on it.
0
 
Dangle79Commented:
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
 
ReneGeCommented:
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
ReneGeCommented:
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
 
Dangle79Commented:
oooooh, which one's he gonna pick!
0
 
ReneGeCommented:
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
 
ReneGeCommented:
@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
 
darrennelsonAuthor Commented:
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
 
Dangle79Commented:
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
 
darrennelsonAuthor Commented:
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
 
Dangle79Commented:
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
 
ReneGeCommented:
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
 
ReneGeCommented:
@Dangle79
You were faster than me   ;-)
0
 
Dangle79Commented:
@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
 
ReneGeCommented:
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
 
ReneGeCommented:

@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
 
Dangle79Commented:
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
 
ReneGeCommented:
@Dangle79

I know. Almost feels like a competition here!!

It's all cool, having fun ;-)
0
 
Dangle79Commented:
@ReneGe
lol
yeah, fun looking at two different approaches to the same problem. mine takes about 50% more code than yours!
0
 
ReneGeCommented:
@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
 
Steve KnightIT ConsultancyCommented:
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
 
Steve KnightIT ConsultancyCommented:
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
 
ReneGeCommented:
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
 
Steve KnightIT ConsultancyCommented:
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
 
ReneGeCommented:
Hey Steve ;-)
Your script rock!!!
0
 
ReneGeCommented:
@Steve: Thanks for explaining!
0
 
darrennelsonAuthor Commented:
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
 
Steve KnightIT ConsultancyCommented:
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
 
ReneGeCommented:
@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
 
ReneGeCommented:
@Dangle79

Until next time  ;-)
0
 
Dangle79Commented:
@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
 
darrennelsonAuthor Commented:
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
 
Dangle79Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

  • 16
  • 9
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now