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

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

JonXL

Active Member
Joined
Feb 5, 2018
Messages
459
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

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

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

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,832
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

davidvorob

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

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,832
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

Active Member
Joined
Feb 5, 2018
Messages
459
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

Active Member
Joined
Feb 5, 2018
Messages
459
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Something else I just thought of: Are the numbers in Excel the result of a calculation?
 

davidvorob

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

Watch MrExcel Video

Forum statistics

Threads
1,130,008
Messages
5,639,512
Members
417,092
Latest member
AppyTrailToMe

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
Top