Import from excel to datatable skipping last colum

2019-07-19 10:50发布

问题:

I am trying to import data from excel file to datatable. But problem is that last column values are skipped. Values of rest of columns are perfect. My excel file contains this:

And after import data in datatable is as below :

My code is as :

  Dim connExcel As New OleDbConnection(constr)
    Dim cmdExcel As New OleDbCommand()
    Dim oda As New OleDbDataAdapter()
    Dim dt As New DataTable()
    cmdExcel.Connection = connExcel

    'Read Data from First Sheet
    connExcel.Open()
    cmdExcel.CommandText = "SELECT * From [Sheet1$]"
    oda.SelectCommand = cmdExcel
    oda.Fill(dt)
    connExcel.Close()

Connections Strings are as :

<add name="ExcelCon12" connectionString="Provider=Microsoft.JET.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0" />
<add name="ExcelCon4" connectionString="Provider=Microsoft.JET.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0"/>

What am I doing wrong?

回答1:

I would endorse the idea that this is indeed a bug in Excel / OleDb. @ubaidashrafmasoody is correct, and I've reproduced the same behavior today. The other offered answer, that it can be fixed by setting

Extended Properties="Excel 8.0;IMEX=1"

does not seem correct, because I encountered this behavior with this already set in my connection string.

The particulars of the buggy behavior is this (though it is possible that editing: If the very LAST column has (majority) empty cells in the first few (presumably 8) rows, then the column doesn't appear in scope to any SQL statement. (This arbitrarily fixes itself after certain edits, exactly as @ubaidashrafmasoody described.) For instance, if I had five columns, Column1, Column2, Column3, Column4, and Column5, with the values (other than Column5 in the header row) blank, then:

SELECT * From [Sheet1$]

would only return 4 columns.

If I were to name only four of the columns, e.g.:

SELECT [Column1], [Column2], [Column3], [Column4] From [Sheet1$]

then it would return correctly. But if I named the fifth column:

SELECT [Column1], [Column2], [Column3], [Column4], [Column5] From [Sheet1$]

then it errors out with a message that the value of the parameter for [Column5] is not specified. Or, without the brackets, that there is no such field.

Even explicitly specifying the range in the SQL statement:

SELECT [Column1], [Column2], [Column3], [Column4], [Column5] From [Sheet1$A1:E55]

or

SELECT * From [Sheet1$A1:E55]

does not work to retrieve that fifth column.

However, an easy 'fix' is to add a new blank column named e.g. Column6 after this . Suddenly, Column5 becomes visible, in spite of its NULLs.



回答2:

From the look's of the code you have provided I'm not seeing an issue. I believe the issue lay's within the column itself; for example: mixed values (bg123 | 1234). Can this column be mixed; integers, strings etc? ADO.Net chooses the DataType based on the column particularly from the majority of the values. Each cell is then cast to that type that ADO.Net choose's it to be. If you have more integer's than it would be cast as numeric on the other hand if not it will be cast as string...

One solution is to use ExtendedProperties within your connection string; indicate's your importing the data.

Try the approach below...

 Extended Properties=""Excel8.0;IMEX=1""" 'This is to tell it to not care about the types...(mixed); be sure to include the `"` around the `ExtendedProperties` as well.

Your connection string now...

 "Provider=Microsoft.JET.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;IMEX=1"""