Post reply

Exporting to CSV with Leading Zeros

  • March 27, 2006 at 9:14 am

    #189926

     

    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 9:20 am

    #628956

    Are you exporting the field as text?

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • March 27, 2006 at 9:44 am

    #628965

    Are you seeing the leading zeros missing when viewing in excel, or are they actualy missing if you open the csv in a text editor. Just guessing but excel maybe formatting.

  • March 28, 2006 at 12:16 am

    #629084

    prefix the column with an apostraphe ', then excel will know it is a text field

     

    so something like this, select field1 + ''' as field1 from table1

  • March 29, 2006 at 10:20 am

    #629577

    Darren,

    You say "prefix", but show an example using "postfix."

    Which is correct?

     

  • June 6, 2006 at 9:16 am

    #642040

    He/She is referring to prefix.

  • June 7, 2006 at 12:43 pm

    #642371

    My two cents...  The easiest way to solve this is to set your destination connection's data source to "Microsoft Excel 97-2000". (if you used the wizard to create your DTS Package this is Connection 2 in the DTS Designer"

    This assumes that you are exporting your data to csv so that it can be viewed via MS Excel (or compatible application), if you are exporting the data to csv so that it can imported into another database then all you will need to do is verify (as noted by andrewkane17 ) that when viewing your file in notepad the leading zeros are visible.

    If the leading zeros are not visible then you need to set the type to varchar for the column containing leading zeros.  (If you used the wizard then this is the "Copy Data from Results to Results Task" in the DTS Designer (it is the arrow pointing to your file icon).

    and now the second cent:  It will work to add a prefix such as Select '''+Column from table.  One thing to keep in mind using this prefix is that excel will display the quote when the file is opened and the quote will exist in the text file.  Depending upon your requirments for usage of the file after export this may or may not be an issue.

    hope this helps

    Jason

    -

Viewing 7 posts - 1 through 6 (of 6 total)

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