I love the CFSPREADSHEET tag that was added to ColdFusion9. It makes working with spread sheet data so easy. The spreadsheets I am given to work with often contain descriptive column names that contain spaces like “First Name” or “Home Phone”. This causes a problem when you try to work with the data in a QoQ (query of a query).

Lets say you have a spread sheet containing these columns: Name, City, State, Postal Code. Then pull it into a query using the cfspreadsheet tag:
<cfspreadsheet action="read" src="c:\Customers.xls" query="customers" headerrow="1" rows="2-65536" sheetname="Customers">

Now try to narrow down the results to only those in a certain postal code. This won’t work of course:
<cfquery name="qryCA" dbtype="query">
SELECT * FROM customers WHERE [Postal Code] = '90210'
</cfquery>

Neither will this:
<cfquery name="qryCA" dbtype="query">
SELECT * FROM customers WHERE [Postal\ Code] = '90210'
</cfquery>

Or this:
<cfquery name="qryCA" dbtype="query">
SELECT * FROM customers WHERE ['Postal Code'] = '90210'
</cfquery>

I have not found any way to escape a space in a column name inside QoQ. But, thankfully there is a way we can manipulate the column names to remove the space.

<cfset colNameArray = customers.getColumnNames() />
<cfloop from="1" to="#arrayLen(colNameArray)#" index="i">
	<cfset colNameArray[i] = colNameArray[i].replace(' ','') />
</cfloop>
<cfset customers.setColumnNames(colNameArray) />

You could use this to completely rename column names if you wanted to. This isn’t just useful for working with cfspreadsheet either, I could see this being used in other circumstances to change the column names.

Thanks to Steven Neiland for pointing me in this direction.

3 Comments

  1. Sunisa says:

    To easily refer to QoQ from cfspreadsheet query, you could remove the ‘headerrow’ attribute. The query will then refer the 1st column name as ‘col_1′, 2nd as ‘col_2′ …as default. You could use these default column names in your QoQ without worrying the actual column name with space or without spaces.

  2. Ryan says:

    Sunisa that is a good tip, thank you. If you are working with a predictable order of columns that would work well.

  3. Derek says:

    Thanks for this!!

Leave a Reply

You must be logged in to post a comment.