CODEWITHSUNDEEP
excelparsingdatecsv
HughieW
HughieW

Reputation: 149

Stopping Excel Date Parsing

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?

Upvotes: 2

Views: 1055

Answers (2)

Dheer
Dheer

Reputation: 4076

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.

Upvotes: 0

Codo
Codo

Reputation: 78985

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.)

Upvotes: 3

Related Questions