ADO - 如何从两个或多个列具有相同名称的xls文件中选择列？

I have an excel file like this:

我有一个像这样的excel文件：

|   |    A   |    B    |    C    |   D    |
| 1 | Name 1 |  Name 2 |  Name 3 | Name 2 |
| 2 |  Data  |  Data   |  Data   | Data   |
| 3 |  Data  |  Data   |  Data   | Data   |

As you can see, headers of two columns have the same name - Name 2.

如您所见，两列的标题具有相同的名称 - 名称2。

My question is, is it possible to tell the ADO engine from which column to select data?

我的问题是，是否有可能告诉ADO引擎从哪个列中选择数据？

Currently my select looks like this:

目前我的选择如下：

SELECT [Name 1], [Name 2] FROM [REPORT7_RAW$] WHERE [Name 1] IS NOT NULL

and ADO picks up the data from column which is listed under column B in excel. In other words it takes the first column which have the given name. Unfortunately I have two columns with the same name and I would like to pull out the data from column D. Is it possible?

和ADO从excel中列B列下的列中获取数据。换句话说，它采用具有给定名称的第一列。不幸的是，我有两个具有相同名称的列，我想从列D中提取数据。是否可能？

I could not find any way to select column by its index rather the name.

我找不到任何方法来通过索引而不是名称来选择列。

1 个解决方案

#1

You will need to change your connection string so that data header names are not used. The normal connection string would look something like this:

您将需要更改连接字符串，以便不使用数据头名称。正常的连接字符串看起来像这样：

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;
Extended Properties="Excel 12.0 Xml;HDR=YES";

You need to change the last bit, HDR=YES, to HDR=NO.

您需要将最后一位HDR = YES更改为HDR = NO。

With that type of connection, the columns(fields) then become F1, F2, etc., where F1 = column A, F2 = column B, etc.

使用这种类型的连接，列（字段）然后变为F1，F2等，其中F1 =列A，F2 =列B等。

This is not ideal, since you are now essentially running the query based on the number of the column rather than the name, but with duplicate column names, this is the only way around that.

这并不理想，因为您现在基本上是根据列的数量而不是名称来运行查询，但是使用重复的列名称，这是唯一的方法。

Per the comment from @barrowc: This format of the connection string will treat your column names as data. So depending on your query, you may need to include code to filter out the row that contains your column names.

根据@barrowc的注释：连接字符串的这种格式会将列名称视为数据。因此，根据您的查询，您可能需要包含代码以过滤包含列名称的行。

#1