CSV files with large numbers (Excel2000)

Topic

New Reply

Hi everyone,

I have a CSV file which I process with Excel.
One column contains 12-digit reference numbers.
If I append entries via Excel and save the file (in existing CSV file format), the next time I open the file in Excel the 12-digit numbers become ’rounded’, e.g. 123456123456 becomes 123460000000 etc

Any ideas on how I can maintain the 12-digits????
Even if I add a space to precede the number, the space gets ‘lost’ when Excel opens the csv file again.

zeddy

Reply | Quote

Replies

In XL97 I can’t duplicate this problem. Is it an issue with XL2000??

I do notice that the GENERAL format that is used after “importing” puts the number into scientific notation:
1.2346E+11, no matter how wide the column is set.

Is this what you are speaking about?

To fix this problem, just format to a number with no decimals (“0”) and all 12 numbers will be there.

Steve

Reply | Quote

I can’t reporduce the problem in XL 2000; I’m not losing the less significant digits, but for me when Excel reopens the csv it formats the large numbers in Scientifc Notation (123E+11). Possible workaround is to precede the number with a double quote (“123456123456), if that is tolerable.

Reply | Quote

After re-opening in Excel, press Ctrl-S to save again, close, then reopen again in Excel.
Do you see all the numbers now??

zeddy

Reply | Quote

OK, lets be specific:
If you enter the following three 12-digit numbers in [a1:a3]
123456789012
555555555555
444444444444
The numbers will appear by default in the cells as
1.23457E+11
5.55556E+11
4.44444E+11
..but in the formula bar will show all digits.
(You can format these cells as text if you want -it doesn’t seem to matter.)
Now save the file in CSV format – acknowledge that extra worksheets won’t be saved in CSV format etc.
Now close the file – accept warning message about CSV file format.
Now re-open the file.
Now save the file WITHOUT DOING ANYTHING – i.e. try CTRL-S.
Now close the file.
Now re-open the file.
The three 12-digit numbers become:
123457000000
555556000000
444444000000
Now save the file WITHOUT DOING ANYTHING.
Now close the file.
Now re-open the file.
The three 12-digit numbers become:
123000000000
556000000000
444000000000
From this point, saving and re-opening doesn’t show any further ’rounding’.

So what’s going on and how can I stop it???????

zeddy

Reply | Quote

You haven’t been entirely specific I’m afraid.

After the save-as and reopen, the CELL shows 1.23E+12
But the FORMULA BAR shows 123000000000.

What happens is this:

– Saving as CSV saves the number AS SHOWN in the cell, thus removing all digits that do show in your formula bar but DO NOT show in the cell.
So if your cell would have shown 1.23456789012E+12, you would have retained all digits.

Reply | Quote

Thanks Jan – I think you’ve cracked it.
When I first entered the numbers, with the cells formatted as text, all the numbers appeared in the cells and in the formula bar. When I saved and first reopened, the cells did indeed show
1.23E+11
5.56E+11
4.44E+11
but the formula bar showed all 12 digits on first re-open.
However, when I saved and then re-opened next, the cells displayed as before BUT the formula bar showed the last SIX digits as zero. So saving the second time did not save the number AS SHOWN – it kept 6 digits i.e in the formula bar showed
123457000000
555556000000
444444000000
The next time I saved and then re-opened, the cells showed exactly as before with the E+11 but the formula bar now showed
123000000000
556000000000
444000000000

So the fix is – each time before saving the file again re-format the column containing the 12-digit numbers as number with 0 decimals.
This problem was a pain as all the opening, appending and saving was being done under VBA control with lots of CSV files and with screen updating off.

Many Many thanks and much appreciation!

zeddy

Reply | Quote

Jan Karel has already explained what is going on. Please remember that a .csv file is a plain text file, so if you open the file in Excel, format the cells, save as .csv and close the file, the values are saved as text just as they were displayed. The number format applied in Excel is meta-information not stored in the .csv file. When you re-open the file in Excel, all cells have General number format, not the format they had before you closed the file.

If you apply General format in Excel, 123456789012 will be saved as “1.23457E+11”, on re-opening this will be interpreted as 123457000000 and displayed as 1.23457E11.
If you apply Fixed format with 0 digits, it will be saved as “123456789012”; on re-opening this will be interpreted as you want, but displayed as 1.23457E11 again, because the number format is General; if you re-apply Fixed number format, you will see 123456789012 again.

So you *must* remember to re-apply Fixed format after opening the .csv file. Since .csv files are plain text files, there is not much you can do about it.

Reply | Quote