I don't wanna be a cutter!

January 9, 2007 12:22 PM

Is there an easier way to combine CSV files? There is a bit

I have 20 CSV files that all have the same format. I need to combine them into just one large file that I will be importing. Is there an easier way to do this then opening and copying / pasting all this data.....

~ as keep shakes the magic 8 ball of the hive ~

posted by keep it tight to technology (9 comments total)

are you on windows?

C:\>copy /?
Copies one or more files to another location.

COPY [/D] [/V] [/N] [/Y | /-Y] [/Z] [/A | /B ] source [/A | /B]
[+ source [/A | /B] [+ ...]] [destination [/A | /B]]

C:\>copy foo.csv + casper.csv + hepatitis.csv + frumpkin.csv chowder.csv

posted by boo_radley at 12:28 PM on January 9, 2007

On linux it is:
cat foo.csv casper.csv hepatitis.csv frumpkin.csv > chowder.csv
posted by flyingcowofdoom at 12:31 PM on January 9, 2007

Place all the CSV files in a single folder named [csv-files] on your Desktop. (no brackets) Make sure all the csv files have a .csv extension.

If you're on a Unix based system (including Mac OS X), open a terminal window and type the following two lines (no brackets)

cd Desktop/[csv-files]
cat *.csv > my-new-file.csv

If you're on a Windows based system, open a command window ("cmd.exe") and type the following two lines (no brackets)

cd "Desktop\[csv-files]"
type *.csv > my-new-file.csv

The type/cat command will display the contents of a file to something called standard output. The ">" character will redirect standard output to a file.
posted by alan at 12:34 PM on January 9, 2007 [2 favorites]

@alan
I could not for the life of me remember "type". I was racking my brains for the Windows equivalent to cat, but it didn't come. Good thinking!
posted by flyingcowofdoom at 1:01 PM on January 9, 2007

If your csv files have header or variable-name rows at the top, you'll have to strip them out again when you're done merging them.

The easiest way to do this would just be to sort the resulting megafile so that all of the header rows are at the bottom together, and delete the bottom few rows so that there's only one header row left. Then resort.
posted by ROU_Xenophobe at 1:03 PM on January 9, 2007

On Windows XP, save the following as csvmerge.cmd:

copy "%~1" combined.csv
rename "%~1" "%~nx1.renamed"
for /F "skip=1 delims=" %%L in (*.csv) do echo."%%~L">>combined.csv
rename "%~1.renamed" "%~nx1"

If you put csvmerge.cmd into a folder that has all the CSV files you want to merge, then drag and drop one of them onto it, it will merge all the CSV files starting with the one you drag and drop, skipping the header on all but that file, into combined.csv.
posted by flabdablet at 9:01 PM on January 9, 2007

Oops, obvious bug. Try this instead:

copy "%~1" combined.csv.part
rename "%~1" "%~nx1.renamed"
for /F "skip=1 delims=" %%L in (*.csv) do echo."%%~L">>combined.csv.part
rename "%~1.renamed" "%~nx1"
rename combined.csv.part combined.csv

posted by flabdablet at 9:03 PM on January 9, 2007

And I think the echo command is wrong, too. Bugger. Try

copy "%~1" combined.csv.part
rename "%~1" "%~nx1.renamed"
for /F "skip=1 delims=" %%L in (*.csv) do echo.%%L>>combined.csv.part
rename "%~1.renamed" "%~nx1"
rename combined.csv.part combined.csv

I really should know better than to try to post Windows cmd scripts from a Linux box; it makes them hard to test :-)
posted by flabdablet at 9:07 PM on January 9, 2007

If you wanna automate this thru Excel, just email me. Address in profile.
posted by NailsTheCat at 3:11 PM on January 10, 2007