Results 1 to 8 of 8

20030121, 15:11 #1
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 3,171
 Thanks
 151
 Thanked 587 Times in 559 Posts
CSV files with large numbers (Excel2000)
Hi everyone,
I have a CSV file which I process with Excel.
One column contains 12digit 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 12digit numbers become 'rounded', e.g. 123456123456 becomes 123460000000 etc
Any ideas on how I can maintain the 12digits????
Even if I add a space to precede the number, the space gets 'lost' when Excel opens the csv file again.
zeddy

20030121, 16:07 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: CSV files with large numbers (Excel2000)
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

20030121, 16:16 #3
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
Re: CSV files with large numbers (Excel2000)
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.
John ... I float in liquid gardens
UTC 7ąDS

20030122, 07:37 #4
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 3,171
 Thanks
 151
 Thanked 587 Times in 559 Posts
Re: CSV files with large numbers (Excel2000)
After reopening in Excel, press CtrlS to save again, close, then reopen again in Excel.
Do you see all the numbers now??
zeddy

20030122, 08:21 #5
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 3,171
 Thanks
 151
 Thanked 587 Times in 559 Posts
Re: CSV files with large numbers (Excel2000)
OK, lets be specific:
If you enter the following three 12digit 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 reopen the file.
Now save the file WITHOUT DOING ANYTHING  i.e. try CTRLS.
Now close the file.
Now reopen the file.
The three 12digit numbers become:
123457000000
555556000000
444444000000
Now save the file WITHOUT DOING ANYTHING.
Now close the file.
Now reopen the file.
The three 12digit numbers become:
123000000000
556000000000
444000000000
From this point, saving and reopening doesn't show any further 'rounding'.
So what's going on and how can I stop it???????
zeddy

20030122, 08:39 #6
 Join Date
 Feb 2001
 Location
 Weert, Limburg, Netherlands
 Posts
 4,812
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: CSV files with large numbers (Excel2000)
You haven't been entirely specific I'm afraid.
After the saveas 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.Jan Karel Pieterse
Microsoft Excel MVP, WMVP
www.jkpads.com
Professional Office Developers Association

20030122, 09:05 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: CSV files with large numbers (Excel2000)
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 metainformation not stored in the .csv file. When you reopen 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 reopening 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 reopening this will be interpreted as you want, but displayed as 1.23457E11 again, because the number format is General; if you reapply Fixed number format, you will see 123456789012 again.
So you *must* remember to reapply Fixed format after opening the .csv file. Since .csv files are plain text files, there is not much you can do about it.

20030122, 09:27 #8
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 3,171
 Thanks
 151
 Thanked 587 Times in 559 Posts
Re: CSV files with large numbers (Excel2000)
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 reopen.
However, when I saved and then reopened 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 reopened, 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 reformat the column containing the 12digit 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