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]

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...

 

[davidvorob]

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]

But to answer my question, does changing the displayed decimal units in the worksheet also change what shows up in the import?

 

[davidvorob]

yes, it does.

 

[james_lankford]

is it actually an excel file or is it a csv

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

 

[xenou]

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]

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.