Importing Excel Spreadsheet into Access - decimal point precision

davidvorob

New Member
Joined
Mar 16, 2021
Messages
9
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
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?
 
Upvote 0
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
 
Upvote 0
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!"
 
Upvote 0
Is it possible to set the datatype of the numeric column to double in the staging table?
 
Upvote 0
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.
 
Upvote 0
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).
 
Upvote 0
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?
 
Upvote 0
Something else I just thought of: Are the numbers in Excel the result of a calculation?
 
Upvote 0
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).

1616016189189.png



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.

1616016213038.png



1616016220857.png
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,267
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top