A query created from Query returned from a cfspreadsheet that does not have the correct values

Question

Today I came across a very strange case, reading vlue from a spreadsheet and trying to filter them under the condition and create a table from the filtered data. Here are my steps

• Read Excel sheet

  <cfspreadsheet action="read" src="#local.sFilePath#" excludeHeaderRow="true" headerrow ="1" query="local.qExcelData" sheet="1" /> 

• Create a hold request

   <cfset local.columnNames = "LoanNumber,Product," /> <cfset local.qSuccessData = queryNew(local.columnNames,"VarChar,VarChar") /> 

• Filter the returned Excel request in state and add valid ones to the new Holding request

   <cfloop query="local.qExcelData" > <cfif ListFind(local.nExceptionRowList,local.qExcelData.currentrow) EQ 0> <cfset queryAddRow(local.qSuccessData) /> <cfset querySetCell(local.qSuccessData, 'LoanNumber', local.qExcelData['Loan Number']) /> <cfset querySetCell(local.qSuccessData, 'Product', local.qExcelData['Product']) /> </cfif> </cfloop> 

• Create a new table

   <cfspreadsheet action="write" query="local.qSuccessData" filename="#local.sTempSuccessFile#" overwrite="true"> 

However, I get the following content on my excel sheet

 Loannumber Product coldfusion.sql.column@87875656we coldfusion.sql.column@89989ER 

Please help for this to work.

Answer 1

I believe that the request loop does not correctly display the values ​​in the Holding request.

Modify your loop as shown below:

 <cfloop query="local.qExcelData" > <cfif ListFind(local.nExceptionRowList,local.qExcelData.currentrow) EQ 0> <cfset queryAddRow(local.qSuccessData) /> <cfset querySetCell(local.qSuccessData, 'LoanNumber', local.qExcelData['Loan Number'][currentRow]) /> <cfset querySetCell(local.qSuccessData, 'Product', local.qExcelData['Product'][currentRow]) /> </cfif> </cfloop>