Text Import from Excel comes in as scientific notation 1

[SetBuilder]

When I use the transferspreadsheet command to import an Excel spreadsheet, one of the text fields (Course-ID) comes in as a scientific notated field. For example, 20012911 comes in as 2.001291+07. when I convert that back to a string, as:
strCourseID = str(vFieldIn1) I get: 20012900, loosing the last 2 digits.

The cells are defined as text in Excel.

I have tried converting it to a txt file, and a csv file, but because of the data, neither of these are very good options.

Any suggestions?

 

[dshatto]

I ran into this problem in another context. Since there weren't any posted replies I had to figure it out myself:
- by dshatto@excite.com


Problem:
When data from an Excel file is imported to Access, processed and exported to Excel, some numbers sometimes end up as text characters of the original number’s scientific notation (i.e. SSN as 7.84877e+008).


Solutions to problem:

Safest: Before importing into Access, open Excel file, select whole spreadsheet, change cell number format to General, save file. Then you can import the spreadsheet, avoiding this problem.

Alternative 1: Change cell’s number format to Text before typing the number. This could be done to individual cells, or to entire columns, but don’t change this format for the whole spreadsheet unless you know all the data will be text.

Alternative 2: Type an apostrophe (‘) at the beginning of each number being entered. Then the data type will be Text regardless of the cell number format.

Note: Once Access converts a SSN to text scientific notation, you cannot accurately convert it back to a 9 digit number; only the first 6 digits are retained. You have to retype the number.


Cause of problem:
When importing an Excel spreadsheet, Access determines field data type from the data type of the cells in the spreadsheet. When:
- the cell’s data is a number with more than 6 digits, and
- its data type is Numeric, and
- its number format is Text, and
- Access import assigns Text data type to the field (based on the values in that field in the first 8 records),
then the cell will import into Access as text characters of the cell’s value in scientific notation (with a 9 digit SSN the format is n.nnnnne+nnn).


Steps to reproduce problem:
1. Start with Excel data file where cells have General cell number format (such as a spreadsheet exported by Access).
2. In a cell that has Text data type, enter a number longer than 6 digits.
3. Change the cell’s number format to Text, save file.
4. Import spreadsheet into Access. The record’s field that you changed in Excel will show text characters of the cell’s value in scientific notation for the number you entered.

 

[SetBuilder]

I never got an answer until your, (7 months later) so I also figured out a "work around" by putting an apostrophy in front of the serial number. I wrote a quick macro for the user to fix the fields after the data is inputted, and before it is exported to Access:
For x = 2 To intLimit
Cells(x, 4).Select
ActiveCell.FormulaR1C1 = "'" & ActiveCell.Value
Next
where intlimit is the number of rows in the spreadsheet.
the "4" is the column (Column D)

But, thanks for replying.

 

[bonnief]

I had the same problem with Access losing the leading zeroes on a batch number (ie. 0012345 would convert to Access as 12345). I insert a dummy record as the first record in the excel spreadsheet. I key in AAAAAAA (adjusted for field length) as each field value in that 1st record. Access then sees the field as text and keeps the leading zeroes.