Stillnet Studios

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.