Stop Excel reading 01/02 as 1st February 2007

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I know that you can stop it when entering data by using a ' before the data,
but I have exported data as a csv from Access and want to read it in Excel;
yet every time I open the csv it converts the available years (i.e. it
ignores 00/01, but converts 01/02, 02/03 etc) to dates. Is there a box
somewhere in 'options' that I have to tick/untick?

Thanks.
 
Rename your .csv as .txt, then the text import wizard allows you to specify
the format of each column at the import stage.
With a file named .csv, Excel always thinks it knows best.
 
Hi Jon

Rename the file as .txt before importing.
That will cause the Data Text to Columns wizard to be invoked.
Mark the relevant column as Text before completion of the wizard.
 
If you do a File, Open Excel will automatically parse CSV files using its
immutable rules.

Here's two ways to go:

- Change the file extension to something else, like TXT, and do a File,
Open. The Text Import Wizard will start automatically. When you get to the
third screen of the wizard specify Text as the format for the problem field.

-Use Data, Import External Data (as it's called in Excel 2003; the name may
vary with other versions). This will allow you to import the data of the
CSV file into an existing workbook (open an empty one before you start if
you don't want to use an existing workbook), using the Text Import Wizard.

--
Jim
|I know that you can stop it when entering data by using a ' before the
data,
| but I have exported data as a csv from Access and want to read it in
Excel;
| yet every time I open the csv it converts the available years (i.e. it
| ignores 00/01, but converts 01/02, 02/03 etc) to dates. Is there a box
| somewhere in 'options' that I have to tick/untick?
|
| Thanks.
 
Excel automatically recognizes any file with a "CSV" extension as one of it's
own, you do not get any iport options. A work around might be:

Rename your file exported from Access with a "txt" extension. Then open that
file with Excel. You will then have an opportunity to define the format of
each field as General, Text, Date, number and so forth. Simply define the
problem field as text.

HTH
 
First re-name the file from .csv to .txt
This will cause the Import Wizard to be invoked. Tell the Wizard that the
field is not a date.
 
Back
Top