Excel import: Value in row #10 empty at data table (missing value)

I try to import an excel file into a .net data table. It works, but no value will be imported if the first nine rows of one column are empty (see attachment).

If row 1 to 9 are empty for column x, no values for example for the not-empty rows 12, 14 or 15 will be imported.

If there is a value in row 9 (column x), the other values also will be imported.

That's strange I think!

The import goes like this:

con = New System.Data.Odbc.OdbcConnection(Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=C:\Folder\file.xls;DefaultDir=c:\temp;)

adap = New Odbc.OdbcDataAdapter("Select Spalte1, Spalte2 from [Test$]", con)

Dim _importedPositions As DataTable
... (define table columns etc.)
adap.Fill(_importedPositions)

empty-row-prob.xls
srexpAsked:
Who is Participating?
 
MakriniConnect With a Mentor Commented:
I don't know if this applies in your situation - but I know it does when importing an excel file to an excel file using a query (sql)

Excel "Jet" driver uses the first 8 values in a column to decide what format of data it is going to use for the rest of the column.  If the majority of the first eight are text - it will ignore numbers...
0
 
srexpAuthor Commented:
That's right, the first "row" is the column header, so it depends on the first eight rows. I think I have to define the data type of the columns. :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.