This is a trick which can save you a lot of time when working with a dataset spread across multiple CSV files. Using a simple CMD command it is possible to combine all the CSV’s into a single entity ready for all your pivot and table wizardry.
Save all of the CSV files into a single folder. Make sure that the folder is free from any CSV’s you do not want included in the compression.
Navigate to “Run” from the Windows Start Menu
- On XP this is located in the Start Menu itself
- In Vista / Win 7 you may have to navigate first to “Accessories” to find the Run executable.
Type “cmd” into the executable field and hit Enter.
When the CMD window opens; you will be presented with your default document folder housing all of your personal files. From here you need to execute the “cd” command which navigates to your desired folder. Type “cd” after the chevron followed by a space then your desired folder location including the drive architecture. For example if your cmd window opens with “C:\Users\Your Name>” pre-populated, the complete command line would read: “C:\Users\Your Name>cd C:Desired Folder”.
Top Tip – if you navigate to your desired folder in Windows Explorer (e.g. clicking My Documents from the Desktop) you can see the folder location path in the top of the window. However, don’t close it – you cannot copy & paste into CMD so you will need to type the folder path out!
Once you have entered the command line and hit Enter, the desired folder location will appear on the subsequent line. Now you need to use the “copy” function to merge all the CSV files together. In a similar fashion to the “cd” command, type “copy” after the chevron, followed by a space then “combine.csv”. This copies the data from all CSV files in that location into a single file called ‘combine.csv’.
Top Tip: Because the CSV file format cannot support multiple tabs all of your data will be copied into one worksheet within the CSV workbook. For this reason it may be worth collating all your data in a similar structure – so as to avoid large amounts of formatting work at the end.
Once executed you will be presented with confirmation; outlining which files have been copied into a single entity. Navigate to your destination folder and enjoy the fruits of your CSV-based labour in combine.csv!