CSV file format export and leading zeroes.

  • Hi All,

    Thanks for help...

    I have a DTS package in SQL Server2000 that exports a file in CSV format. One of the columns has to have leading zeroes, which I added in the execute sql; however, when exported, the leading zeroes are not there. What is the easiest way to fix this. I have bunch of packages that I need to do the samething for leading zeroes.

    Thanks a lot for help.

     

     

  • someone!

  • Sorry if this is stating the obvious, but what are you using to examine the output? If it's Excel, and the output file extension is .csv, then Excel can strip off your leading zeroes during its automated file conversion when you open it.

    Have a look at the file using Notepad to see if the zeroes are really gone.

    Bill.

  • If you are using Excel to read the data, in the Text Import Wizard, step 3, tell Excel that you want to import the column as Text. Then, Excel will not strip leading zeroes.


    Steve Eckhart

  • Thanks Steve, but I am not doing import... I am doing export.

  • Is the column that is being exported an integer? If so, try converting it to a varchar in the select statement and see if that helps.

    If you could post the DDL for the table and your SELECT statement for exporting the data, we may be better able to help you out.

  • Thanks entusiastic... here is the select statement.

     

    SELECT replicate('0', 9-len(i.other_id)) + cast (i.other_id as varchar(9)),convert(varchar(10),p.pledgeDate, 101) as [EFFECTIVE_DATE],

      case when p.pledgeTypeFK = '3' then convert(varchar(11), p.amount)

      when  p.pledgeTypeFK = '5' then  convert(varchar(11),

      (convert(decimal(5,2), salarypercent))/100) end as [DEDUCTION AMT/PERCENT],

      case when ltrim(rtrim(r.Type_FK)) = 'F' then 'Federal'

      ....................... contd.

  • The other_id is the field that I am having problem with and it needs to be padded with leading zeros. Thanks for help guys.

  • How are you looking at the file after you export it?  Are you opening it in Notepad or in Excel?  If you are opening it it Excel, try opening it in Notepad to verify that the leading zeros are not there.

  • If you prefix the value with a single quote/tic Excel will recognize that you intend the value to be text instead of numeric. I beleive that the older versions of Excel won't even show the leading quote/tic - 2003 puts an information block indicating the the numeric value is being presented as text.

    Good luck,

    Darrell

  • I guess the best way is creating a template, formatting the column with leading zeros as text and then using the template. But then I have to do this for bunch of DTS packages... Wish there was an easier way to export the data with leading zeros in csv format. Thanks guys for help.

  • Hi,

    I have the same issue.

    With a single tick(quote), using DTS, it retains the tick as well.

    eg., '0123 in database turns out '0123 in csv file.

    But, in Excel, a tick(quote) then 0123 and hitting enter generates 0123.

    My requirement is to maintain leading zeroes when we export data using SQL Server 200 DTS to a csv file.

    Any help would be greatly appreciated.

  • Ghanta Bro (3/28/2006)


    I guess the best way is creating a template, formatting the column with leading zeros as text and then using the template. But then I have to do this for bunch of DTS packages... Wish there was an easier way to export the data with leading zeros in csv format. Thanks guys for help.

    You've been asked at least 2 times as to how you are examining that the output file doesn't have the leading zeros... and you've not answered.

    If you want help, ya gotta answer the questions...

    --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)

  • Ghanta Bro (3/27/2006)


    someone!

    And, this is a forum... not online help... don't be so rudely impatient, please.

    --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)

  • Hi Jeff,

    I opened it in Excel and found the data trimmed.

    But, in notepad, it appears fine.

    So, I guess to open a CSV file in Excel and look at the data is not correct.

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

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