Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.
Import rounded numbers into Access from Excel
I have a file in Excel 2010 that has fields with numbers rounded to the nearest dollar. The value has cents in it, but it is just formatted to only show whole dollars. However, I wish to import the cents into an Access 2010 table as well. I currently use the following command to import the worksheet:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
strTable, strFile, True, wks.Name & "$"
For the most part, all of the values are being imported correctly. However, when there is a column where the top several rows do not have any cents, it seems that when it will not bring in the cents in the subsequent records. It will bring in the rounded number instead. I am not 100% sure, but I think there is something in Access where it looks at the top rows to determine the datatype it is importing. In this case, it seems that it thinks everything will be whole dollars and it ignores the cents.
I know that if someone manually formats the sheet to show 2 decimal places prior to importing, it will work fine. But, I am trying to eliminate the need for any modification to the original dataset. What is the best approach at doing this?
Thanks
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
strTable, strFile, True, wks.Name & "$"
For the most part, all of the values are being imported correctly. However, when there is a column where the top several rows do not have any cents, it seems that when it will not bring in the cents in the subsequent records. It will bring in the rounded number instead. I am not 100% sure, but I think there is something in Access where it looks at the top rows to determine the datatype it is importing. In this case, it seems that it thinks everything will be whole dollars and it ignores the cents.
I know that if someone manually formats the sheet to show 2 decimal places prior to importing, it will work fine. But, I am trying to eliminate the need for any modification to the original dataset. What is the best approach at doing this?
Thanks
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with premium.
Start your 7-day free trial.
I wear a lot of hats...
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
Unfortunately, I can't change to csv, because the spreadsheet is sent over with multiple tabs. #4 seems to be the best option for me unless I can find somehtng else. I'd rather not write code to loop through each cell, since there are several thousand rows with 30+ fields.
The field format is standard. I tried to change it to currency, but that did not work. It still imported the rounded number.
Thanks
The field format is standard. I tried to change it to currency, but that did not work. It still imported the rounded number.
Thanks
Your best bet is #1 then. You'll need to create a link for each sheet in the workbook. That can be done through the UI, or via code.
Try it manually first and see if you get the results you want.
Jim.
Try it manually first and see if you get the results you want.
Jim.
Hi
Will it be possible to ask the source team who gives you the files\outputs to set those columns as 2 decimals?
Will it be possible to ask the source team who gives you the files\outputs to set those columns as 2 decimals?
Creating links is also tough, because the number of links will grow indefinitely, since we are getting multiple files monthly and all have to go into 1 table, which will get messy.
I'm being told the source file will not change.....
I'm being told the source file will not change.....
<<Creating links is also tough, because the number of links will grow indefinitely, since we are getting multiple files monthly and all have to go into 1 table, which will get messy.>>
What you do is create a link on the fly. Once your done with it, delete it.
or
create links that point to a "known" dummy spreadsheet. As you work with each file, you will delete the existing dummy spreadsheet and copy the one you want to work with to that name.
All you will need then is one link per possible sheet in a workbook no matter how many files you have.
As long as you don't try and use the links while the file is not there, you'll get no errors. I've used this technique a number of times.
Jim.
What you do is create a link on the fly. Once your done with it, delete it.
or
create links that point to a "known" dummy spreadsheet. As you work with each file, you will delete the existing dummy spreadsheet and copy the one you want to work with to that name.
All you will need then is one link per possible sheet in a workbook no matter how many files you have.
As long as you don't try and use the links while the file is not there, you'll get no errors. I've used this technique a number of times.
Jim.
Linking is going to have the same issue. Access analyzes approximately 30 rows and based on what it sees, makes an assumption regarding data type. If you have the option of switching to A2013, there is some relief since MS has finally acknowledged this problem and allowed us to create import specs for spreadsheets as well as .txt and .csv files. Baring that, you are left with # 4 which isn't actually as bad as it sounds.
Since Excel is not my native language and I am not all that familiar with the object model, my first step when I need to perform automation is to open the workbook in Excel, turn on the macro recorder, perform the task I want to automate and then look at the VBA that Excel generated. I can then take that VBA and modify it slightly to run from Access. Essentially, all you need to do is to change the format of a specific column to be currency rather than general and show 2 decimal places.
Then you can save the workbook, close it, and use TransferSpreadsheet to import it.
Since Excel is not my native language and I am not all that familiar with the object model, my first step when I need to perform automation is to open the workbook in Excel, turn on the macro recorder, perform the task I want to automate and then look at the VBA that Excel generated. I can then take that VBA and modify it slightly to run from Access. Essentially, all you need to do is to change the format of a specific column to be currency rather than general and show 2 decimal places.
Then you can save the workbook, close it, and use TransferSpreadsheet to import it.
Experts Exchange Solution brought to you by
Your issues matter to us.
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Start your 7-day free trialIt's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access
From novice to tech pro — start learning today.
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with premium.
Start your 7-day free trial.
1. Link to the spreadsheet as a table. You can then work with it that way.
2. Have the user save the spreadsheet in a CSV format (or other text format). Then you can use TransferText and specify a import specification that you've written for the import.
3. Same as #2, but link to it as a table and use a schema.ini file to describe the fields.
4. Open Excel as an automation option (Excel needs to be installed where your doing the import), and then loop through the cells and pull the data. This gives you total control.
Let me know if you need anything more...
Jim.