We help IT Professionals succeed at work.
Get Started

Truncating leading zeros when importing Excel into data table

Last Modified: 2012-05-12
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
                     End If
                     iRow(i) = Replace(iRow(i), ",", " ")
              End If
      End If

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?
Watch Question
Most Valuable Expert 2012
Top Expert 2014
This problem has been solved!
Unlock 1 Answer and 16 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE