Creating Better CSV Files With Excel

Excel is a great tool for a number of reasons. But when it’s used to create CSV files, it usually throws something at me that’s hard to parse.

I often need to use a script to parse CSVs (datafeeds, database exports, or just spreadsheets of data) and grab data, or reformat the CSV, but a problem I’ve run into many times is the fact the Excel exports to CSV and does not allow me to enclose the fields in quotes, double-quotes, or…anything. And it separate the fields by commas. And that’s just how it is. (No options!)

So, instead of doing regex acrobatics or parsing contortions, there’s a way to make Excel export in a much easier-to-parse format.

To do this, you must change a Windows XP setting. Click Start->Settings->Control Panel->Regional and Language Options. Next to the Language, click “Customize”. Next, chnage the “List Separator” (under the Numbers tab) to something very unique. I use a tilde (“~”) symbol, due to the fact that I rarely find that within the data I’m working with. But you can use something else such as a pipe symbol.

Then, export your data to CSV. Your fields will have a more unique separator which you can easily distinguish. If you want, you can easily do a search-and-replace of that symbol as well. It’s saved me a lot of time!