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?
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
This is the setup we’ll use to combine all these suckers
And with that, we’re done with Part 1!