CSV File Export Gets distorted by Excel

This question didn't really fit in any other forum thread in particular, but I'm sure other people have probably encountered this (and may not even know it), with Excel and other SpreedSheet applications.

I have an app that allows users to export a report of products that they have ordered.

Some products have unique identifiers of the form:

01-06
02-2005
etc.

The problem that I am encountering is that when this information is exported to a CSV it shows up as:
"01-06"
"02-2005"
etc.

Now, when you Import this information into Excel, it likes to convert this information to:
6-Jan
Feb 2005

If i try to convert these cells back to just Text, i end up with:
37262.00
38384.00

The number of days since Jan. 1st 1900 (I _think_)

Does anyone have any ideas how to prevent this from happening?

Is there a way to turn off AUto Formatting in Excel?
Is there an Escape Character to use in a CSV file to force the Spreedsheet application to recognize data as text only?

Thanks,
Dave

not sure if this is also true for CSV, but excel uses the apostroph (') as fist character for marking text fields...
give it a try and post the results here, plz.

If i place single quotes around the items that Excel is misformatting, like follows:

"Order ID","Line Item No.","Product ID","Ship To Identifier","Cost Center","Extended Price",
"ciu-JoAnn","0",'01-26',"Sault Saint Marie-CIU","CIU","$3.46",
"ciu-JoAnn","1",'09-08',"Sault Saint Marie-CIU","CIU","$11.92",

the cell then shows up in Excel w/ the single ' contained within, on both sides of the data:
'01-26'
'09-08'

If i instead do this:

"'09-08" (Note the single quote inside the double quotes)

then recieve '09-08 in the cell, but if i double click within the cell, and press enter, then the cell is formatted correctly.

It seems as though there should be some way in order to get Excel 2000 to _not_ perform its auto formatting to dates. (If anyone can tell me the steps to disable this feature I would appreciate it).

Dave

i don´t know of an easy solution. a workaround:
- either make a macro that you run after importing to remove the '
- or import using VBA instead. this will work 100%

I've had similar problems with Excel's auto-formatting and had to do macros (I hate macros). Isn't it amazing how the more someone tries to make things easier, the harder things get?

export as .txt, then open with excel... you get to choose the delimiter and then on the final screen you can select columns and decree their data type - choose text and your examples come out okay.

this might help you if you're doing a one-off export, however, it doesn't sound user-friendly enough for your situation... thought i'd contribute anyway...

MS really does make it difficult!!

Came across this (amongst others) when trying to do something similar myself.

Put an equals sign at the front of the field and MS appears to present the data 'as-is' (in most cases!)

i.e.
"colA text","717-213",="717-213"

column A is text so that's ok
column B gets calculated (u do the maths) - cheers excel!
column C is as is (at last)

enjoy,