Monday, February 28, 2011

CSV tip

Ever tried to import data from a .CSV file and found a "misplaced" comma (,) or semi-colon (;)? Well, in fact these two characters may not be misplaced at all - maybe they're just part of the data themselves.

So to avoid eventual problems when parsing the data, one possible and effective solution is to change the default character used by Excel (the most popular app, by far) when splitting the data to build the .CSV file.

By default the "," or ";" delimiters are used, but these can be changed by something less predictable to be used on data in a spreadsheet (something like "|").

In order to this: Start -> Control Panel -> Regional and Language; then open "Additional settings" and on the "Number" tab, locate the "List separator" text box and change the value.

Now, when saving the spreadsheet (.xlsx or .xls) as a .csv file, the delimiter in use shall be the one specified instead of the ";" default one.

That's it.

G.