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

Posted on 2009-12-18
Medium Priority
Last Modified: 2013-11-08
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.)

Question by:srexp
LVL 10

Accepted Solution

Makrini earned 500 total points
ID: 26081124
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...

Author Closing Comment

ID: 31667777
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. :)

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Simulator games are perfect for generating sample realistic data streams, especially for learning data analysis. It is even useful for demoing offerings such as Azure stream analytics, PowerBI etc.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question