A common requirement from Excel users is to merge multiple csv files into a single file for use in Excel. In this post we show a very effective way that makes use of the old DOS (you may not even remember it)!
Note: In the newer versions of Excel there is a much better, quicker way using PowerQuery (also known as Get & Transform). If this type of topic interests you you MUST get to know about this great new free tool available in Excel!
Firstly, put all the CSV files into a single folder with no other files (if you put it on the Desktop put it in a new folder). Only include the ones you are happy to merge together. Obviously the CSV’s should have exactly the same structure as all this does is put the contents of one file underneath the other file. As shown below we want to merge these 10 csv files into a single file. Note that in the folder we only have those CSV’s. It is also important to work with the file path (you will see why soon)
Click in the file path and copy the path (note that when you click in the path area it will give the full path).
Now go to your windows or start icon (normally bottom left of the screen) and search for the word RUN.
When you click on the RUN program, you will see this screen. Type CMD and click OK.
You will get to a simple version of this screen. In the image below we have already completed the next steps which are:
- Type cd then hit space (NB that there is a space) and paste the path that you copied at the beginning (or go to that folder and copy it again and paste here using CTRL + V).
- Click Enter
- You will then see your full path shown. DOS is now waiting for a command of what to do in that folder.
Type the following:
copy *.csv combine.csv
and click enter
What you have done is told DOS to copy all files (* means anything) of the type csv into a single file called combine.csv (you can use whatever name works for you).
The end result looks like this but this is not where the magic is.
You can close these windows down now. Go back to the folder with the CSV’s and you will see that there is a new file with the name you chose (combine in this case).
You can now work on one file which contains all the data of the other files. You may still need to do some cleanup work but at least it is all in one file.
To see how to clean it up have a look at the Data Cleanup in Excel course we offer.