Highlighted

cfspreadsheet creating extra null columns

New Here ,
May 08, 2020

Copy link to clipboard

Copied

Hi there,

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

 

     
12345
14345
12345
12345
12345

 

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_1col_2col_3col_4col_5
112[empty string][empty string][empty string]
214[empty string][empty string][empty string]
312[empty string][empty string][empty string]
412[empty string][empty string][empty string]
512[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.

Views

286

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

cfspreadsheet creating extra null columns

New Here ,
May 08, 2020

Copy link to clipboard

Copied

Hi there,

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

 

     
12345
14345
12345
12345
12345

 

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_1col_2col_3col_4col_5
112[empty string][empty string][empty string]
214[empty string][empty string][empty string]
312[empty string][empty string][empty string]
412[empty string][empty string][empty string]
512[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.

Views

287

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
May 08, 2020 0
BKBK LATEST
Adobe Community Professional ,
May 10, 2020

Copy link to clipboard

Copied

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 . 

 

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
May 10, 2020 0