Stop Excel changing CSV text into dates

[Paul E Collins]

My application generates CSV data files. Users view these in Excel.

When a row of the CSV file contains the text 1-1, Excel incorrectly renders
it as a date (1 January). If users then format the cell as text, they don't
get 1-1 back but a useless number (the Excel numerical representation of 1
January).

How can Excel be made to open a CSV file (when the user launches that file
from Explorer) without screwing with it? It just needs to display the text
in the file and not try to "format" it. I could not find any suitable
setting in Tools>Options, nor does placing various sets of quotes around the
1-1 appear to have any effect.

Eq.

 

[RadarEye]

My application generates CSV data files. Users view these in Excel.

When a row of the CSV file contains the text 1-1, Excel incorrectly renders
it as a date (1 January). If users then format the cell as text, they don't
get 1-1 back but a useless number (the Excel numerical representation of 1
January).

How can Excel be made to open a CSV file (when the user launches that file
from Explorer) without screwing with it? It just needs to display the text
in the file and not try to "format" it. I could not find any suitable
setting in Tools>Options, nor does placing various sets of quotes around the
1-1 appear to have any effect.

Eq.

Hi Paul,

I have craeted a CSV file using asc(0150) as separator for 1-1.
This is slightly wider then the hyphen.
Otherwise maybe you can use an underscore at this point.

HTH,


Wouter

 

[Joel]

You have to change the CSV to XLS to prevent the change. I would renamve the
CSV file to TXT. then inport the TEXT file into excel using Data - Import
External Data - Import Data. You can then specify in the wizard to import
Text.

You can do the same thing using File - OPen - Text file (*.PRN,*.TXT,*.CSV)

 

[Paul E Collins]

You have to change the CSV to XLS to prevent the change. I would renamve
the CSV file to TXT. then inport the TEXT file into excel using Data -
Import External Data - Import Data. You can then specify in the wizard to
import Text.
You can do the same thing using File - OPen - Text file
(*.PRN,*.TXT,*.CSV)

That does work, but I suspect users would grumble at having to go through
the additional dialogue box. I also considered RadarEye's idea about
replacing the hyphen with a similar character, but it's not really
acceptable in case they copy the value and paste it back into our system for
searching, or even worse copy it into a data file for import -- it would no
longer match up with the original value.

I have a feeling I will have to change our export feature to offer formatted
XLS as an alternative to CSV, but it seems like a lot of effort just to make
text display as text!

Thanks for the suggestions.

Eq.

 

[timmg]

My application generates CSV data files. Users view these in Excel.

When a row of the CSV file contains the text 1-1, Excel incorrectly renders
it as a date (1 January).

Try padding a space in front, eg

test, alpha,123, 1-1
test2, beta,234, 1-1
t3,theta,3432, 1-1

The apostrophe is the ancient text indicator, but it reads as a " ' "
when you first open, but converts to invisible if you edit the cell.

HTH,

tim