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:

Windows Command Line

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.

Tags: ,

12 Comments on Merging CSV files Using the Command Line

  1. Maurice says:

    Thanks for posting this tip. I used it on 3,000 csv files to one for a database load. Saved me hours!

  2. Rohan Golwalkar says:

    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

  3. Robin Adair says:

    A great direction. Finally something I could follow. Loved the touch of the confirming screen shot. Huge help. Thanks Robin

  4. dinesh says:

    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.

  5. Karthik says:

    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!

  6. ketan solanki says:

    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)

  7. Rakesh Shetty says:

    Is there a way to find out Merge CSV files and create a column with the filename from the original file??

  8. Rob says:

    Simply beautiful. I wonder how many hours I could have saved throughout my career. Thank you.

Leave a Reply