+ Reply to Thread
Results 1 to 19 of 19

Leading Zeros lost in csv format

  1. #1
    Registered User
    Join Date
    02-12-2008
    Posts
    45

    Leading Zeros lost in csv format

    I have an excel spreadsheet with numbers and the format has to show zeroes at the beginning (e.g. 0011111) It is always going to be a seven digit number. I can format the cells to show the numbers with the leading zeros, but as soon as I save the file as a csv, the leading zeros are lost.

    Does anyone know how to the zeros can be retained when the file is saved in csv format?

    Any help would be appreciated.
    Last edited by Scotsman89; 06-10-2009 at 06:26 AM.

  2. #2
    Registered User
    Join Date
    02-12-2008
    Posts
    45

    Re: Leading Zeros lost in csv format

    That is a shame, so does that mean it cannot be done?

  3. #3
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Leading Zeros lost in csv format

    try saving it as text the formating will be preserved.
    then reopen with excel making sure you use delimited and each field is set to text
    resave as csv
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  4. #4
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Leading Zeros lost in csv format

    when you open it the wizard lets you choose delimited
    click next
    make sure only tab is selected
    click next
    hold shift select all fields and check the text box under "column data format"
    click finish

  5. #5
    Registered User
    Join Date
    02-12-2008
    Posts
    45

    Re: Leading Zeros lost in csv format

    Sorry if it is a stupid question but what do you mean

    "when you open it the wizard lets you choose delimited"

    How do I get this wizard?

  6. #6
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Leading Zeros lost in csv format

    once saved as something .text close it
    open excel then use file /open navigate to correct folder change the file types drop down to text files
    locate something.txt
    click open
    the text import wizard will then open.
    Last edited by martindwilson; 06-09-2009 at 09:27 AM.

  7. #7
    Registered User
    Join Date
    02-12-2008
    Posts
    45

    Re: Leading Zeros lost in csv format

    Thanks a lot.
    It has worked, however, it now inports all the data into collum A (whereas before it was split equally in the relevant collums).
    Any way we can keep the zeros without affecting the formatting?

  8. #8
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Leading Zeros lost in csv format

    cant tell without an example!

  9. #9
    Registered User
    Join Date
    02-12-2008
    Posts
    45

    Re: Leading Zeros lost in csv format

    I have attached an example.

    So I want to save this as a csv file, however, when i do this with your instructions all the data goes into collum A.
    Attached Files Attached Files

  10. #10
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Leading Zeros lost in csv format

    see attached
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    02-12-2008
    Posts
    45

    Re: Leading Zeros lost in csv format

    Fantastic, it works! Thanks a lot for your help!!

  12. #12
    Registered User
    Join Date
    11-15-2011
    Location
    usa
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Leading Zeros lost in csv format

    for me it's not working, you're right that "txt" preserves the Leading Zeros but not "CSV" I'm looking how to preserve the Leading Zeros in "CSV" format

  13. #13
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Leading Zeros lost in csv format

    open it with a text editor like notepad not excel you should see the 0's

  14. #14
    Registered User
    Join Date
    11-15-2011
    Location
    usa
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Leading Zeros lost in csv format

    Yes you're right, Thank you

  15. #15
    Registered User
    Join Date
    04-05-2012
    Location
    Singapore
    MS-Off Ver
    Excel XP
    Posts
    1

    Re: Leading Zeros lost in csv format

    Yes, the zero is there but the excel still not showing zero for csv file.
    Car Advertising Get Paid To Drive | www.movdia.my

  16. #16
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Leading Zeros lost in csv format

    this is an old thread

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1