number in excel Text cell is overflow while importing to access

[Ronald_yoh]

Hi there,

I have an excel file contains a value: 740515322 in the cell. The cell itself has been converted to Text format. While importing the excel to MS Access, that value is converted to 7.40515e+008.

does anyone know how to solve this? thanks

[CreganTur]

The simple answer is because you're tyring to import a string into a number data type. If you change the field to a string, it will import correctly. Do you need it as a number for calculations?

[Ronald_yoh]

I have checked the excel cell is on text format, when i imported to access the field also in text format. all i want is everything in text format (no calculations need to be performed). do you know what could have been wrong?

[MaximS]

Can you explain which method you are using to import your data?

I've tried with importing wizard and is working absolutely fine.

MS Access >> File >> Get External Data >> Import

[Ronald_yoh]

Hi.. I have attached the excel sheet that I'm having a trouble with. Try to import this excel to MS Access then you'll get the overflow value.

Please refer to the second column in the excel for the issue.

also, this file was duplicated (using saveas method) from another excel file and the cells were converted to TEXT using VBA as follow:


xlBook.SaveAs sNewExcel

'making it visible so it's available to be udpated
xl.Visible = True
xlBook.Windows(1).Visible = True

bOpen = True

For iLoopSheet = 1 To xlBook.Worksheets.Count

Set xlSheet = xlBook.Worksheets(iLoopSheet)
xlSheet.Activate

'select the entire range
xlSheet.Cells.Select
With xl.Selection
'format cells to text
.NumberFormat = "@"
End With
Next iLoopSheet

[CreganTur]

The issue here has to do with Excel, not Access, since you said that the spreadsheet you are loading is created using Save As and the text formatting is created via VBA. Excel will often change numbers to scientific notation on its own because of its formatting procedures. I've run into countless cases where a large number appears correctly on a spreadsheet when the cell is set to General or a numerical format, but changes to scientific notation when I change the format to Text.

The best solution I can think of at the moment would involve a process change on your part- the original spreadsheet will need to have that column set to Text formatting from the start. That will avoid this entire issue.

HTH:thumb