Problem to import numbers with decimal sign from Excel to Access

[straymae]

Hello

i need to import an excel-sheet into Access via an Access VBA module that contains for certain columns numeric fields. All fields in the Excel-sheet are in Text-format.

The problem is that numeric values that contain a comma as decimal separator are wrong imported via my code.

My regional settings decimal separator for numbers is comma ",".
Before import, my destination table is created with no data and with all columns having the TEXT-datatype.

When I try the TransferSpreadsheet method (TransferType:=acImport, ...) then I receive something like this :

4498494,77 --> 4.49849e+006
13922,48 --> 13922.5
566127,68 --> 566128
68,44 --> 68.44

Comma are altered to points, large numbers are rounded. The first record contains 4498494,77 as value. All values in that columns are numbers.

Can anyone could provide any assistance in this matter? Thanks.
By the way, I do not wish to alter the Excel sheet's format.

Stijn, Belgium

 

[llkhoutx]

Query you imported data using the "clng" or "cdbl" function on each text field which is supposed to be numeric.

 

[straymae]

This doesn't seem to work. Here's an extract from my code that i use to test it.

Set adoRST_X = New ADODB.Recordset
With adoRST_X
.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sFile & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
.LockType = adLockPessimistic
.CursorType = adOpenDynamic
.Open Source:="SELECT CDBL(BALANCE) FROM [" & sNomTable & "$]"
Do While Not .EOF
strResult = vbNullString
For j = 0 To .Fields.Count - 1
strResult = strResult & .Fields(j).Value & " | " '.Fields(j).Type
Next j
Debug.Print Left$(strResult, Len(strResult) - 3)
.MoveNext
Loop
.Close
End With

Value in XLS (TEXT format) : 1095471,39
Result after the above test : 109547000000

Using CCUR gives the same result.
CLNG even blanks out the field.
When I don't use a conversion function, then the result is 1.09547e+006
....


When I use a point as decimal separator in the Excel sheet, then the only good result is achieved by not using a conversion function :
SELECT BALANCE FROM ...

I have a strong suspicion that VBA uses the American notation (comma as thousand separator and point as decimal separator) when evaluating datatypes on import mode.

Does anyone have experienced similar behaviour in this matter?

Thanks a lot!

 

[workmad3]

4498494,77 --> 4.49849e+006
13922,48 --> 13922.5
566127,68 --> 566128
68,44 --> 68.44
__________________________________________________________

I have a strong suspicion that VBA uses the American notation (comma as thousand separator and point as decimal separator) when evaluating datatypes on import mode.

If the information you gave in the first post was correct (as in those were the results) then VBA is interpreting the , as a decimal point correctly. As 68,44 is 68.44 using a decimal point and 4498494.77 is also what it gave as the equivalent. The rounding problems are probably due to access storing the data using floating point data types which are always prone to rounding errors. As it stands though. I cant see the problem with the import as it has imported the data items correctly(if no conversion is used) but it isnt using the , as a decimal separator it is using . instead

 

[workmad3]

4498494,77 --> 4.49849e+006
13922,48 --> 13922.5
566127,68 --> 566128
68,44 --> 68.44
__________________________________________________________

I have a strong suspicion that VBA uses the American notation (comma as thousand separator and point as decimal separator) when evaluating datatypes on import mode.

If the information you gave in the first post was correct (as in those were the results) then VBA is interpreting the , as a decimal point correctly. As 68,44 is 68.44 using a decimal point and 4498494.77 is also what it gave as the equivalent. The rounding problems are probably due to access storing the data using floating point data types which are always prone to rounding errors. As it stands though. I cant see the problem with the import as it has imported the data items correctly(if no conversion is used) but it isnt using the , as a decimal separator it is using . instead