Results 1 to 5 of 5
  1. #1
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts

    Leading zeroes in .CSV files (XP)

    Is there a way to preserve leading zeroes when saving an Excel worksheet as a .CSV file?

    My need is to save the leading zeroes in New England and New Jersey ZIP codes. I've tried formatting their cells in Excel as General, as Text, as ZIP codes, and as General with a leading apostrophe. In every case, when I save the worksheet as a .CSV file, the leading zeroes are dropped.

    Surely there's a way to do this.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Leading zeroes in .CSV files (XP)

    Are you sure about this? The way it works for me is as follows:
    - The .csv file contains the values as formatted in Excel, including leading zeros.
    - If I open the .csv file with a text editor such as Notepad or Word, the leading zeros are present.
    - If I open the .csv file in Excel, the values are interpreted as numbers, with General number format, so the leading zeros are lost. This is to be expected, since the.csv file doesn't store formatting information, it is just a text file.

    If you don't want Excel to interpret the values as numbers, rename the file to .txt instead of .csv. Opening it will start the Text Import Wizard. In step 3 of the wizard, you can specify that the columns are to be interpreted as Text instrad of General. Leading zeros will then be preserved.

  3. #3
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts

    Re: Leading zeroes in .CSV files (XP)

    It seems that you are right. I saved the file as .CSV, then checked it with Notepad. The leading zeroes are preserved.

    In the past, I had saved it as .CSV, then checked it with Excel. The checking step showed that the zeroes had been dropped, since their cells were displayed in the General format. They were still there in the .CSV file, though.

    Somehow they got dropped when I imported the .CSV into my Peachtree accounting system, though. This was probably due to operator error, but I'll watch it carefully the next time.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  4. #4
    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: Leading zeroes in .CSV files (XP)

    One technique to still use a CSV file and retain the leading zeroes is to "force" excel to interpret the values as text when it is importing the csv file. This can be done with custom formatting that includes a "sticky-space" [=Char(160)]. If you format the zipcodes by:
    Format- cells - custom:
    <alt>0160 00000 [Hold alt key, enter 0160 on the numeric keypad, then enter 5 zeroes]

    or use
    00000<alt>0160 [enter 00000 then hold alt key, enter 0160 on the numeric keypad]

    The first adds a sticky-space before the number, the second after the number. Excel will not remove this char from the value (a normal space would be removed) and interpret it as text since it has text and numbers in the cell. The only problem is that the cell does have an extra character in it.

    Steve

  5. #5
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts

    Re: Leading zeroes in .CSV files (XP)

    Thanks. I'll try it.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

Posting Permissions

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