I have a simple xlsx with 5 columns and 6 rows created in excel 2010.
When I use
<cfspreadsheet action="read" src="#path##cffile.serverFile#"
query = "getProductionDAta" sheetname = #worksheet#>
and do a cfdump, I get this:
|1||1||2||[empty string]||[empty string]||[empty string]|
|2||1||4||[empty string]||[empty string]||[empty string]|
|3||1||2||[empty string]||[empty string]||[empty string]|
|4||1||2||[empty string]||[empty string]||[empty string]|
|5||1||2||[empty string]||[empty string]||[empty string]|
Looks like it is reading the columns that I don't want and just clearing them. Normally, that is fine as I know how many columns I am requesting and can ignore the extra columns. But in production, I am trying to extract a few hundred columns from a large spreadsheet and there are around 1000 blank columns in the query result.
Quite annoying, isn't it? But I think it's normal behaviour. I wouldn't expect any memory issues. All the [empty string] would be just nulls in memory.
You could proceed as follows after the cfspreadsheet tag:
<cfloop array="#listToArray(getProductionData.columnlist)#" item="columnName"> <!--- Delete all query columns, with exception of col_1 and col_2 ---> <cfif columnName is not "col_1" and columnName is not "col_2"> <cfset queryDeleteColumn(getProductionData, columnName)> </cfif> </cfloop> <cfdump var="#getProductionData#" >
In any case, you should put in a Feature Request for a new attribute in cfspreadsheet to omit the superfluous query columns .