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.
Popular search terms for this article:
concatenate excel, concatenate in excel, concatenate csv files, concatenate excel files, concatenate csv, how to concatenate, yhs-geneiotransfer, concat csv files, concatenate files excel, how to concatenate excel files
I’ll use this tip in near future. Thanks
This is a great tip; thanks for writing it up. Unfortunately, I don’t have Office 2007 or 2010 for the final step. I found de-duping instructions for older versions of Excel on Microsoft’s website.
Great writeup, thanks! I am running XP and did find that I couldn’t put the directory in the copy command language. But I wasn’t working in the root drive, either. So once I changed the directory to my working folder, I simply typed from the Prompt (C:\New Directory\) “copy *.csv combined.csv”
This also works for my .txt files. Great timesaver, thanks again!
Gah! So obvious once you say it. Thanks!
Great time saving tip…i often have to deal with hundreds of files…Thanks for sharing…
Well after hours of searching, downloading and general dicking around, I found your tips. Unbelievably simple to execute: thanks very much. I haven’t done anything in DOS for years, and really couldn’t believe this was so easy to do.
I tried various help sites and yours was the only method that worked! Thanks so much.
I tried this as well – and it somehow worked. However the DOS command did not sort the files alphabetically or chronologically but in a somehow random structure. I even tried to rename my files, using 1.csv, 2.csv, 3.csv and so on. It still did a put the files randomly together. Unfortunataly I need my files chronologically sorted. Anybody has a clue how I could do that??
You make my day! I’m so happy, 327 files in less than 2s when after 1 hour this morning I was struggling at 50 files!!! You are the BEST 🙂
You’re welcome Thibault! Keep reading WorkAwesome!
Thanks very much for this. I created a .BAT file to save having to go via dos prompt.
Create a new text file, and rename it something like “Concat.bat” (the .bat replacing the .txt). You may need to enable filename suffixes if it doesn’t change into a BAT file.
right click and “edit” it to insert the following code:
copy *.csv Concat.csv
Now run this in whatever folder has the CSV files that you need to concatenate and it will create a file called “Concat.csv” with all the data from the CSVs in it.