如何从冷灌装查询中读取具有空格的列？ [英] How to read columns with spaces from coldfusion queries?

问题描述

我正在从电子表格中读取数据。

I am reading data from a spreadsheet. One of the column in the spreadsheet contains spaces.

例如，列的名称是[名字，姓氏，卷]。

For Example, Columns names are [first name,last name,roll].

读取电子表格后，我得到 qryObj 。
现在当我尝试从查询读取名字

I am getting a qryObj after reading the spreadsheet. Now when i am trying to read first name from the query

<cfquery dbtype="query" name="getName">
    SELECT [first name]
    FROM   qryObj
</cfquery>

这是抛出db错误。我尝试过 ['first name'] ，但仍然是抛出错误。

It is throwing db error. I have tried with ['first name'] also but still it is throwing error.

错误是：

Query Of Queries syntax error.
Encountered "[. Incorrect Select List, Incorrect select column

推荐答案

我做了一些疯狂的事情，如 googling 查看人们在其他情况下做了什么，并尝试了各种SQL方法来转义非标准列名称（反向蜱，方括号，双引号，因此我同意@da_didi，QoQ / IMQ不适用于此，您应该在 Adob​​e bug中提出一张票跟踪器。

I did crazy stuff like googling to see what people had done in other situations, and tried various SQL approaches to escaping non-standard column names (back ticks, square barackets, double quotes, combos thereof) , and drew a blank. So I agree with @da_didi that QoQ/IMQ does not cater for this. You should raise a ticket in the Adobe bug tracker.

您可以执行 SELECT * ，这样就不需要引用列名。或者你可以序列化查询，使用字符串替换重命名列，反序列化它然后QoQ修订的名称。我只能这样做与少量的数据。

You could do SELECT *, which removes the need to reference the column name. Or you could serialize the query, use a string replace to rename the column, deserialise it again then QoQ on the revised name. I'd only do this with a small amount of data though.

或者你可以推回XLS文件的owbner，并说除非你修改你的列名，否则不能。

Or you could push back on the owbner of the XLS file and say "no can do unless you revise your column names".

使用 excludeHeaderRow 从XLS文件中压缩列名称，然后指定自己的列名称。我怎么知道可以这样做？通过 RTFMing < cfspreadsheet> docs 。

You could also perhaps suppress the column names as they stand from the XLS file using excludeHeaderRow,and then specify your own columns names. How did I find out one could do that? By RTFMing the <cfspreadsheet> docs.

这篇关于如何从冷灌装查询中读取具有空格的列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！