Post reply

display leading zeros in CSV

  • February 12, 2007 at 1:25 pm

    #69591

    Hi,

    On DTS SQL 2000 I have exported a file to CSV format with one of the fields that has leading in varchar datatype. however, I opened the CSV file I did not see leading zeros that I wanted it to be. On the DTS process Task preview it did show the leading zeros. Please advise  how I can  make my exported CSV file to show leading zeros? Thanks.

  • February 15, 2007 at 8:00 am

    This was removed by the editor as SPAM

  • February 15, 2007 at 5:00 pm

    #690105

    It's on going issue for everybody who transforms number string to csv format. But if you do not mind to have double quotes at the beginning and end of the number string, then I can suggest the following.

    In your Transform Data Task Property in DTS, choose the option SQL query in the Source tab. Then add '"' before and after the column of the number string. See the example below.

    From: select  [strID],[data] from [TestDB].[dbo].[test]

    To: select  '"' + [strID] + '"' as [strID],[data] from [TestDB].[dbo].[test]

    The result in the csv file would look like below:

    strID data
    "0000123" data1
    "0000124" data2
    "0000125" data3
    "0000126" data4
    "0000127" data5
    "0000128" data6
    "0000129" data7

    But interestingly, if you open the file in Notepad it would show like below:

    "strID","data"

    """0000123""","data1"

    """0000124""","data2"

    """0000125""","data3"

    """0000126""","data4"

    """0000127""","data5"

    """0000128""","data6"

    """0000129""","data7"

    This means that actual output csv file contains all the leading zeros but Excel treated this column(strID) as number. Therefore this is a potential bug in Excel - perhaps Format Cells function???

  • February 15, 2007 at 11:38 pm

    #690126

    Hi Terry,

    Thanks for your help. I don't think the "" is going to work for me. This is because my customer would like to see the numbers without the "". Any ideas if you can do without the """ and still showing the zeros. By the way the interger data has a conversion to varchar.  Thanks.

  • February 16, 2007 at 9:23 am

    #690264

    I just did one that required leading zeros on one of the numeric fields.

    If the length of the CSV field is 15.

    In the select statement

    SELECT RIGHT(('000000000000000' + CAST(Field AS VARCHAR(15))), 15)

    Hope it helps.

     

  • February 16, 2007 at 9:28 am

    #690265

    Becacause my value varies somestime is 1 or 23, or 564 and it  need varies leading zeros to have a  fixed length of a varchar(5). How does this work. Please advise. Thanks.

     

     

  • February 16, 2007 at 12:00 pm

    #690298

    Can you give me an example?

    You mean the value in the table can be 1, 23 or 456, but the fixed length of the CSV field is 5 ??

    In my example, if the field is varchar(5) and the length in the CSV is 5

    SELECT RIGHT(('00000' + CAST(Field AS VARCHAR(5))), 5)

    RIGHT is

    Returns the part of a character string starting a specified number of integer_expression characters from the right.

     

    SO if the Field = 1

    SELECT RIGHT(('00000' + CAST(Field AS VARCHAR(5))), 5) will have the value '00001'

    if the Field = 456

    Then the result is '00456'

  • February 16, 2007 at 12:35 pm

    #690307

    I apologized I did not make myself clear. MY values of the field can have 1 digit or 2 digit or 3 digits and up to 5 digits. the leading zeros will fill in the empty spaces if is the digits are leass than 5. For example,

     2 will become 00002

    25 will become 00025

    378 will become 00378

    8899 will become 08899

    56678 will remain 56678

    Any ideas ? Thanks.

     

  • February 16, 2007 at 2:46 pm

    #690336

    Then my query

    SELECT RIGHT(('00000' + CAST(Field AS VARCHAR(5))), 5) will give you waht you want, just try it.

    Let me know.

  • February 16, 2007 at 2:56 pm

    #690340

    Thanks, I will try it and let you know.

  • March 27, 2007 at 11:47 am

    #697236

    I have this same issue in exporting zip codes to a .csv file. I need the first 5 from the left, including the 0. This code works beautifully in the query analyzer, but when it actually does the export to .csv, the leading zeros still don't show up.

  • March 27, 2007 at 12:02 pm

    #697244

    unless you open from wordpad you will not see the zeros. This is a automatic default from Microsoft. You  might want to check if there is an option  in the csv file to remove this default option.

  • March 27, 2007 at 2:53 pm

    #697274

    It is problem of excel.  When it sees a numeric fields, it will automatically delete the leading zeros.  The only way is before loading into the sheet, select the column that will hold the field, right click and select format cell, change it to 'text'.  Then when you load the data into the excel spreadsheet, it will retain the leading zeros.

     

     

  • March 27, 2007 at 3:53 pm

    #697284

    That's what the "" is supposed to do when importing into Excel... it's one of the default settings.  Tell your client the "" is absolutely necessary.

    --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)
    Intro to Tally Tables and Functions

  • March 28, 2007 at 10:26 am

    #697477

    If the problem is with Excel, I will just not worry if I preview the file in Excel. My process is DTS to a .csv and then automatically scheduling the FTP of the .csv to the website. So, even though I don't see the zeros, in my case it seems to work now.

Viewing 15 posts - 1 through 14 (of 14 total)

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