Doing keyword research, and have a million CSV files that you need to extract data from?
Well, this is where a handy, and rather tiny, SEO hacks that has saved me plenty of time over the years comes in.
The ability to merge as many CSVs as you want into a single monster file, allowing you to then just dedupe it and use it how you need.
Built into Windows, the command prompt is not something the average user will open up.
I’ll show you how to open it, and most importantly, how to merge all your csv files together.
Opening the windows command prompt
You open the command prompt by;
- Clicking start/windows in the bottom left corner
- Typing ‘CMD’ (just type it, don’t need to press anything)
- Clicking on ‘Command Prompt’
You’ll be presented with a blank command prompt, that will default to your user directory and look similar to the below;
Change the directory to the location of your CSV files
The next step is to change the directory in CMD, to where you have your CSV files stored.
To do this, you type CD, and then the folder location.
So for me, I will be typing in;
CD C:\Users\sam\Box Sync\Clients\Sample CSV files
And then hitting enter, and it will look like the below;
CMD has successfully changed the location to where you have your CSV files.
If you don’t know the location of this, you can just browse through your folders and then click in the top of the screen.
Browsing to my folder, I will see;
You then click in the bar at the top where I have highlighted, and you will be able to copy the folder location, like this;
Merging your CSV files
Now that you’ve updated the location, you can merge your CSV files.
To merge the files, you need to type copy *.csv filename.csv
the wildcard *.csv will select all the csv files in the folder, and the filename can be anything you want.
So for me, I will type the following;
copy *.csv all-keywords.csv
The command prompt will then run through and merge them all, and output a new csv file based on the name you gave it.
Depending on how many files, and how big they are, will depend on how long this takes to run.
For my small batch, it took 2 seconds. Even with a large one it shouldn’t take more than 10-15 seconds to run though.
Deduplicating your new CSV
After you’ve merged your files, you will just need to open the csv, and dedupe them. You could do this in Excel, or open it in Google docs.
- Select the column of keywords
- Click on ‘remove duplicates’ under the data tab
- Ensure ‘expand the selection’ is selected, and then click ‘remove duplicates’
- Click ‘unselect all’ and then tick on the keyword column only so that it only dedupes the keywords
Excel will then duplicate all the keywords, and you can then throw them into google sheets or whatever you use to manage your keywords, or look at the data.
Successfully merged your CSV files
And that is how you merge multiple CSV files together with the Windows command prompt.
Couldn’t be easier!