My blog

Excel and CSV files

I work in the Student Systems group at Monash University and we use CSV files quite extensively at work. Most of these CSV files come from our student database as that is its default export format. We usually use Excel to open and manipulate these files, but unfortunally Excel tends to be a bit overeager when it finds data that remotely looks like a date or a number. Just last week we had a case of a data upload failing because Excel converted the subject codes in the file into dates. The unfortunate subject code was "MAR11".

It would be great if there was a way to tweak the import process in Excel and dial back it's eagerness. The only way that Microsoft has provided is to open the files using Excel's Import Text Wizard. This gets tedious mighty fast.

I've managed to cook up one solution. It's a simple script that tells Excel to open a CSV file with all columns imported as text. Adding the script to the context menu (i.e. right-click menu) for CSV files means that it's now trivial to open up CSV files without having Excel mangle everything.

Check it out on my projects page.