CSV file format export and leading zeroes.

  • If you want to see your leading zeros in Excel then you need to either:

    1. Qualify the field with quotes (e.g. "0000123") when exporting to the csv

    or as said before...

    2. Put the single quote/apostrophe/tick before the 0000123 (e.g '0000123)

    ...so that Excel will recognise the data as text and not a number.



    Ade

    A Freudian Slip is when you say one thing and mean your mother.
    For detail-enriched answers, ask detail-enriched questions...[/url]

  • Adrian is correct,

    Anything that looks like a numeric entry, will be treated like one by Excel. If you type 000123 into Excel, it will be changed to 123 by Excel unless you have some sort of textual prefix like a single (left justify) or double (right justify) quote or circumflex (center). Double quote may give fits in other places.

    So, the data must mimic what you type... if you want to preserve leading zeroes, you just include something that will tell Excel to treat it like text.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Rather than using the LEN function to determine the number of times

    to replicate your leading zeros, it might be simpler and easier to read

    like this:

    SELECT RIGHT('000000000' + CONVERT(VARCHAR, ), 9)

    It kind of reads easier.

    Todd Fifield

  • I would like to retain a leading zero on decimal output.

    IE I would like 0.4567 vs .4567; The only way I can think of doing this is to convert to varchar, but then it's no longer a number its text, and my text is surrounded by quotes. This is no biggie for a Phone Number, but for an exchange rate or and amount it is.

    I am opening the file with a text editor notepad or textpad.

    Any ideas?

  • Ray White (3/7/2008)


    I would like to retain a leading zero on decimal output.

    IE I would like 0.4567 vs .4567; The only way I can think of doing this is to convert to varchar, but then it's no longer a number its text, and my text is surrounded by quotes. This is no biggie for a Phone Number, but for an exchange rate or and amount it is.

    I am opening the file with a text editor notepad or textpad.

    Any ideas?

    SELECT STR(.123,10,3)

    Use BCP to export and you won't get the quotes.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I was asked to do this for ICD-9 diagnosis fields that may have leading or trailing zeros to be retained in CSV export from SSRS 2008 R2. Example is 090.10 for a diagnosis. I found that adding a carriage return to the expression in Visual Studio will preserve the zeros! Looks good in regular Excel/PDF output and print as well.

    Fields!diag_field.Value + chr(13)

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply