We help IT Professionals succeed at work.

Truncating leading zeros when importing Excel into data table

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) = ""
      Else
              If .Columns(i).ColumnName = "ZIPCODE" Then
                     If Len(Trim(iRow(i))) < 5 Then
                             iRow(i) = Lpad(iRow(i), "0", 5).ToString
                     End If
              Else
                     iRow(i) = Replace(iRow(i), ",", " ")
              End If
      End If
Next

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?
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2015

Commented:
Isn't this an Excel-specific thing? If the cell type is "numeric", then zeros are stripped. I believe you would need to use a "text"-type cell. I don't recall all the cell types, so there might be a better one than "text".

Author

Commented:
During the import the column data type assigned to the data table columm is determined by the 'average' of the first 8 rows of imported data.  Since they are all numeric (zip code), it will assign a data type of 'double'.
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2015

Commented:
OK. I got that a little backwards, but the concept is still kind of the same:  you will have to set the column types to be of type String in order to keep the leading zeros. I'll have to look up the OleXXX stuff again  to give you an idea of how to accomplish this. I'll post back shortly if no one else has.

Author

Commented:
BTW, I have no control over the inbound Excel file and that is why I need to import 'as is' and normalize once it is in the Item Array.
Without double-checking it looks like, without the method call i.e. (), it is assuming a default on the ToString function. If this is the case then perhaps set it as:
iRow(i) = Lpad(iRow(i), "0", 5).ToString("00000")

I'm on my Mac at the moment so I can't check but will come back to you later if this doesn't resolve it for you.
Regards

Author

Commented:
The string format suggestion caused a runtime error.  (ToString("00000")

Added a couple of traps to see what was going on:

   If Len(Trim(iRow(i))) < 5 Then
         hold = Lpad(iRow(i), "0", 5).ToString         <hold = '02301' (correct) >
         iRow(i) = Lpad(iRow(i), "0", 5).ToString()
         iRow(i) = hold                                       <setting the value to 'hold' >
         hold = iRow(i)                                       <hold = '2301' >
   End If
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Your datatable column probably has numeric type. You can not change it if there are rows so one option is to add another column of string type and store your padded zip codes in that column.

Author

Commented:
CC

Is there no way to force 'string' type for all columns during import by ADO and have it ignore the contents?
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
You can try to use cstr in you select.

Select cstr(columnname) from ...

Author

Commented:
CC

cstr doesn't seem to work in a Select statement.
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:

Author

Commented:
CC

There's is really nothing to format.  If I format 'hold', for example to "00000", iRow(i) will end up with '0' since it is data type 'double'.  I need to force the zipcode field to text or string during the import.  That is why the Cast or Cstr approach seemed intriguing, but I can't get it to work in the Select statement.
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014
Commented:
What happens when you use cast/cstr? You may have to use cstr and format combined.

Author

Commented:
I don't believe you can use a Format function in a Select statement.  If that is not true, what should the Select look.  'Select Format(CStr(Zipcode), "00000") as Zipcode' ???
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Yes try that.

Author

Commented:
Seems to work fine.   Thanks.

I already awarded these points before, but I just got a notification that it was an inactive question.  Not sure why it didn't post properly.