How to access query column with multiple words?

Question

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?

Answer 1

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" ..>