Excel: Merging CSV Files The Easy Way

For reasons which are best not explained, I was given some large data exports that had been created as a dozen or so Excel sheets (to the tune of 40,000+ lines per sheet) and asked to convert them into something more useful. Specifically, a CSV file.

Now, I already have a funky Excel VBA routine to export multiple worksheets into separate CSV files but this still left me with a dozen (or so) files, instead of the single one I wanted. At the time, I was working in Windows XP so my solution was to create a batch file, save it in the same folder as all those CSV files that I wanted to stitch together and run it.

The code is simple. Open your favourite Windows text editor (Notepad++ anyone?) and type in the following:

copy *.csv stitchedFiles.csv

Now save it as a .bat format file e.g. “csvStitch.bat” – of course, if you would rather have a snappier filename, feel free to do so.

And that’s it. Simply double-click it, sit back and watch it work. At the end you’ll have a one big CSV file called “stitchedFiles.csv” and all of your little ones keeping it company.

Clearly, we would all much rather be doing this in Mac OSX or some other Unix system, where the code is:

cp *.csv stitchedFiles.csv

Save this as a file? No. Simply go into Terminal or XTerm, navigate to the relevant folder, type in the above and hit Enter. Job done.

P.S. Could we have done the navigation, command line thingy in Windows? Of course we could. I just did it this way to show that there are alternatives in life.

P.P.S. On a final note, do remember that while Windows file naming is not case sensitive, Unix is. Life could get tricky if you create a file called “stitchedFiles.csv” and one called “stitchedfiles.csv” in your beautiful Unix system and then try to send them both to Windows.