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.