Stop Excel changing CSV text into dates
 

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 ToolsOptions, nor does placing various sets of quotes around the
1-1 appear to have any effect.

Eq.

Stop Excel changing CSV text into dates
 

On 6 jan, 17:03, "Paul E Collins"
wrote:
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 ToolsOptions, 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

Stop Excel changing CSV text into dates
 

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" wrote:

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 ToolsOptions, nor does placing various sets of quotes around the
1-1 appear to have any effect.

Eq.

Stop Excel changing CSV text into dates
 

"Joel" wrote:

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.

Stop Excel changing CSV text into dates
 

On Jan 6, 10:03*am, "Paul E Collins"
wrote:
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