Microsoft Excel likes to automatically format data in an attempt to be more helpful and friendly to the user. As an application developer, I can appreciate that. Users like friendly and helpful...until it is no longer friendly and helpful.

I recently created a management interface to allow a user to import and export a CSV file to manage data. The CSV format was part of the requirements, and is not negotiable. The import and export are not hard to do, and is something I've done plenty of times before.

"Helpful" Excel

While functional testing a change, however, I noticed that some of the data was wacky in the export file when opened in Excel. Specifically, three columns which contain a number range, i.e., 4 - 5, were appearing as dates formatted like 4-May. Excel saw the data and, even though it knows it is dealing with a text file, proceeded to be helpful and provide a format for the data. Thank you, Excel. This is not a time to be "helpful."

Time for a Single Quote?

The usual trick for ensuring Excel treats data as text is to prepend a single quote to the cell data. However, this applies to an Excel file, not a CSV. Adding the single quote in a CSV displays the quote in the cell. Not good.

Prepending a Space

I found that adding a space to the front of the number range, so that the CSV column content appears as " 4 - 5" did the trick. When the CSV is opened in Excel, Excel recognizes it is dealing with text and displays the value as text.

The downside to prepending the space is that it appears in the Excel cell. When the user updates the data, they must remember to retain the space if they wish Excel to properly show the data again.