Results 1 to 8 of 8
  1. #1
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts

    CSV files with large numbers (Excel2000)

    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

  2. #2
    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: 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

  3. #3
    Uranium Lounger
    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

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts

    Re: CSV files with large numbers (Excel2000)

    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

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts

    Re: CSV files with large numbers (Excel2000)

    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

  6. #6
    Platinum Lounger
    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 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.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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 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.

  8. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 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 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

Posting Permissions

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