[kyllo]

But when someone sends you a CSV file (a very common format for database exports and EDI), Excel does the type conversions automatically when you open it. You don't get a chance to change the cell format to Text beforehand. The ' workaround is a huge time-waster if you are dealing with a large amount of data, plus it screws up the file for use outside of Excel.

There really needs to be an option to turn off all type conversion globally for all files in Excel.

[srdev]

Rather than open it directly in Excel, open a blank workbook and use the data import functionality. This lets you specify the type of each column.

Its not a perfect solution, but its a passable work-around.

[kyllo]

This works when you have control of the data.

The real problem arises when you ask someone to send you data in CSV format. If, in between exporting it from their database and sending it to you, they happened to open and save it in Excel, you will get corrupted data. Usually the sender is blissfully unaware of what Excel's automatic type conversion does to their data.

CSV has been made unreliable as a format for data exchange between companies (aka EDI) largely because Microsoft decided that CSV files should always be opened in Excel by default in Windows. At the very least they should turn automatic type conversion off for CSVs.

[tgflynn]

Not sure if this would work but have you tried quoting the values in the generated CSV ?

[kyllo]

Excel ignores double quotes around the fields, it just uses them to escape commas inside the fields.

The official way to do it is to insert a single quote at the beginning of every field that you don't want auto-converted. In practice this is a time-wasting pain in the neck and ruins your data for use outside of Excel.