We help IT Professionals succeed at work.

Incorrect data type when importing Excel into data table

I am importing an Excel file into a data table via OleDBDataAdapter.  I then process the new data table for data validation.  The problem is that during the oledb import  the data type for a column is automatically set to 'double' if the column data happens to contain numeric data.  In my case, all rows in that column are NULL except for two - one contains text and the other is all numeric.  For some reason, the data for that column is determined to be 'double'.  All columns are assumed to be 'text'.  This poses a problem when I do my validation because I am comparing the value to an expected text value which throws an error saying that the column is expected to be of type 'double'.

Following (or during) the oledb import into the data table, is there some way I can force all columns to be 'text' before I begin my validation process?
Comment
Watch Question

NorieAnalyst Assistant
CERTIFIED EXPERT

Commented:
Couldn't you just force/convert anything that isn't text during the validation process?

Commented:
This poses a problem when I do my validation because I am comparing the value to an expected text value which throws an error saying that the column is expected to be of type 'double'.

Why not just cast the value from the table to what you need it to be before the validation?

Author

Commented:
Once the data table is loaded via oledb I go through the rows with:
For i As Integer = 0 To UBound(iRow.ItemArray)
Next
How could I convert/force the data type to text as I walk the ItemArray?
NorieAnalyst Assistant
CERTIFIED EXPERT

Commented:
How are you going through the array?

What you could do is use a variable for the items in the array as you go through it, then check their data type and if needed cast the value.

How are you getting the data in the first place?

Another option could be to do the conversion in the SQL statement, assuming that's what's being used.
Commented:
So I guess you are doing iRow.Items(i) to get your value?
if so, then check that for null and then convert...

if not iRow.Items(i) is dbnull.value then
'do your conversion...double to string, whatever

End if

Author

Commented:
What is the command to convert it to string?

Commented:
.ToString

or DOuble.Parse()
Integer.Parse()

There are options, I would recommend reading about each and seeing where they will break and what is expected.

Author

Commented:
Sorry for the obtuce question about how to convert, but I figured that you were already engaged in the query I would get lazy.
Thanks.
NorieAnalyst Assistant
CERTIFIED EXPERT

Commented:
If you know which column it is why not just skip it?
NorieAnalyst Assistant
CERTIFIED EXPERT

Commented:
Just remembered, you can use IMEX=1 to make sure everything is text.

Commented:
Sorry for the obtuce question about how to convert, but I figured that you were already engaged in the query I would get lazy.
Thanks.

Just be sure to read about your conversion functions so you know what can cause them to fail...

Explore More ContentExplore courses, solutions, and other research materials related to this topic.