CSV->Excel Data Problem

  • Thread starter Thread starter Mike Copeland
  • Start date Start date
M

Mike Copeland

Using MS Office Excel 2003. I have a data problem with populating a
set of large CSV files. Specifically, I have (some) data going into
various cells that implicitly converts to a "date value), even though
it's not.
In the CSV data, I have many values such as "FBM11", "PAT12",
"ABCO07", etc. These are displaying correctly (they're all text data),
but the value "MAY05" displays as "5-May". 8<{{
I know that I could manually change all these values by prepending a
"`" character, but this isn't feasible since I am formatting many
thousands of all data values in a program I'm developing. I tried to
insert the "`" character in all cells, but the data looks crummy for all
other values - which is unacceptable for this application. A typical
line is:
9024,"ABEL, CARTER","010164","M",4,"DCR13","MAY05","PAT12"
My question is: is there something I can insert into the CSV text for
this value that will cause Excel to treat this value as a/n text, rather
than a "date"?
 
Mike Copeland said:
In the CSV data, I have many values such as "FBM11", "PAT12",
"ABCO07", etc. These are displaying correctly (they're all
text data), but the value "MAY05" displays as "5-May". 8<{{
I know that I could manually change all these values by
prepending a "`" character, but this isn't feasible [....]
My question is: is there something I can insert into the
CSV text for this value that will cause Excel to treat this
value as a/n text, rather than a "date"?
Click to expand...

Not an individual basis ("this value"), but on a column basis.

Instead of opening the CSV file in Excel, use Import External Data to bring
the CSV data into the worksheet. The import wizard allows you to assign a
(limited) type to each column. In particular, you can assign type Text to a
column.

This is an common complaint about Excel interpretation of CSV files: it
always tries to interpret data as numeric representations, just as if you
had entered it manually, even if the data are quoted.

There are no standards for the interpretation (reading) of CSV files. Even
RFC 4180 covers only how to __write__ to CSV files. Moreover, RFC 4180
post-dates the Excel implementation by decades(!).
 
Back
Top