I have a problem importing the information from Excel to Access table by using the following code:

Code Block
DoCmd.TransferSpreadsheet acImport, 8, "tmpComs", _
locSubmissionLocation & varFormWeek & "\" & arrCountries(varCurNumber) & varFormWeek & ".xls", _
False, "Commitments!b16:n" & lastDirtyRow

The problem I have is that some of the numbers in one of the columns consisting of 7 or more digits are imported as scientific notations, e.g. 1017342 is changed to 1.01734e+006.

I tried Googling it, also trying different solutions, but none of them seem to be working. Here are some discoveries while trying to tackle this issue:

  • the cell format is set to Text, so I cannot understand why it is treated as a number
  • I realised that Access does a guess on what type the information is and the way how this guess is done is determined by HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel registry key. The settings on my computer are set ImportMixedTypes = Majority Type and TypeGuessRows = 1 so that the guess would be made by the first row only (in this case row 16).
  • to support above guess I have the following code which adds a dummy row in row 16 so that each column is treated as appropriate data type. As you can see column B is set to "A", so Access should assume it is a text field. Therefore, I cannot figure out why it change it to number.

Code Block

xlsCurrentFile.Cells(16, 1).select
xlsCurrentFile.range("B16") = "A"
xlsCurrentFile.range("D16") = "A"
xlsCurrentFile.range("E16") = 0.1
xlsCurrentFile.range("F16") = 0.1
xlsCurrentFile.range("G16") = 0.1
xlsCurrentFile.range("H16") = 0.1
xlsCurrentFile.range("I16") = 0.1
xlsCurrentFile.range("J16") = "A"
xlsCurrentFile.range("K16") = "A"
xlsCurrentFile.range("L16") = 0.1
xlsCurrentFile.range("M16") = 0.1
xlsCurrentFile.range("N16") = "A"

  • now the trickiest part is that from 43 Excel files which are imported every week some have this problem while some goes through this process correctly and even the longer numbers are correctly imported into the database as text. All of the files have the same layout and same cell types set as they are all based on the same template.
  • I also tried to change the cell type of the rows and columns which fail to import properly to General and it worked perfectly fine. In this case I have two concerns - 1) why in some files the records are correctly imported even though they are set to Text; 2) what causes such behaviour

Anyway, I am completely confused and out of ideas as Access seem to be working completely randomly. Any ideas would be greatly appreciated.



Re: Long numbers converted to scientific notation when using TransferSpreadsheet

Cringing Dragon

Might it have something to do with the text field size If it defaults to a length of 12 characters, it may "convert" long numbers into scientific notation in preference to truncating them. I guess the trigger point is 7 digits because you "lose" 5 characters to the e+006, and a sixth to the decimal point, which only leaves 6 characters. SO although the receiving field is text, it still recognises the incoming value as a number and does its "best" to convert the number into text - even though it's not what you want.

Re: Long numbers converted to scientific notation when using TransferSpreadsheet

Vox AC30

You are correct in asuming that Access is "guessing" the data type for the destination columns, it recogises the value 1017342 as a number and creates a field of a data type that supports the largest number that can be stored, which is Floating Point. The best solution is to have the table already created with the data type of the destination field to be a Long Int. Have a look at the data type of the destination column that Access creates by assumption..it will be Floating Point data type!

Re: Long numbers converted to scientific notation when using TransferSpreadsheet


Hi again, thanks for advice.

However... the table tmpComs is created in the database and field type is set to Text (255) for it. So what I have, basically (and logically, IMO), is the Excel file with a dummy row from which VBA should guess that it should be a text field as a source and on the other side a destination field, which is set as text as well. I cannot understand why Access, VBA or powers of God would want to interfere at any point between these two and do any assumptions that it is a number and try to truncate or do whatever with it - and now apparently it does. The saddest thing in here is that it looks like a completely random thing - sometimes it does convert the number to notation , sometimes it does not and I cannot see any logical criteria on which it chooses whether to do this action or not...

Any other ideas, guys Sad

Thanks a lot,