Numbered Text appearing in Scientific Notation

[Guest]

I tried importing an excel file. One of the fields is a product number
(alphanumeric) which can be all numbers or combination of letters and
numbers. I've defined it in Excel as text and made sure that all the data
are in text. However, I still keep getting this 4.00000e+06 when it should
be 4000000; I specified this field to be text. I've even tried appending to
another table with the correct datatype with no luck. I checked the Excel
file to be sure that the format is correct, and it is (there was a problem
where it was coming out in that format, but I had to click on each cell to
change it from Number to Text). How can I correct this?

 

[User]

One idea is to save the Excel file as text, then import it as a text file.
Formatting in Excel seems to be for the purpose of viewing only and doesn't
necessarily affect the underlying value, so when you import directly you
might not get what you see. I've had that problem with leading zeros too.
If you save it to text at least you can look at the file and see what is
getting imported.

Doug

 

[Nestor Stura]

I had the same problem importing to a SQL Server table and solved it
formatting the column in excel to Text. (Select the whole column, right
click, cell format and select text). After that, just save the file in .txt
or .csv format, disregard an error message that appears telling that you
will lose the format and now you can import in Access.

Be careful, just in case you must repeat the operation, you will need to
reformat the column because saving it as a text file changes your Text
column format.

Hopes help,

Néstor

 

[Ron2006]

Another possible workaround:

go into the spreadsheet, add a row 2 and then put a single character
(or date) in each of the columns that corresponds to the true data
structure. For this particular column put a letter.

Access looks at a certain number of charcters to determine what to
define the data as on an import. By forcing it in row 2 you eliminate
certain types of errors.

Ron

 

[Guest]

Thanks everyone for the input. Eventually had to get the data in text form
(it was Business Object report) since it has grown to over 60K rows.

Ron,

This was the first workaround I tried prior to posting here. Interestingly
enough, it didn't work. I thought the first row after the headers would set
the formatting for the rest (and in most cases it did), but in this case it
didn't. I'm guessing it takes more of a sampling than simply looking at the
first row.

Fred

 

[Ron2006]

hmmmmmmmm....

That approach worked for me, but it was in the opposite direction. I
had a field I needed to be alpha, but the first n rows happend to be
numeric, and so any that had the alpha were being dropped.

I am wondering if that happens to be the way it is showing up in the
spreadsheet since it is text in all your references in access.

Are you importing or linking. In general, I have gotten a lot better
results with importing that I have with linking.

Ron