an imported text field from Excel displays as an exponential number in Access

[Paul James]

I'm trying to import an Excel worksheet into an Access table, and one of the
text fields in Excel contains numbers stored as text. In Excel, one of
these values displays as:
9782946


But after I import the worksheet into Access, it displays as:

9.78295e+006

It appears that Excel is sending this data to Access as a number, rather
than as text. I realize that as far as Excel is concerned I'm storing a
number as text, and while it's all well and good that Excel is trying to be
so helpful, I would like it to treat the data as text. That's why I
formatted the range as text in the first place. And so I would also like it
to export the data to Access as text, and not as a number in exponential
format.

Can anyone tell me how to do this so that if I'm importiong a number like
9782946 it will arrive in Access in exactly that form, and not as
9.78295e+006?

Thanks in advance.

Paul

 

[Immanuel Sibero]

Hi Paul

I think you can accomplish this by defining your access table first, so that
when you're importing you're actually moving data from Excel to an already
existing table in Access. Of course, you would define the table field
receiving this particular data as a text field.

Otherwise, you can also add an apostrophe (sp?) --> ' in Excel, at the
beginning of the data to force it to be label.

Immanuel Sibero

 

[Paul James]

Thanks Immanuel - I tested your solution and it works fine.

I should mention that I did create the table in Access, and format that
field as text, but I still have the problem. So it appears that I have to
use a workaround like either yours or the one PegL suggested in the other
thread.

I appreciate your help.

Paul

 

[Paul James]

Both PegL and Immanuel suggested perfectly good ways to solve this problem,
and they both work.

However, it seems strange that we have to go through those extra
arrangements to get Access to import text data from Excel into a text field
in Access. Does anyone know if there's a way to format the field in Excel
in the first place so we don't need a workaround to receive the data
properly in Access?

 

[Immanuel Sibero]

Hi Paul,

I remember reading an Access MVP's response on this, I think it was on the
Access.Externaldata or Access.Conversion newsgroup.

MS Access actually guesses the data type by reading the first few (10 or
20, maybe) lines of Excel data. If they are numeric, then Access considers
the whole column to be of numeric type. I havent really played around with
this, but I'm curious if you just put an apostrophe on the first line, if
that would have the same effect of telling Access that the whole column is a
text column.

Immanuel Sibero

 

[Paul James]

I'm curious if you just put an apostrophe on the first line, if

that would have the same effect of telling Access that the whole column is a
text column.

Hi Immanuel - Yes, it would. That's essentially what PegL suggested in
microsoft.public.excel.misc which for some reason doesn't show up in
access.tablesdesign. So I tried it and it worked. So it's a solution. But
again, it seems somehow misguided to require us to jump through hoops like
that. Sometimes microsoft developers go overboard in providing their
applications with "helpful" features that are more trouble than they're
worth. For my part, I'd rather be the one controlling what happens to my
data, rather than having some program features trying to do my thinking for
me to that extent. In this case, it appears we have to provide some VBA
code to undo these "helpful" features in Excel.

Paul