如何选择字段名称包含点的列 [英] How to SELECT column which field name contains a dot

问题描述

我正在使用 ADO 从 Excel 工作簿导入数据.我在使用一个工作表时遇到了一些问题，其中一个列名包含一个点:Col.1".

I am using ADO to import data from Excel workbooks. I'm having some troubles with one worksheets where one column name contains a dot : "Col.1".

我尝试了所有我发现的东西:双引号、方括号、背杆.什么都行不通.要么引发错误，要么在每一行上查询输出Col.1".

I tried everything I found : double quotes, brackets, backstick. Nothing works. Either an error is raised or the query output "Col.1" on every row.

QUERY_SQL = _
"SELECT `Col.1`, Col3 FROM [table$] " & _
"IN '" & SourcePath & "' " & CHAINE_HDR

鉴于我无法重命名源文件中的列，

Given the fact that I can't rename the column in the source file,

如何使用名称而非编号 ([F1]) 手动选择此列?

推荐答案

我终于找到了如何从该列中检索数据.

I finally found how to retrieve data from this column.

您需要替换."通过#" 并将列名放在方括号或支持中:

You need to replace "." by "#" and put the column name in square brackets or backsticks :

QUERY_SQL = _
"SELECT `Col#1`, Col3 FROM [table$] " & _
"IN '" & SourcePath & "' " & CHAINE_HDR

但是这在 INSERT INTO 查询中不起作用.:

INSERT INTO [sheet$] (Col2, `Col#4`) IN '" & TargetPath & "' 'Excel 12.0;' " & QUERY_SQL

虽然这对我来说问题不大，因为我可以重命名目标工作表中的所有字段，但这仍然是一种奇怪的行为.

While this is less problematic for me as I can rename all field in the target sheets, this is still a strange behaviour.

这篇关于如何选择字段名称包含点的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！