Fields imported from Excel are in scientific notation

[pbuethe]

I am clicking a button to import an Excel spreadsheet into a new table in Access, using TransferSpreadsheet. This has not been used for about a year, but before that it was working perfectly numerous times. Now there are two columns which should be importing as text, but are coming out in scientific notation. The columns are MOTHERSS (mother's social security number) and MED_REC (medical record number). MOTHERSS is 9 digits, but should be treated as text. MED_REC can be alphanumeric or just numeric; if there are alpha characters they are usually at the beginning of the field. The weird thing is: MOTHERSS seems to be importing correctly if it has a leading zero. and sometimes MED_REC is correct (e.g. alphanumeric values are correct in Access).

I have tried a number of suggestions I found on searching the forum, including: adding a dummy record with the correct data types, importing to an existing table rather than a new table, sorting a column, checking data types, etc. The same thing always happens.

Why should this be happening now? Please help! Thank you.

 

[Pat Hartman]

The column in your table is defined as numeric and it shouldn't be. It should be defined as text.

 

[pbuethe]

The columns are defined as text in the table.

 

[Pat Hartman]

Are the columns too narrow to show the entire field? I haven't seen Access turn a text field into scientific notation but it will display numeric fields that way if the column width is too narrow.

 

[pbuethe]

They look like they are wide enough. It seems like it rounds off usu. the first 7 digits of the number and changes it to scientific notation. So it ends up with, e.g. 1.87557e+006 . Most of the medical record numbers are only 7 digits to begin with, so this example was originally between 1875565 and 1875574.

Also, I tried fixing the table manually, then when I exported it back to (a different file in) Excel, it did the same thing in the opposite direction.

 

[pbuethe]

Working with a co-worker, we think we discovered what caused the problem. We receive the original Excel file used in this process from an outside agency. They used a different format than in the past. What is relevant to the problem is, they did not use a consistent format throughout the MOTHERSS and MED_REC fields. I need to fix these fields to be consistent and then the export should work. I will let you know if it does.

 

[pbuethe]

Well, first I removed the formulas in the MOTHERSS field on the values starting with zero, e.g. TEXT("012345678","00000000#") was changed to 012345678. However, the other values still came out in sci. notation. Then I formatted the column with the custom format 000000000. Then the MOTHERSS field came out OK. However, I am still at a loss for the MED_REC column since the format of that one is variable. It may be 6-10 digits or start with one or two letters. Therefore I cannot apply a custom format to the whole column. This field also has the TEXT formulas on the values starting with zero.

 

[pbuethe]

I finally got it to work! Starting from the Excel file in its original format, I edited each value in the two columns that was converting to scientific notation (i.e. those with at least 7 digits without a leading zero or letter). All I did was double-click the field, type a non-numeric character, then delete it. After saving the file, I then imported it into the database and all fields looked normal. When I sent it back to Excel it still looked OK. It would be nice to have a more automatic way of fixing this in case it happens in the future, but for now everything is fine. Thanks for your suggestions, Pat.

I did it more automatically by:

1.starting in the Excel file, created a new column T which concatenates a letter to the MOTHERSS column.
2.created column U which strips the added letter from column T.
3.created columns V and W which do the same for the MED_REC column, however due to the variable format of the MED_REC, the letter is concatenated to the beginning of the field.
4.copied the values from column U to MOTHERSS.
5.copied the values from column W to MED_REC.
6.deleted columns T-W.
7.Saved the file.

The file imported OK.