如何从冷融合查询中读取带有空格的列? [英] 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.

例如，列名称为 [first name,last name,roll].

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

推荐答案

我做了一些疯狂的事情，比如 google 看看人们在其他情况下做了什么，并尝试了各种 SQL 方法来逃避非标准列名(反引号、方括号、双引号、它们的组合)，并画了一个空白.所以我同意@da_didi 的观点，即 QoQ/IMQ 不适合这一点.您应该在 Adobe 错误跟踪器 中提出请求.

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 文件的所有者并说除非您修改列名，否则不能这样做".

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> 文档.

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屋！