Re: need leading zeros when export to text file

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Darren Green (darren.green_at_reply-to-newsgroup-sqldts.com)
Date: 12/13/04 

Date: Mon, 13 Dec 2004 06:49:57 +0000

In message <254C5E54-6104-4060-9938-1008D5FA3C1D@microsoft.com>, Jess
<Jess@discussions.microsoft.com> writes
>I used to DTS to export data from DB2 to text file with delimited characters
>instead of fixed width format.
>In the text file, I notice that the numeric fields were not padded with
>leading zeros, but I need the leading zeroes.
>
>I tried another method by importing from DB2 to a SQL table and when I run
>query on the table, it doesn't show leading zeroes as well.( In DB2, the data
>are with leading zeroes)
>
>Has anyone got any idea on the above ? thnks.
>

In DB2 I assume you could write a query that fomatted the data first, or
it is definitely possible in T-SQL, e.g.

SELETCT RIGHT('0000000000' + CAST(NumericCol AS varchar(10), 10) AS
NumericCol) FROM Table

Or

you could do this formatting in an ActiveX Script Transform, using some
VBScript, e.g.

DTSDestinatination(1).Value = Right("0000000000" +
CStr(DTSSource(1).Value), 10) 

-- 
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com
PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

Relevant Pages

  • RE: BCP loading datetime date
    ... Is there a way to have bcp convert a DB2 ... I am using SQL Server 2005 with an xml format file. ...
    (microsoft.public.sqlserver.server)
  • Re: sql-server 2k,daten export
    ... Das geeignete Mittel wäre da die Data Transformation ... Services (DTS) vom SQL Server. ... Verbindung zu DB2 und der Rest sollte mittels ...
    (microsoft.public.de.sqlserver)
  • DCount and Date format Question Against IBM DB2 Date
    ... I have an Access Front-End with IBM Mainframe DB2 backend tables. ... to use DCount to confirm records found. ... The data in the DB2 table is stored in the following format: ...
    (microsoft.public.access.queries)
  • Re: I want to collect all records older than 10 days from today from a
    ... SQL Server MVP ... >I transform data from a DB2 table to a SQL tabel. ... The date format in the ... > so I need manually change the date every day. ...
    (microsoft.public.sqlserver.dts)
  • Re: Purging data in Tables
    ... Thanks for the help regarding the Purging of data in db2 tables. ... in this how should the today date format be? ... I don't have a SQL server to test against. ... that shows the proper syntax? ...
    (comp.unix.programmer)