Learn how to automate the loading and formatting of CSV files with clever Excel VBA

We've all seen countless .csv files.  They're great... sometimes.  But many times the .csv file is not quite what is needed.  It may need 'cleaning up', additional calculation fields, unnecessary lines removed, or maybe the file could do with some formatting once loaded.  Using clever Excel VBA to Load CSV Files will dramatically speed up this process.

Using Clever Excel VBA to Load CSV Files (and Clean them up)

I've seen many people deal with CSV or text files in different ways. Some just open the file in Excel and get to work... ouch.  For one-off situations, this may be the best course, but if this is part of a process, or you have many similar files... the risk of an error, not to mention going slightly crazy, is real. Automate!

Below I turn a simple, but ugly, CSV file into a nicely formatted excel sheet with one click.

A simple CSV file

I had limited inspiration when creating this... so here it is, the world's worst shopping list in CSV format.

A few things jump out.  It's out of order, it has a 'Thanks' line which shouldn't be there and there's no total cost for each line or overall for that matter.

But simple file aside, CSV files from a system, supplier, customer, or whoever typically have one or more of these issues.  Using Excel VBA to Load CSV Files and automatically remove issues is very useful.  It also means more time actually using the data!

A few years back I was working in an investment bank that had a bunch of finance staff using a large csv file to complete various P&Ls for different trade accounts. It was tedious for them and one afternoon I sat down with one of the guys and watched the process... it was amazing, it took nearly 30 minutes per P&L just to get the right information out of the very large CSV file after they opened it, sorted it and did a bunch of other stuff that seemed prone to error.  They were good at it and new all the right Excel formulas and menu items, but 30 minutes per P&L!!!

I spent part of the next day actually doing a couple of the P&Ls. (don't worry I'm a qualified accountant...)

Within a week, in between other projects, I wrote some code to load their P&L CSV file, sort it, extract just the data for the current P&L, generate key statistics and output it in the format they needed. It worked, and got the process down to a fraction under 10 seconds.  That was a saving, every day, of 29 minutes and 50 seconds per P&L (there were a lot of P&Ls).  Needless to say the finance guys bought me a few beers.

Anyway...

Clever Excel VBA File

The Excel file contains a simple ADMIN sheet (see below) and a VBA module. The load button prompts the user for the file location.  The VBA opens the file and loads it into a 2-dimensional array (this is important... it's not loading it into a sheet (although it could) it's just storing it in memory in a special object called an array... this is very efficient for performance). It then removes unwanted lines, adds a calculated field, sorts the lines, and ultimately populates a new sheet with formatted output.

Using Excel VBA to Load CSV Files makes life easy but from a user's perspective, it's always good to know exactly what is happening in the background. So as part of the process, I output a few key facts in cells C9-C11.

The Output Sheet

See below. The shopping list is sorted correctly, lines not required are removed and the total cost is added. Formatting is also added. Of course, it's just a start... but hey, it's only a simple shopping list.

That's it.  The code was quick to write and geared to this example (I didn't test it exhaustively).  With minor modification, it could be used in many situations.

To get a copy of the 'LoadCSVSimple01.xlsm' and 'ShopList.csv' files ask in a comment below or contact me.

Getting data into a usable and useful format is very valuable.  Doing this fast, with a low risk of errors, is also very valuable. If CSV or text files are common then being able to automatically load them in exactly the format required is a good strategic action to take.  I used a slightly more advanced version of this to organize the underlying data in my tableau survey post. I'm now ready for my next survey challenge and can change all the survey data into formats perfect for Tableau in seconds.

Finally, if you can influence how you receive data it's always better to get direct links or better quality files. But we all know sometimes it's just not worth the headache. Using Excel VBA to load CSV files is just the start, keep working to improve the underlying data. Actionable insights will follow.