leading zeroes

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

Answer 1

could you please post the output of the same sample data?

---

Nitesh Rai- Please mark the post as answered if it answers your question

Answer 2

The output should be,
001,
002,
031,
036
031,
037.
If the no. has no leading zeroes , it should add a zero.

---

NSG12

Answer 3

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

Answer 4

Thanks.  But, instead of Script Component,  can something be done in Derived Column.

---

NSG12

Answer 5

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

Answer 6

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

Answer 7

 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

Answer 8

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)

---

Answer 9

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

Answer 10

The last 2 answers worked.  Thank you all.

---

NSG12