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
 

JonXL

Active Member
Joined
Feb 5, 2018
Messages
459
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
What if you update your Excel to show more decimals by clicking the little button with the 0s and arrow? Just looking at it, it seems that it's correctly importing what is in the spreadsheet in a WYSIWYG manner...
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

davidvorob

New Member
Joined
Mar 16, 2021
Messages
9
Office Version
  1. 2016
Platform
  1. Windows
i can do that, but the premise is that i have hundreds of auto-generated large spreadsheets that i'm trying to import with multiple tabs each. I don't really want to be hitting the increase decimal points in excel prior to the import process (and then have to revert back as other users of the same spreadsheets prefer not seeing the many decimal points). That is why i'm trying to get an automated version to get the values inside the cells as opposed to what is being shown.
 

JonXL

Active Member
Joined
Feb 5, 2018
Messages
459
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
But to answer my question, does changing the displayed decimal units in the worksheet also change what shows up in the import?
 

james_lankford

Well-known Member
Joined
Jan 11, 2009
Messages
1,181

ADVERTISEMENT

is it actually an excel file or is it a csv

I swear I've had a similar problem with a csv
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,832
Office Version
  1. 2019
Platform
  1. Windows
I don't think there is anything you can do to fix this using the current approach (which clearly doesn't do the job) so it probably is time to start working on a new plan....
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,980
Office Version
  1. 365
Platform
  1. Windows
I often find the interaction between Excel and Access to be problematic. In trying to make things easier for the user, they have set it up so Excel and Access talk to each other, and interpret the data themselves. The problem is that you have no control of it, and sometimes they make "assumptions" that are either incorrect, or do not work for the task you are trying to accomplish.

There are some potential workarounds you could explore, such as:

1. If re-formatting the column in Excel gets the data to where it needs to be so that things work the way you need in Access, you could create an Excel macro that first loops through all your files and does the re-formatting required. Once you set this up, it is just a matter of running this macro first, which you could call right from Access VBA or in VB Script, or however else you would like to run this entire process.

2. If you cannot get the files the way you want in Excel so Access treats them the way you want, you can create an Excel macro that formats the files and exports them to something like a CSV or Tab-Delimited Text file. And then import those files into Access. The advatnage to doing that is you have the ability to control how the data comes into Access when importing Text files (where you do not with Excel files). And then like the first option, you can program all this to run.

Hope that gives you some ideas to think about.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,013
Messages
5,639,548
Members
417,097
Latest member
miguel_z

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