Convert cvs to excel (Excel XP)

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

Could be his income!

Reply | Quote

Could be his income!

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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
[Fmr MS MVP - Word]

Reply | Quote

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
[Fmr MS MVP - Word]

Reply | Quote