Importing Excel Spreadsheet into Access - decimal point precision

[davidvorob]

Hello,

I am trying to import hundreds of auto-generated excel files into a set of tables in my access DB. The way I'm doing it, through a VBA module, is to loop through each excel file, put it into a temporary staging table that is structured with all text fields, and then type-cast and append into appropriately structured tables (with doubles, where necessary). What I am noticing is that although the excel files have many numerical fields, going out to 13 decimal points, excel formats it by showing only one decimal place. Access is picking up only the one decimal place that excel is formatting it to. Is there a way to get all of the precision that is contained in the file? Is there a work-around without going into each individual file and re-formatting it (multiple tabs per file)?

Thank you,
David

 

[JonXL]

At what point are the extra numbers being dropped? The import to staging or staging to primary table?

Also, can you post the code you're using?

 

[davidvorob]

they are being dropped in the importing to staging table. My code is as follows (have a few of these based on the tabs i need). The file name and location has already been brought in by a different function and stored in a log table, referenced below (based on a lAttributionID).




Function importSummaryTableAndPerformance(lAttributionID As Long)
Dim sSQL As String
Dim rst As DAO.Recordset

Dim sFileLocation As String
Dim sFileName As String

Dim dPerformancePORT As Single
Dim dPerformanceBMK As Single
Dim dPerformanceRelative As Single

sFileLocation = DLookup("Directory", "tbl_Attribution_Log", "ID = " & lAttributionID)
sFileName = DLookup("FileName", "tbl_Attribution_Log", "ID = " & lAttributionID)


sSQL = "delete * from tbl_TempStaging"
DoCmd.RunSQL sSQL

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tbl_TempStaging", sFileLocation & sFileName, False, "Global Portfolio Summary!"

' Insert into Attribution Summary Table
sSQL = ""
sSQL = sSQL & " INSERT INTO tbl_Attribution_Summary " & vbLf
sSQL = sSQL & " SELECT " & vbLf
sSQL = sSQL & " " & lAttributionID & " AS AttributionID, " & vbLf
sSQL = sSQL & " B.ID AS BucketID, " & vbLf
sSQL = sSQL & " A.[F9] AS AttributionAmount " & vbLf
sSQL = sSQL & " FROM " & vbLf
sSQL = sSQL & " (SELECT * FROM tbl_TempStaging WHERE NOT ISNUMERIC(F5) AND ISNUMERIC(F9)) A " & vbLf
sSQL = sSQL & " INNER JOIN tbl_Mapping_Summary B " & vbLf
sSQL = sSQL & " ON A.F5 = B.BucketName " & vbLf

DoCmd.RunSQL sSQL

sSQL = "delete * from tbl_TempStaging"
DoCmd.RunSQL sSQL
end function

 

[davidvorob]

main line, is on the acImport line item...i've tried a few variations with acSpreadsheetTypeExcel9, 12 or 12xml.

putting in a breakpoint and running it on the first iteration, i have this for the full line with sample sFileLocation & sFileName:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tbl_TempStaging", "C:\Monthly\HPA.1722_Monthly.xls", False, "Global Portfolio Summary!"

 

[xenou]

Is it possible to set the datatype of the numeric column to double in the staging table?

 

[davidvorob]

not really as the staging table is used for multiple imports and sometimes it's not a numeric value. I treat each import in a special manner. Honestly, i don't think it has anything to do with the code or how i setup the staging table. When going through the import wizard, i only see the cutoff values as well. Something tells me it's with how access opens up the excel file.

 

[xenou]

I guess that's possible. You can experiment to find out the affect of formatting on the imports (also the affect of a text column vs a numeric/double column in the staging table).

 

[JonXL]

Can you show an example of the Excel data in Excel and then how it looks when imported?

And when you say it's not in the import window with the extra digits either, what are you setting as the data type when doing that test?

 

[JonXL]

Something else I just thought of: Are the numbers in Excel the result of a calculation?

 

[davidvorob]

sure....just created a simpler (cleaner) test file (test.xlsx) that has two columns and three records (image attached). It shows that column B is formatted to show 1 decimal point (1.2), but underneath, it has many (1.23456879).

Next, there are two snapshots of the access import wizard (and i'm getting the same thing when i try loading via VBA TransferSpreadsheet command) with both text and double as the field types.