importing numbers as text more info

  • Thread starter Thread starter Martin Lord
  • Start date Start date
M

Martin Lord

I'm importing it from an excel file. I already defined
the whole row as text by right clicking on the "A"
column, format cells, Category, Text. I import it to
access by using the import spreadsheet wizard. At step 4
of the import wizard in Field Options it recognizes the
partNumber field as text because by default it says: Data
Type: Text. But when I go ahead and finish importing it
still converts these numbers to the numerical value in
scientific notation.

The fields that are a combination of text and number or
just text are imported correctly.

Thanks.

ML
-----Original Message-----
Not enough info.
What kind of file are you importing from?
Text, Excel, other?

If Excel, then try putting a text value in the first row of data and see if
that helps.
--
Joe Fallon
Access MVP






.
Click to expand...
..
 
Try selecting the actual data under the A column direct and not just by the
the Column head. You don't mention which version of Excel you are using.
Older versions may require the ' in front of the data to indeed declare it
as text.
 
Martin Lord said:
I'm importing it from an excel file. I already defined
the whole row as text by right clicking on the "A"
column, format cells, Category, Text. I import it to
access by using the import spreadsheet wizard. At step 4
of the import wizard in Field Options it recognizes the
partNumber field as text because by default it says: Data
Type: Text. But when I go ahead and finish importing it
still converts these numbers to the numerical value in
scientific notation.
Click to expand...

In summary, your Excel column is successfully recognized as Text and
numeric values such converted to text but in an unexpected format. The
data type and values are determined by Jet on the Excel side before
they ever get to Jet on the MS Access side, therefore the cause of
unexpected format is something in Excel.

The only way I could reproduce your result i.e. to get Jet on the
Excel side to convert a numeric 1113276 to text '1.11328e+006' was to
apply a custom cell format of 0.00000E+000 using the Excel UI. Are you
sure you have removed the formatting from this Excel column?

Jamie.

--
 
Back
Top