How to access query column with multiple words?

cfspreadsheet, coldfusion

Solution

When using bracket notation the contents must end up as a string, so:

<cfoutput query="spreadsheetData">
    #spreadsheetData['start date'][CurrentRow]#
</cfoutput>

If you don't use quotes, you are passing in a variable, which is done like so:

<cfset ColumnName = 'start date' />

<cfoutput query="spreadsheetData">
    #spreadsheetData[ColumnName][CurrentRow]#
</cfoutput>

Note that you must use the query name before the brackets - if you simply write `[ColumnName]` then this is inline array creation notation, not accessing the variable.

Also, if using this outside of a query loop (i.e. not within `cfoutput`/`cfloop` with query attribute), you also need to scope the `CurrentRow` variable, i.e.

spreadsheetData[ColumnName][spreadsheetData.CurrentRow]

(or provide your own explicit number/variable).

As Leigh notes below, for cfspreadsheet-specific behaviour, you can also specify the `columnnames` attribute, to rename the column to something directly accessible, e.g.

<cfspreadsheet query=".." columnNames="Foo,Bar,StartDate,Etcetera" ..>

Problem

I'm using a cfspreadsheet read to read a sheet into a query object. ``` <cfspreadsheet action="read" src="TestExcel.xls" sheet="1" query="spreadsheetData" headerrow="1" excludeHeaderRow="true"> ``` The problem is, some of the headers contain more than one word. So I end up with a query a bit like this: ``` ID Name Start Date End Date 3 Test 1/1/2009 1/1/2013 17 Test 2 11/11/2010 11/11/2012 ``` If I try to access one of the columns that have a space in the column name, I get an error. ``` <cfoutput query="spreadsheetData"> #start date# </cfoutput> ``` I've tried `#[start date]#` as well, but that didn't work. I cannot control the format of the excel sheet that I receive. Is there any way to access the multiple-worded-header columns?

Original source