Stopping Excel Date Parsing

Question

I've written a script to export product data from a cscart database to a CSV file. It works perfectly, however when opened in MS Excel, it parses things such as "12-14" as dates, which shows "Dec 14".

I read http://excel.tips.net/Pages/T003228_Stopping_Date_Parsing_when_Opening_a_CSV_File.html but the client will not be willing to do that - is there any other way in the CSV file itself to force Excel to recognise it as a string.

I've tried wrapping all fields in double quotes, I've tried putting spaces around the hyphen - no luck. Any ideas?

Answer 1

Have you considered exporting the data in the Excel 2003 XML format? Unless you're using a built-in CSV writer, creating the XML format shouldn't be too difficult. And most important of all: you can exactly specify the data type.

A good example can be found in Wikipedia.

(Don't confuse this format with the latest XML/ZIP Excel format with the .xlsx extension.)

Answer 2

I tried this out by putting a single quote and it worked. The sample data I used was

ID,Name,String,Date

1,abcd,"'12-1",abc

21,zys,"'12-1",abc

13,pqrst,"'12-21",abc

However in the String is now displays when opened in XLS as '12-1, not something very nice, but an easy solution.