Imported numbers from Excel change to scientific notation

[Guest]

While in Excel I have changed my 10 digit account numbers into text format,
and then I import them into Access. Frequently, these numbers do not convert
correctly. They get converted into scientific notation and the trailing
numbers are lost. While converting Excel into Access, is there some way to
keep all the digits intact?

 

[Jeff Boyce]

Import as text, into a temporary table.

Append from the temporary table into the final table structure, using
queries. Force the text into the correct (integer, single, double) datatype
explicitly with CInt(), ... functions.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

This might work but, it is WHILE I am Importing as text into a temporary
table that the account numbers are changed to scientific notation format.

 

[Jeff Boyce]

I mis-spoke.

Create a table with text-type fields. Import into that table (I named it a
"temporary" table because it won't be the final version.)

This allows you to import values into a known table structure, rather than
relying on the wisdom of the wizard to decide.

After your data is in the "temporary" table, run the queries that parse and
coerce the data into the "permanent" tables with the correct data types.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

It worked. Thanks.

I mis-spoke.

Create a table with text-type fields. Import into that table (I named it a
"temporary" table because it won't be the final version.)

This allows you to import values into a known table structure, rather than
relying on the wisdom of the wizard to decide.

After your data is in the "temporary" table, run the queries that parse and
coerce the data into the "permanent" tables with the correct data types.

Regards

Jeff Boyce
Microsoft Office/Access MVP