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?

Maddox

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

Wittman

In addition to Peter's answer, keep in mind you could also use the columnNames attribute to assign different column names if you wish ie <cfspreadsheet query=".." columnNames="Foo,Bar,StartDate,Etcetera" ..>. – Catarrhine

@Peter - When I try your first sample code I get the error "Complex object types cannot be converted to simple values." – Maddox

@Peter - this code does work though - <cfoutput query='spreadsheetData'>#spreadsheetData['start date'][currentRow]#</cfoutput>. I wish there was a less explicit way to do this though... – Maddox

@Catarrhine - thanks! It's good to know how to do that. I want this to be as dynamic as possible, though - as in, the columns can be in any order, and we can just access by name – Maddox

@Maddox - Yep, for dynamic cases, Peter's is the better overall/generic answer. FYI, IIRC you always need to use a row number when using queries and bracket notation ie #query["column"][row]#, regardless of whether or not you are inside a query loop. – Catarrhine

Sorry froadie, this is one of ACF's weird quirks/bugs - doing query.col works but doing query['col'] requires the row number. Since I work now 99% of with Railo (where bracket and dot notation are not different from each other), I had forgotton about this. – Wittman

(Just updated/corrected the answer, and added info provided by Leigh about columnNames attribute.) – Wittman