When moving data from one system to another, it is invariably the CSV format that is used: it’s conveniently supported by Excel, Access and almost every other spreadsheet and database management system. However, it’s not the best format to use. CSV, as it’s name suggests, uses commas to separate values into columns. Furthermore, text is identified using double quotation marks, allowing for commas to be inserted into text fields. Unfortunately, double quotation marks are also used to signify inches, so if you have a CSV file of monitors with screen sizes described, it can cause significant issues when importing into the target spreadsheet or database.

So what is the answer? Well, pipe delimited seems a more robust solution. This is where the pipes character (|) is used instead of commas. Unfortunately, it is not so universally supported.

For MS Excel to support pipe-delimited, you need to customise the regional options for your operating system. To do this, go into Control Panel and select “Regional and Language Options”. Next click “Customize” for your region setting.
Change the “List separator” option from comma to pipes. Then click “OK”.

Now, instead of CSV files, MS Excel will support pipe-delimited.

But what if your extract is already in CSV and you need to convert it to pipe delimited? Then you need a program like PS Pad – a text editor that can replace characters and carriage returns:

1) replace “,” with |
2) replace ^” with |
3) replace “$ with |

This will essentially turn it from CSV to pipe delimited.

Of course, this is all a bit long-winded – it would be far better for spreadsheets and databases to be able to import and export data in pipe delimited format without having to muck about like this, and this is where Open Source could leap ahead of Microsoft, but perhaps that’s just a pipe (delimited) dream.

Tags: Excel, microsoft, open source, programming