Reading Excel (93-97) sheet with more than 65536 rows using cfspreadsheet I have a process that reads multiple sheets from an Excel file, then inserts the data into a database table. However, I am experiencing some memory issues when one of the sheets contains more than 65536 rows and am looking for ideas on how to improve the code.

In summary, I am using cfspreadsheet to "read" an uploaded Excel file. (In most cases, the file contains a single sheet. However, in some cases it is more than 2 sheets.) The process always reads the first sheet ie "Details" . If more than 65533 rows are found, it then reads the second sheet too i.e. "Details_1". Finally, I use a QoQ and UNION ALL to create a combined query. Once read, the data is inserted into a database table.

Can anyone offer suggestions for improving the process to make it less memory intensive?

&lt;cffunction name="putExcel" access="remote" returnFormat="plain" output="true"&gt; 
    &lt;cfargument name="xclfile" required="no" type="string" default="0"&gt; 

    &lt;cfset ins =insertUserLog("#Session.user_name#","#Session.user_code#","putExcel function called for  Upload","","")&gt;

    &lt;cftry&gt;
        &lt;cfset fileEXCL = "#ExpandPath('../folder')#/#arguments.xclfile#" /&gt;                  

        &lt;!---when there e 2 Sheets ---&gt;      
        &lt;!---get info from  sheet1 as a "query1"---&gt;           
        &lt;cfspreadsheet action="read" src="#fileEXCL#" sheet="1" query="Query1" headerrow="1" /&gt;
         &lt;!--- recordcount for "sheet1" as "count1"---&gt;                
        &lt;cfset count1 =#Query1.recordcount#&gt;
        &lt;!--- case when excel has more than 65533 rows                    
            ;THIS IMPLIES THAT THERE 2 SHEETS)---&gt;   
        &lt;cfif count1 gt 65533&gt;
         &lt;!--- take info from  sheet 2 as a "query2" and count as "count2"---&gt;   
            &lt;cfspreadsheet action="read" src="#fileEXCL#" sheet="2" query="Query2" headerrow="1"  /&gt;

            &lt;cfset count2 =#Query2.recordcount#&gt;
            &lt;!---club both query's using QoQ and call it "excelQuery"---&gt;
            &lt;cfquery dbtype="query" name="excelQuery"&gt;
                SELECT * FROM Query1
                UNION ALL  
                SELECT * FROM Query2
            &lt;/cfquery&gt;
            &lt;!---total record count for "sheet1" &amp; "sheet2"---&gt;
            &lt;cfset rowCount =#excelQuery.recordcount#&gt;              
        &lt;cfelse&gt;                
            &lt;!---this case there is just 1 query "Query1" ;rename it "excelQuery"---&gt;
            &lt;cfquery dbtype="query" name="excelQuery"&gt;
                SELECT * FROM Query1 
            &lt;/cfquery&gt;
            &lt;!--- recordcount for "sheet1"---&gt;
            &lt;cfset rowCount =#excelQuery.recordcount#&gt;  
        &lt;/cfif&gt; 

        &lt;cflog file="Collections" application="yes"  text="#Session.user_info.uname# logged in. Data  file #fileEXCL# read. Recordcount:#rowCount#" type="Information"&gt;

        &lt;cfset ins =insertUserLog("#Session.user_name#","#Session.user_code#","file #fileEXCL# read. ","Recordcount:#rowCount#","")&gt;

        &lt;cfcatch type="any" &gt;           
            &lt;cflog file="Collections" application="yes" text="Error in reading Data  file #fileEXCL#." type="Error"&gt;
            &lt;cfset ins =insertUserLog("#Session.user_name#","#Session.user_code#","error file","failed","#cfcatch.Message#")&gt;
            &lt;cfreturn 1&gt;
        &lt;/cfcatch&gt;   
    &lt;/cftry&gt;

    ... etc...
This is in a function, but you haven't given us the complete function.  How's your var scoping?  Maybe update your code to show us all the function.

You don't need to use the octothorpes in most cfset statements like this:

&lt;cfset count1 =#Query1.recordcount#&gt;


Could just be written as

&lt;cfset count1 =Query1.recordcount&gt;


Do you have to use SELECT * or could you specify columns?

And most importantly, and the reason it's probably timing out...  you have 2 queries.  By using UNION ALL to join them together, you're taking the 65533 rows of the first sheet and joining it to the X rows in the second sheet.  That's a pretty big dataset, especially if you're just using Excel.  Have you considered using a database?  Do you really need all this data at this point?