Post reply

CSV file format export and leading zeroes.

  • March 27, 2006 at 8:08 am

    #67404

    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.

     

     

  • March 27, 2006 at 8:25 am

    #628948

    someone!

  • March 28, 2006 at 12:43 am

    #629086

    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.

  • March 28, 2006 at 7:08 am

    #629143

    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

  • March 28, 2006 at 7:36 am

    #629159

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

  • March 28, 2006 at 7:50 am

    #629170

    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.

  • March 28, 2006 at 8:37 am

    #629190

    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.

  • March 28, 2006 at 9:13 am

    #629214

    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.

  • March 28, 2006 at 12:25 pm

    #629272

    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.

  • March 28, 2006 at 1:24 pm

    #629287

    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

  • March 28, 2006 at 1:41 pm

    #629290

    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.

  • January 13, 2008 at 6:43 am

    #767482

    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.

  • January 13, 2008 at 7:56 am

    #767486

    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)

  • January 13, 2008 at 7:57 am

    #767487

    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)

  • January 13, 2008 at 9:46 pm

    #767562

    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