locked
leading zeroes RRS feed

  • Question

  • i have to import an excel table to SQL.  The column has values like this.
    1
    001,
    31,
    35,
    031
    035.
    Some of them are left padded with zeroes.  Some are not.  Whether they are left padded or not, i should left pad with Zeroes and send to SQL.  How to do it.
    Thanks.
    NSG12
    Wednesday, August 26, 2009 9:12 PM

Answers

  • I am assuming that the data type of your column is a DT_WSTR or DT_STR, and that you can guarantee that this column will only contain numbers or spaces.  If so, the following expression will work in a Derived Column transform:

    RIGHT("0000" + (DT_WSTR, 5)((DT_I4)[Column]), 5)


    Todd McDermid's Blog
    Wednesday, August 26, 2009 10:50 PM
  • I have another expression which will work:

    REPLICATE("0",15 - LEN(Column1)) + Column1

    15 is the columns width that u want.

    Good to know different ways of doing things.


    Hope this helps !! Please close the threads once answered - Sudeep
    • Proposed as answer by Nitesh Rai Thursday, August 27, 2009 9:28 AM
    • Marked as answer by Todd McDermid Thursday, August 27, 2009 6:12 PM
    Thursday, August 27, 2009 9:26 AM

All replies

  • could you please post the output of the same sample data?
    Nitesh Rai- Please mark the post as answered if it answers your question
    Wednesday, August 26, 2009 9:17 PM
  • The output should be,
    001,
    002,
    031,
    036
    031,
    037.
    If the no. has no leading zeroes , it should add a zero.
    NSG12
    Wednesday, August 26, 2009 9:19 PM
  • Take the flat file source.
    Then a script componet.
    Input file:                 Required Output
    ------------------------------------------
    1                             01
    2                             02
    001                         001
    09                            09

    Let the Source Column Name is Id
    Inside script component create a new output column (NewId).
    I have written following code in script componet

    Inherits

     

    UserComponent
    Dim A As String
    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
    A = "0"
    If Row.ID.StartsWith(A) Then
    Row.NewId = Row.ID.ToString()
    Else
    Row.NewId = A.ToString() + Row.ID.ToString()
    End If
    End Sub



    Nitesh Rai- Please mark the post as answered if it answers your question
    • Proposed as answer by Nitesh Rai Wednesday, August 26, 2009 9:59 PM
    • Unproposed as answer by Todd McDermid Thursday, August 27, 2009 6:13 PM
    Wednesday, August 26, 2009 9:35 PM
  • Thanks.  But, instead of Script Component,  can something be done in Derived Column.
    NSG12
    Wednesday, August 26, 2009 9:47 PM
  • Derived Column name            Expression
    --------------------------------------------------
    NewId                                   FINDSTRING(ID,"0",1) == 1 ? ID : "0" + ID
    Nitesh Rai- Please mark the post as answered if it answers your question
    • Proposed as answer by Nitesh Rai Wednesday, August 26, 2009 9:59 PM
    • Unproposed as answer by Todd McDermid Thursday, August 27, 2009 6:13 PM
    Wednesday, August 26, 2009 9:58 PM
  • thanks for all your efforts.  But,   I donno if i communicated wrong.

    The column has a width of 5.
    If the value is 1, it has to be stored as 00001.
    If the value is 003, it has to stored as 00003.
    If the value is 312, it has to be stored as 00312.
    If the value is 4309, it has to be stored as 04309.
    If the value is 00001, it has to be as it is.
    NSG12
    Wednesday, August 26, 2009 10:13 PM
  •  Try this (I am not sure how efficient is this)
    It will be nice to use script component

    If

     

    Row.ID.Length = 5 Then

    Row.NewId = Row.ID.ToString()

     

    End If

     

    If Row.ID.Length = 4 Then

    Row.NewId =

    "0" + Row.ID.ToString()

     

    End If

     

    If Row.ID.Length = 3 Then

    Row.NewId =

    "00" + Row.ID.ToString()

     

    End If

     

    If Row.ID.Length = 2 Then

    Row.NewId =

    "000" + Row.ID.ToString()

     

    End If

     

    If Row.ID.Length = 1 Then

    Row.NewId =

    "0000" + Row.ID.ToString()

     

    End If


    Nitesh Rai- Please mark the post as answered if it answers your question
    Wednesday, August 26, 2009 10:27 PM
  • I am assuming that the data type of your column is a DT_WSTR or DT_STR, and that you can guarantee that this column will only contain numbers or spaces.  If so, the following expression will work in a Derived Column transform:

    RIGHT("0000" + (DT_WSTR, 5)((DT_I4)[Column]), 5)


    Todd McDermid's Blog
    Wednesday, August 26, 2009 10:50 PM
  • I have another expression which will work:

    REPLICATE("0",15 - LEN(Column1)) + Column1

    15 is the columns width that u want.

    Good to know different ways of doing things.


    Hope this helps !! Please close the threads once answered - Sudeep
    • Proposed as answer by Nitesh Rai Thursday, August 27, 2009 9:28 AM
    • Marked as answer by Todd McDermid Thursday, August 27, 2009 6:12 PM
    Thursday, August 27, 2009 9:26 AM
  • The last 2 answers worked.  Thank you all.
    NSG12
    Thursday, August 27, 2009 4:07 PM