Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Houston, Texas, USA
    Posts
    303
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Convert cvs to excel (Excel XP)

    We get a flat file from our client in cvs format. It contains numbers that are from 10 to 17 characters in length. When we try to import the file into Excel or Access the number is imported as 123457E+16. How can we make sure that if we change the format the number will not be rounded or changed in any manner.

  2. #2
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Louisville, Kentucky, USA
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Convert cvs to excel (Excel XP)

    I don't know about Access, but in Excel the number shouldn't be changed at all. If you make the Excel column wider, I suspect that you will see the correct number. You could also click on a cell and see what number shows up in the formula bar.

    I've only done a little bit with Access, but I suspect it wouldn't change the number either.

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Convert cvs to excel (Excel XP)

    Excel has a limit of 15 digits for any numeric value (and I believe that Access does also). To import that data and not lose anything, you will have to import that field as text. To do this, do the following:

    1- Rename the file from .CSV to .TXT.

    2- Open Excel to the worksheet where you want to import the data.

    3- Select "Get Extermal Data" from the Data menu and then click on "Import text file" on the flyout menu.

    4- Select the file and click on the Import button.

    5- In the dialog box click on Delimited and then on the Next button.

    6- Click on Comma and any other appropriate delimiters then click on Next.

    7- Click on the column header above the column with the 17 digit numbers and then click on Text. If any other columns need special data type, click on those column headers and then on the appropriate data type.

    8- Select where you want the data placed and click on OK.

    You should now have the data imported with that column as text.
    Legare Coleman

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Convert cvs to excel (Excel XP)

    Not true in Excel. Excel has a limit of 15 digits for numeric data and will drop the low order digits over 15.
    Legare Coleman

  5. #5
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Louisville, Kentucky, USA
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Convert cvs to excel (Excel XP)

    <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30> Thanks, Legare. I stand corrected. I guess I've never had to deal with numbers over 999 trillion. Hopefully the original poster is dealing with part numbers or something and not the number of parts he needs to produce.

  6. #6
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Louisville, Kentucky, USA
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Convert cvs to excel (Excel XP)

    I thought I'd play around with Excel 97's limits for internal calcs and tried the following:

    cell A1: =VALUE("1234567890123456")
    cell B1: various values
    cell C1: =A1+B1

    When B1=5, C1=1,234,567,890,123,450
    When B1=6, C1=1,234,567,890,123,460
    When B1=5.12, C1=1,234,567,890,123,450
    When B1=5.13, C1=1,234,567,890,123,460

    Can you explain this?

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Convert cvs to excel (Excel XP)

    Excel has only 15 digits MAX of precision and it has "rounding errors" in the last place.

    =VALUE("1234567890123456") = 1234567890123450 (it TRUNCATES it does NOT round)

    1234567890123450 + 5 = 1234567890123455 gets rounded to 1234567890123450 (ZERO is just a placeholder NOT a significant figure)
    1234567890123450 + 6 = 1234567890123456 gets rounded to 1234567890123460
    1234567890123450 + 5.13 = 123456789012345513 gets rounded to 1234567890123460

    The only 1 "wrong" is:
    1234567890123450 + 5.12 = 123456789012345512 gets rounded to 1234567890123450 instead of the "correct" 1234567890123460, but off 1 digit in 10^15 is not a too bad a rounding error. Remember it also uses base 2 not base 10 as we are doing.

    Steve

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Convert cvs to excel (Excel XP)

    Could be his income!
    Legare Coleman

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Convert cvs to excel (Excel XP)

    Steve explained it pretty well. Just to add a little to his discussion. This is caused by the fact that the computer works in binary, not decimal. Therefore, the number of significant digits is not exactly 15 decimal digits, it is a little more than 15. So, the differences you are seeing are caused by where the hardware loses binary digits. when converted back to decimal, you get the rounding errors that you see.
    Legare Coleman

  10. #10
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Convert cvs to excel (Excel XP)

    Now that you know why this occurs, you might also like a relatively simple work-around: Simply place a tick mark in front of all of the data with more than 15 digits - or in front of all data, if that suits. What this does is to convert the number to a text string, and Excel can handle text strings over 1000 charatcers.

    Of course, if you need to do math with the imported values, you'll need to extract the numbers from the text strings. With a bit of effort (or maybe a lot if you're doing anything complex), though, you'll be able to do this woithout losing any precision.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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