• CSV files with large numbers (Excel2000)

    Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » CSV files with large numbers (Excel2000)

    Author
    Topic
    #382136

    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

    Viewing 1 reply thread
    Author
    Replies
    • #646805

      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

    • #646807

      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.

      • #646938

        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

      • #646940

        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

        • #646944

          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.

          • #646949

            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

        • #646947

          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.

    Viewing 1 reply thread
    Reply To: CSV files with large numbers (Excel2000)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: