Results 1 to 10 of 10
Thread: Convert cvs to excel (Excel XP)

20030924, 20:32 #1
 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.

20030924, 20:54 #2
 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.

20030924, 20:54 #3
 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

20030924, 20:56 #4
 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

20030924, 21:39 #5
 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.

20030924, 21:52 #6
 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?

20030924, 22:18 #7
 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

20030925, 00:41 #8
 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

20030925, 00:47 #9
 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

20030925, 07:37 #10
 Join Date
 May 2002
 Location
 Canberra, Australian Capital Territory, Australia
 Posts
 5,086
 Thanks
 2
 Thanked 426 Times in 352 Posts
Re: Convert cvs to excel (Excel XP)
Now that you know why this occurs, you might also like a relatively simple workaround: 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.
CheersCheers,
Paul Edstein
[MS MVP  Word]