Combining Many Excel CSV Files Into One Part 1

What should you do when you need to combine many Excel files (CSVs, XLSXs, TXTs, whatever) into one “master” file?

Merge Many CSVs into Master

Given this file structure with CSVs, we want to merge them all into the master file

Part 1 of a 3-Part Series (Part 2) (Part 3)

This situation or some slight variation has undoubtedly landed on your desk before. You’re given a bunch of CSVs (or XLSXs, or text files maybe), and you need to get all that data into a single “master” file. Bonus points if you get new data files daily!

Fortunately, with VBA you have everything you need to make short work of this challenge.

In Part 1, we’ll handle the folder setup and decide on our overall design. This post will be short, but DAMN important — spending a bit of time up-front allows us to see the potential pitfalls from the beginning, when we can actually DO something about them.

First things first. We know that we want to import each CSV one time, so it would make sense to move each imported file to some kind of “processed” (or “completed”) folder once it’s done.

When taking on bigger projects, it can be really helpful to write out the “ideal world” code first to serve as a guideline. In this case, our “ideal world” code would look like this:

And our file structure should look like this:

C:
|—…
|—MyFolder
|——processed
|———(starts off empty)
|——CallCenterData_1.csv
|——CallCenterData_2.csv
|——(more call center CSV files)
|——master.xlsb
|—…
|—Program Files
|—Program Files (x86)
|—…
|—Users

Folder Structure for Combining CSVs

This is the setup we’ll use to combine all these suckers

And with that, we’re done with Part 1!

Ready to dive-in and do some heavy-lifting? Check out Part 2, where we extract data from each CSV and add it to our master sheet.
Does this high-level strategy and setup make sense to you? If not, let me know and I’ll help you get what you need! And if you’d like more step-by-step, no-bullshit VBA guides delivered direct to your inbox, join my email newsletter below.

Get the VBA Toolbelt!

Quit digging through old projects and forums like a chump! Download the VBA Toolbelt and start with the most common Excel tasks already done for you.

No spam, ever. Unsubscribe at any time. Powered by ConvertKit