从excel导入到datatable，跳过最后的列值 [英] Import from excel to datatable skipping last column values

问题描述

我正在尝试将数据从excel文件导入到datatable。但问题是最后一列值被跳过。其他列的值是完美的。我的excel文件包含：

在datatable中导入数据如下所示：

我的代码是：

  Dim connExcel As New OleDbConnection（constr）
 Dim cmdExcel As New OleDbCommand（）
 Dim oda As New OleDbDataAdapter（）
 Dim dt As New DataTable（）
 cmdExcel.Connection = connExcel 
 
'从第一张表中读取数据
 connExcel.Open（）
 cmdExcel.CommandText =SELECT * From [Sheet1 $]
 oda.SelectCommand = cmdExcel 
 oda.Fill（dt）
 connExcel.Close（）
  

连接字符串如下：

 < add name =ExcelCon12connectionString =Provider = Microsoft.JET.OLEDB.12.0; Data Source = {0};扩展属性= Excel 8.0 /> 
< add name =ExcelCon4connectionString =Provider = Microsoft.JET.OLEDB.4.0; Data Source = {0};扩展属性= Excel 8.0/> 
  

我做错了什么？

解决方案

我将认可这是Excel / OleDb中的一个错误。 @ubaidashrafmasoody是正确的，我今天再现了相同的行为。另一个提供答案，可以通过设置

扩展属性=Excel 8.0; IMEX = 1

似乎不正确，因为我已经在我的连接字符串中设置了这个行为。

错误行为的细节是这样的（尽管可能的是编辑：如果最后一列在前几个（大概是8个）行中具有（多数）空单元格，则该列不会出现在任何SQL语句的范围内（这在某些编辑之后任意修改，正如@ubaidashrafmasoody所描述的那样）。例如，如果我有五列Column1，Column2，Column3，Column4和Column5，其值（头列中的Column5除外）为空，然后：

SELECT *从[Sheet1 $]

只能返回4列。

如果我只列出四列，例如：

SELECT [Column1]，[Column2]，[Column3]，[Column4 ]从[Sheet1 $]

然后它会正确返回。但是如果我命名第五列：

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

然后出现错误消息，指定[Column5]的参数值未指定。或者，没有括号，没有这样的字段。

甚至在SQL语句中明确指定范围：

SELECT [Column1]，[Column2]，[Column3]，[Column4]，[Column5] From [Sheet1 $ A1：E55]

或

SELECT * From [Sheet1 $ A1：E55] p>

无法检索第五列。

然而，一个简单的修复是添加一个新的空白列名为eg Column6之后。突然之间，Column5变得可见，尽管它的NULL。

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?

解决方案

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.

这篇关于从excel导入到datatable，跳过最后的列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！