How do you combine multiple csv files into one file?

[Brian Wenner]

Hello,

I have six comma delimited files with a csv extensions on my desktop. I want
to combine the six files into one file and load them into Excel. Right now I
am loading all six into one session of excel then I copying the contents of
one book and paste into the first book, copy paste, copy paste, copy paste,
etc... I figure there has to be a better way. So my question is:

How do you combine multiple csv files into one file with Excel?


Brian

 

[Microsoft]

As I recall, arent the CSV files just text with Comma Seperators? If that is
the case open them in notepad and paste them together!
MArcos

 

[drwecki]

Actually, that won't solve Brian's problem. There is a program that costs 20 bucks that will do it for you but...I don't think so.... All you need to do is open your DOS prompt i.e

Start>Run>type "cmd"

Then make sure all your csv files are in the same folder. I caled my folder help and put it in the C: folder.

Type CD C:help (hit enter)

You should see some verification that you are in this folder.

Type copy *.csv name.csv

Note there is a space between the word copy and the * and a space between.csv and name. Also note that you can change name to anything you want. This will copy all csv files an combine them in a file called name.csv. It copies them in such a manner that it is identical to copying and pasting multiple csvs in excell on the vertical axis 1 to infinity (Not a to vv). It's pretty sweet.


Brian 2

 

[drwecki]

I meant to say type cd c:\help...that will do it

 

[bkarim2]

Thanks a lot

Thanks a lot dude, you are the man!

 

[whatfourever]

How about instead, you go to the command window. (Press "Window Key" and "R", then type command and enter.

Type copy c:\*.csv c:\File.csv and press enter

This will combine all of the csv files that are in your root c:\ directory into one file called File.csv.

You can change the file names and paths as necessary.

Paul

 

[whatfourever]

Sorry guys. I see someone already gave that suggestion.


later

 

[user123]

i have a similiar problem and the command works as long as all the .csv files are in the same folder.
i was wondering if there is a variation of this that tells it to look in subfolders for the .csv file?

ie:
C:\temp\folder1\file1.csv
and
C:\temp\folder2\file2.csv
and
C:\temp\folder3\file3.csv

is there someway to combine it all into 1 csv file?
(copy and pasting the .csv files into one folder can be hard when you have a lot of folders,

thanks in advance!

 

[rfi234]

There is a neat program that will handle the merge of multiple csv files to one master file. Bulk File Merger will do the trick. We just banged through 240 files in a matter of seconds. The program also merges xls, doc, pdf and txt files...

 

[nasureshkumar]

Hi

I tried to use the command mentioned by drwecki vbmenu_register("postmenu_9254273", true); to combile multiple CSV files to one csv.
i facing following issue.

1. Header are repeating while merging into one file.

Please help to resolve the same.

Thanks and regards
Suresh

 

[trottida]

Ok, this is great but is there a way to include the file name in each record in the first column?

 

[Ancient Jake]

Actually, that won't solve Brian's problem. There is a program that costs 20 bucks that will do it for you but...I don't think so.... All you need to do is open your DOS prompt i.e

Start>Run>type "cmd"

Then make sure all your csv files are in the same folder. I caled my folder help and put it in the C: folder.

Type CD C:help (hit enter)

You should see some verification that you are in this folder.

Type copy *.csv name.csv

Note there is a space between the word copy and the * and a space between.csv and name. Also note that you can change name to anything you want. This will copy all csv files an combine them in a file called name.csv. It copies them in such a manner that it is identical to copying and pasting multiple csvs in excell on the vertical axis 1 to infinity (Not a to vv). It's pretty sweet.


Brian 2

Thank you so much for this post drwecki - saved me a days work

 

[frantzie]

Hi

I tried to use the command mentioned by drwecki vbmenu_register("postmenu_9254273", true); to combile multiple CSV files to one csv.
i facing following issue.

1. Header are repeating while merging into one file.

Please help to resolve the same.

Thanks and regards
Suresh

I know this is an old thread, but anyway:
Make 2 new columbs ind the merged sheet.
Columb 1, an index columb,
Columb 2, index for each merged csv

1 1
2 2
3 1
4 2
5 1
6 2
7 1
...

Then sort by columb 2, delet all headers
then sort by columb 1,

Your sheet is cleaned for headers...

 

[gjobby]

I have the same question as trottida, any solutions?
Ok, this is great but is there a way to include the file name in each record in the first column?
Thanks

 

[rtos32]

There is a neat program that will handle the merge of multiple csv files to one master file. Bulk File Merger will do the trick. We just banged through 240 files in a matter of seconds. The program also merges xls, doc, pdf and txt files...

PHP doubt: I have 5 csv files. I have to read each scv files and required column values to be taken and store it as new csv column wise . Example first csv file 0,1,3,4,6 column values to be take and store it new.csv first 5 columns and than to read second csv file to take col 3,4,8 values to be store to new.csv file 6,7,8th column. The to read 3rd csv file to take col 3,4,5,9 to be stored in new csv 9,10,11,12th columns.. Can anyone help me great in PHP programming fedora12. Thanks in advance K.Prabu

 

[denzees]

Excellent work brother.
many many thanks for saving my lots of time.

 

[MK24]

Hi, drwecki your advice worked for me, thank you so much!

But question: After I followed your guidelines in command prompt and it finished running, the new csv file with all of the combined csv's won't open in excel. I had to combine 564 csv files with over 200,000 entries in each. When I open the new file with all of them combined, the excel sheet is blank (no cells or anything, just a dark grey color).

Are these too many files to be combining or do you think there was another issue?