如何选择[标题文本w.点] AS XXX从Excel表? [英] How to SELECT [header text w. dot] AS xxx FROM an Excel table?

问题描述

摘要::使用Jet.OLEDB提供程序和SQL查询，我不知道如何访问标题文本包含点的列.有什么方法可以在SELECT查询中转义点?

Summary: Using the Jet.OLEDB provider and the SQL query, I do not know how to access the column the header text of which contains a dot. Is there any way to escape the dot in the SELECT query?

详细信息:我正在使用连接字符串

Details: I am using a connection string

Provider=Microsoft.Jet.OLEDB.4.0;Data source=test.xls;Extended Properties="Excel 8.0;HDR=Yes;"

当Excel工作表的列的标题文本中包含点时(请注意名为缩写包装的点列)...

When the header text of the column of an Excel sheet contains a dot (notice the dot column named Abbrev. Packing)...

...然后是这样的SELECT查询...

... then the SELECT query like this...

SELECT
  [Date] AS d,
  [Code] AS code, 
  [Abbrev. Packing] AS packing,
  [Price] AS price
FROM [Sheet1$]

...失败，错误为80004005.当我从标题文本和 SELECT 命令中删除该点时，一切工作正常，并提取了数据.但是，Excel表来自第三方，我无法更改标题的文本.

... fails with error 80004005. When I remove the dot from the header text and from the SELECT command, everything works smoothly, and the data is extracted. However, the Excel table comes from third party, and I cannot change the text of the header.

如何在SELECT命令中转义点，或者如何解决该问题?

How can I escape the dot in the SELECT command, or what is the way to fix it?

推荐答案

当我尝试加载通过SSIS软件包提供的类似电子表格时，SSIS似乎用数字符号(#)替换了句点(.).因此，我想如果您有该选项，您可能需要通过SSIS加载它，否则您可能会尝试像这样直接查询它:

It appears that SSIS replaces period (.) with number sign (#) when I tried to load similar spreadsheet as you provided through a SSIS package. So, I am guessing you would need to either load it through SSIS if you have that option available, otherwise you could probably try querying it directly like this:

SELECT
  [Date] AS d,
  [Code] AS code, 
  [Abbrev# Packing] AS packing,
  [Price] AS price
FROM [Sheet1$]

这篇关于如何选择[标题文本w.点] AS XXX从Excel表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！