Do you have a bunch of similarly formatted Excel spreadsheets piling up in your Documents folder? I don’t know about you, but I’d rather see one large worksheet than see the same collection of data spread out over dozens of worksheets. Let’s roll them all up into one.
Move Your Files into Your Main Directory
OK, I’ll be honest. We’re doing most of the work in the Windows command prompt, not Excel per se; but a post title like “How to Do X in DOS” doesn’t have the same sex appeal. Ultimately, though, your CSVs are going to be opened and edited in Excel.
For most people, their main directory is their C: drive, as opposed to My Documents or another folder further down your PC’s hierarchy. You can actually perform the following operation on any folder, but since we’re going to change directories in the command prompt, you’ll have less typing to do if you paste them directly into C: using Windows Explorer. If you want to use another drive, like E: or F: or whatever you’ve mounted for an external drive, that’s fine too.
Open the Command Prompt and Change Directories
From the Start menu, type cmd into the search box and hit Enter. From the command prompt (where the cursor is blinking), type cd c:\:, with a space between “cd” and “c:\”, and hit Enter. The prompt will change from whatever its previous directory was to — you guessed it — C:\. Naturally, if you put your files in another drive, substitute the appropriate drive letter.
Copy Your CSVs into a Single File
This is where the magic happens. Type copy c:\*.csv c:\filename.csv, with a space between “copy:\*.csv” and “c:\filename.csv” (substituting filename with your new file name) and hit Enter. This takes all of the CSV files in the drive and merges them, so you’ll want to make sure that if you do this more than once, don’t leave older CSV files in that directory if you don’t want them to be merged.
Dedupe Repeat Headers
Fire up Excel and open up the file (you might have to select All Files instead of All Excel Files). Assuming you generated the original files from the same source, it’s likely that each of those worksheets had the same headers, which will repeat themselves in your new aggregate file. If the source files have slightly different headers, like “Sales for January” and “Sales for February”, you may or may not want to remove the additional headers. If you do decide to remove near-identical headers, you’ll have to do a Find-and-Replace for the unique substring (e.g. “January”) to make the them completely identical.
Note that we are going to remove all duplicate rows, not just the headers. For most people, duplicate rows are redundant information. If that’s not the case for you, you’ll have to remove the header rows by hand.
To remove duplicate rows, hit Ctrl-A to Select All, then in the Data tab (Excel 2007 and 2010), select Remove Duplicates in the Data Tools group, uncheck “My data has headers” (your top header will remain intact), click OK, and voila — a single header for all of your rows.
That’s basically it. Give you new file a good scan to make sure your data is formatted correctly, and if everything checks out, go ahead and remove the source CSV files from your C:\ drive.