I am importing an Excel file into a data table via OleDbDataAdapter. I then normalize all of the data to 'text' with the following.
For i As Integer = 0 To UBound(iRow.ItemArray)
iRow(i) = iRow(i).ToString()
If IsDBNull(iRow(i)) Then
iRow(i) = ""
If .Columns(i).ColumnName = "ZIPCODE" Then
If Len(Trim(iRow(i))) < 5 Then
iRow(i) = Lpad(iRow(i), "0", 5).ToString
iRow(i) = Replace(iRow(i), ",", " ")
If the field is ZIPCODE I want to re-insert the leading zeros that the Excel import removed. The LPAD function works fine, but when the LPAD results are loaded into the ItemArray value ( iRow(i) = Lpad(iRow(i), "0", 5).ToString ) the leading zeros are stripped again.
What do I need to do to retain the leading zeros?