ColdFusion Spreadsheet Headers Ignore Empty Columns

This is one of those notes-for-my-future-self type posts related to a bizarre behavior with ColdFusion's support. ColdFusion ignores any column at the end of your document where the only data was in the row you specified as a headerrow (assuming you ignore the header row). Got that?

Example

Say you have a spreadsheet that looks like this:

Column A Column B Column C
1 [Empty] [Empty]
2 [Empty] [Empty]
3 [Empty] [Empty]

And you read it into CF with the following command:

<cfspreadsheet action="read" headerrow="1" src="Book1.xls" query="Data" rows="2-4" />

<cfdump var="#Data#" />

You might be surprised to see this as the result:

query
 TEST
11
22
33

It seems like the second two columns have been dropped. Edit your spreadsheet to look like this though,

Column A Column B Column C
1 [Empty] [Empty]
2 [Empty] [Empty]
3 [Empty] x

And you get the following result:

query
 TESTTEST2TEST3
11 [empty string] [empty string]
22 [empty string] [empty string]
33 [empty string] x

Conclusion

ColdFusion drops columns out of the query result when returning columns that would have no data. This presents all kinds of problems if you are expecting certain columns to exist. For instance, if you coded to select Data.ColumnC (assume "Column C" was named "ColumnC", another problem for another time), suddenly you'd have an error even though the column exists in your spreadsheet. What if you wanted to add new columns to your spreadsheet? How would you test if the column did or did not exist? You'd have to loop over each cell in the first row to see if it had data and then assume that the first empty cell in the first row signified having run out of columns (it might just be a gap). CF's spreadsheet stuff is dodgy at best.

 

Comments

Nathan D's Gravatar If you omit "rows" you will get all the data you expected (and in the new 9.0.1 attribute to omit the header row will be handy there), but perhaps more useful in this case is that if you KNOW you'll have a certain number columns you can use the COLUMNS attribute to force it to show you all the columns you want.
Jon Hartmann, July 2011

I'm Jon Hartmann and I'm a Javascript fanatic, UX/UI evangelist and former ColdFusion master. I blog about mysterious error messages, user interface design questions, and all things baffling and irksome about programming for the web.

Learn more about me.

Post a job. Find one. authenticjobs.com

Interested in becoming a sponsor? Contact me.