cfspreadsheet creating extra null columns

[kkcchan]

Hi there,

I have a simple xlsx with 5 columns and 6 rows created in excel 2010.

1	2	3	4	5
1	4	3	4	5
1	2	3	4	5
1	2	3	4	5
1	2	3	4	5

When I use

<cfspreadsheet action="read" src="#path##cffile.serverFile#"
columns="1-2" rows="2-6"
query = "getProductionDAta" sheetname = #worksheet#>

and do a cfdump, I get this:

query

	col_1	col_2	col_3	col_4	col_5
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. Just wondering whether this is normal behaviour and whether it will cause memory issue when extracting from large spreadsheet.I am using cf2018 with the latest update.

[BKBK]

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 .