ColdFusion Spreadsheet Headers Ignore Empty Columns
- August 31, 2011 11:08 AM
- ColdFusion, Mystery Error Message
- Comments (1)
This is one of those notes-for-my-future-self type posts related to a bizarre behavior with ColdFusion's
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:
<cfdump var="#Data#" />
You might be surprised to see this as the result:
| query | |
|---|---|
| TEST | |
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
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 | |||
|---|---|---|---|
| TEST | TEST2 | TEST3 | |
| 1 | 1 | [empty string] | [empty string] |
| 2 | 2 | [empty string] | [empty string] |
| 3 | 3 | [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