Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    New Lounger
    Join Date
    Dec 2009
    Location
    Broomfield, CO
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Problems importing into Access 2010 from Excel - decimal places

    I have an Excel 2010 spreadsheet, where one of the columns is "Currency". I
    import that spreadsheet into Access 2010. When I then do a "SUM"
    Query in Access, the resulting number is off by a penny. When I then export the
    contents of the Access Table, and place it back into an Excel spreadsheet, I
    find that instead of being only 2 decimal places, the Currency column is now up
    to 4 decimal places.


    When I use the same spreadsheet in Excel/Access 2007, it works correctly.

    I have just setup this new computer, that came with Office Professional 2010 pre-installed
    by Dell.


    Any ideas?


    Thanks,


    David




  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    There were some significant issues with the original release of Access 2010 when importing and exporting to Excel, so check to make sure you have SP1 of Office 2010 installed. If you do, then you may be encountering rounding issues with Excel that work differently than Access. How are you moving the Access data back to Excel? If you are using copy and paste, you might try using the export process, or vice versa and see if the results differ. Note that currency formatted fields are stored in Access with 4 decimal digit precision, but that hasn't changed from 2007 to 2010.
    Wendell

  3. #3
    New Lounger
    Join Date
    Dec 2009
    Location
    Broomfield, CO
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for getting back to me Wendall.

    I'll have to check on the SP to make sure it is installed.

    One example....When I look at the number in Excel, it shows $46.16 but when I look at the same number in Access (by clicking on the number in the Table, not just highlighting it, but actually clicking on it), I see $46.1599 so it does not even need to be exported/copied back to Excel in order to see the problem. And when I look at the same file/record in Access 2007, when I click on the number in the table, it just continues to show the correct $46.16

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    David,

    Ah the blessings of binary representing decimal. This is a common problem in Excel, e.g. "What you see is NOT what you get!" Unless you use the Round() function on all your calculations that either use division or multiplication by a fractional number you will have this rounding problem. Excel displays the number in the cell based on the FORMATTING of the cell so if the cell is formatted to show 2 decimal places it will round the number for VISUAL presentation ONLY! When you calculate it will still use the entire number as stored thus your 46.1599 will display as 46.16 but will calculate using 45.1599 and those .0001s will cause problems if you have enough of them.

    To get around this problem you can import into Access using an Append query against a linked Excel sheet and use the round function in the Update query fields.
    Ex: appqryexample.JPG
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    New Lounger
    Join Date
    Dec 2009
    Location
    Broomfield, CO
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I checked, and I DO have SP 1 installed.

    I had known about the issue in Excel about the rounding problem in Excel, but as this number is coming directly pasted from an ascii Text File, with no operations performed on it, I did not think it would impact this issue. Even when I expand the decimals to 4 places in Excel, it still shows $46.1600, so no visibility of the changed number.

    When I leave as just a number in Excel (without converting to Currency) so that it looks like 46.1600, it imports into Access without issue, and maintains the correct decimal places. And then if within Access I change the field type to Currency, it also keeps the decimal places correctly.

    It appears that it only happens when I have a column in Excel that is set to Currency that is imported. As long as it is not set to Currency in Excel it imports correctly. I can then change it to Currency in Access, and all works, but that is a lot of extra work, that should not be needed.

    And this only is happening in Access 2010, and NOT in Access 2007.

    Thanks again,


    David

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    I suspect your issue may be with Excel 2010 and not Access 2010. As RG indicates, Excel stores data in a floating point form if you format it as currency, but if you leave it as text it stores the ASCI characters. On the other hand, when you import data into a currency field in Access (and in SQL Server) the data is stored as a BCD number so that the exact 4 digits are stored as such. Is there a compelling reason to take the data into Excel first? Access can handle text data pretty well - in fact we have handled a number of projects involving multi-million record tables going directly from text files to Access.
    Wendell

  7. #7
    New Lounger
    Join Date
    Dec 2009
    Location
    Broomfield, CO
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for your insights Wendell. Point taken about the floating decimal for currency in Excel. But why did it work in Access 2007, but not Access 2010? The source for both imports is the same.

    I have looked into bringing the Text directly into Access, but due to the complex structure of the Text file, it would not work. There are many extra un-needed rows in the text file (such as page headers, report date, report name, blank rows, etc.) that I can easily eliminate by first sorting in Excel before doing a Text-to-Column in Excel. Additionally, there are times when each customer record is on multiple rows. For Example, the Customer Number and Name are in the first row of the text file record, then the Item Number, Description, and Date Purchased is on the 2nd Row in the text file. These second data items must be parsed out and moved to new columns and moved up one row to get the entire record on one row in Excel. The text file comes from our client, and we have no control over the structure and content of the text file, so we have to live and deal with what they send to us.

    Thanks again.

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Question

    I'll do some further research on that and see if I can come up with an answer. Two questions:
    • Is the Excel workbook being saved in the older .xls format, or in the .xlsx supported by 2007 and 2010?
    • Also, are you using the Transfer Spreadshee function to import, or are you linking to the Excel workbook, or are you copying and pasting, or are you using the import function in Access?
    Wendell

  9. #9
    New Lounger
    Join Date
    Dec 2009
    Location
    Broomfield, CO
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks.

    The spreadsheet is saved as an .xlsx

    And I am going into Access, then using the External Data/Import/Excel function.

  10. #10
    New Lounger
    Join Date
    Dec 2009
    Location
    Broomfield, CO
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    One other note.....I tried creating a very simple spreadsheet. Only one column, and only one row, with a Currency amount of -46.16 (yes...a negative number), and imported that into Access 2010, and had the very same problem.

    Don't know if the negative number is part of the problem, but it worked just fine in Access 2007.

    Thanks

  11. #11
    New Lounger
    Join Date
    Dec 2009
    Location
    Broomfield, CO
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Here is the latest info……

    I finally found someone at Microsoft that was willing to talk to me without requiring that I pay $100 for a support call.

    They got me to a member the Access support team, who was willing and interested in checking out this issue. Working with him, I was able to show how the data file was changing decimal precision when imported into Access. We created a new file in Excel, with only one column, and only one row, with a manually entered value. When we imported it into Access, we could get the problem to replicate. He then created the same file on his own system and went to work testing it.

    He called me back today with his results.

    The data file imported WITHOUT PROBLEMS into Access 2003 and Access 2007.

    But when he went to import the file into Access 2010 (and 2013), the same problem arose.

    Based on that, he thinks that this is either a bug, or a change in the algorithm of the import routine that was implemented in Access 2010 (and still there for Access 2013). He placed a report internally to see if it can be fixed or resolved, but stated that it may take a while before anything is done with it.

    In the meantime, he provided several workarounds that he has tested. A couple involved various ways of changing the data type either in Excel or Access, then changing back to Currency.

    But the way that I think will work best for me, is that after the data is imported, to run an “Update” query with the following parameters:

    FormatCurrency([amount],2)

    That will force the values to change to the correct decimal precision.

    Until Microsoft fixes this issue, there is nothing more that can be done, besides using the manual workaround for the problem.

    Thanks to all who responded. It is very much appreciated!

    David

  12. #12
    New Lounger
    Join Date
    Sep 2013
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Post I've had the same issue

    Quote Originally Posted by davidcanfield View Post
    Here is the latest info……

    I finally found someone at Microsoft that was willing to talk to me without requiring that I pay $100 for a support call.

    They got me to a member the Access support team...

    David
    I have had exactly the same issue so was interested to read this thread.

    I will have to get my employer to install SP1 and SP2 for Office 2010 to see if SP2 fixes the issue, but I suspect not considering that Excel 2013 does exactly the same thing.

  13. #13
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    I've not had a chance to test SP2 of OFfice 2010 to see if that resolves the issue, but it's certainly worth trying. Please post back with what you find. Thanks.
    Wendell

  14. #14
    New Lounger
    Join Date
    Oct 2013
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    After 2 hours of searching I finally found your post. I can't believe more people don't have this problem!
    My dilemma is that I work with numbers (fish actually) and not currency, and my OCD won't let me use the currency fix.
    Is there anyway you could provide those other workarounds you mentioned?

    Quote Originally Posted by davidcanfield View Post
    Here is the latest info……

    I finally found someone at Microsoft that was willing to talk to me without requiring that I pay $100 for a support call...

    In the meantime, he provided several workarounds that he has tested. A couple involved various ways of changing the data type either in Excel or Access, then changing back to Currency.

    But the way that I think will work best for me, is that after the data is imported, to run an “Update” query with the following parameters:
    FormatCurrency([amount],2)
    That will force the values to change to the correct decimal precision.

  15. #15
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Is the data in question being imported from Excel, or are you linking to an Excel workbook?

    Also, are your numbers always integers, or do you have numbers with fractional parts?

    There are options to convert to Long Integers, which can be up to about 2 billion, or you can use a Double data type which can be much larger and uses floating point concepts. But we need to know more about your data before we can make a suggestion as to how to proceed.
    Wendell

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •