When slogging brute-force through masses of keyword research, I tend to download everything from Google Keyword Tool to .csv files for easier filtering in Microsoft Excel 2010. After much gnashing of teeth and trying ineffective XLS scripts, I stumbled across a beautifully simple way to merge .csv files using the Windows Command Line (AKA the C Prompt).
Let’s say I have downloaded 3 .csv files (file-A.csv, file-B.csv and file-C.csv) and I want to quickly merge them.
Simply open the command line by typing the word “run” into your Windows Start Menu, or execute the cmd.exe file at C:\Windows\System32\cmd.exe. A black window will open like a portal into 1985.
Next, direct the command line to the directory that contains the individual .csv files you want to merge. The command line begins in the C:\Windows\System32\ directory. I keep an empty folder at C:\merge for this purpose so I don’t accidentally merge other files. So all I need to do is type “CD merge” to ask the command line to kindly switch to my C:\merge directory.
Next, the copying. Type “Copy *.csv merged.csv” to copy all .csv files in that directory into a single file called “merged.csv.” You can call the file anything you want: Actually, I usually call it “1.csv” instead of “merged.csv” because it’s fast.
Here’s what the whole process looks like:
And you’re done. Open C:\merge\merged.csv (or whatever you called it), dedupe it and you’re ready to filter out the wheat from the chaff in Microsoft Excel 2010. Enjoy!
A hot tip from my cool friend Don Schantz:
Another handy tip to go with this is that if your files are in another folder with a long path name, you can drag the folder name from your Explorer address bar directly into the command prompt window after typing CD , and it will fill it in with the necessary quotes. Yes, drag and drop into a DOS command line.
Originally posted on Web PieRat.
Thanks for posting this tip. I used it on 3,000 csv files to one for a database load. Saved me hours!
Thanks a lot for this tiny but super useful command.
I had a question – this will just merge everything – is there a way to remove the dups as well?
If yes – kindly share thanks in advance
Good question, Rohan! No, I haven’t been able to find a way to do this in the command line, I just use the “Remove Duplicates” command in the “Data” menu in Excel 2010. But it would be faster if we could do it all in one step from the command line. There are a lot of forum posts for various ways to do this using shell programming and scripting languages, though, if you want to get into that. See http://stackoverflow.com/questions/770199/how-to-find-unique-lines-in-a-text-file-from-command-line or http://www.unix.com/shell-programming-scripting/179059-remove-duplicate-lines-based-field-sort.html for some ideas. and if you DO find a way to do it in the command line, I’d love to hear about it.
Thanks Jill,
let me check this out – and i shall post if i find a solution which we can use it in one command.
Rohan,
Were you able to find a way to Copy and remove the dups in one command ?
A great direction. Finally something I could follow. Loved the touch of the confirming screen shot. Huge help. Thanks Robin
Hi Jill
I have one folder with some 100 csv files.I want to merge these 100 csv files into 2 separate files 73,17 each.Do we have any command to copy only selected files instead of all files.
Thanks in advance.
Hello, Dinesh. Not that I know of with basic command line functions, sorry.
Hi Jill,
Thanks so much for sharing this trick. I’ve a quick question, by using this method, my files’ contents get copied over the same sheet one below the other. Is there a way, I can copy them on separate sheets?
Please advise! Thanks so much!
Thanks for the command line solution. It saved a lot of time and energy. A question – Is there a way to join the files ( joining would be horizontal in one go)
Is there a way to find out Merge CSV files and create a column with the filename from the original file??
Simply beautiful. I wonder how many hours I could have saved throughout my career. Thank you.
still useful in 2017, saved me a load of time and hours of copy and pasting, thanks for sharing
it workedddddd thank you……
Great solution it helps!!!
Wow-what!
Just 2 mins to merge 10s of files
thanks to the author for sharing, have been doing it through VBA till now with a lot of time investment.
Very good instruction and a very useful tip! Thank you for sharing this as it is so much easier than using VBA to do a similar task.
That is so good. I spent hours trying to get a vba code to run before stumbling on this – thank you ever so much