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

Posted on 2009-12-18
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

    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

    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

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
    Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
    This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

    746 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now