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?

<cffunction name="putExcel" access="remote" returnFormat="plain" output="true"> 
    <cfargument name="xclfile" required="no" type="string" default="0"> 

    <cfset ins =insertUserLog("#Session.user_name#","#Session.user_code#","putExcel function called for  Upload","","")>

        <cfset fileEXCL = "#ExpandPath('../folder')#/#arguments.xclfile#" />                  

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

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

        <cflog file="Collections" application="yes"  text="#Session.user_info.uname# logged in. Data  file #fileEXCL# read. Recordcount:#rowCount#" type="Information">

        <cfset ins =insertUserLog("#Session.user_name#","#Session.user_code#","file #fileEXCL# read. ","Recordcount:#rowCount#","")>

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

    ... etc...
  • \$\begingroup\$ For those who didn't see it, here was the original question on SO: stackoverflow.com/questions/18246447/… \$\endgroup\$ – duncan Aug 15 '13 at 14:50
  • \$\begingroup\$ (Edit) CFSpreadsheet has some memory issues to begin with, and QoQ's are generally for moderate sized resultsets ~5 to 50,000 rows (depending on memory). Combining the two probably pushes it over the edge. If you are ultimately inserting it into a db table it is more efficient to skip cfspreadsheet altogether and use db tools to perform the insert (if possible). That is my preferred approach for importing Excel files into sql server. What is your db type? \$\endgroup\$ – Leigh Aug 15 '13 at 16:21
  • \$\begingroup\$ My DB type is SQL \$\endgroup\$ – Fransis Aug 15 '13 at 16:21
  • \$\begingroup\$ I am ultimately inserting data into SQL \$\endgroup\$ – Fransis Aug 15 '13 at 16:21
  • \$\begingroup\$ Further when i am trying to read sheet 2 there are java-memory issues \$\endgroup\$ – Fransis Aug 15 '13 at 16:22

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:

<cfset count1 =#Query1.recordcount#>

Could just be written as

<cfset count1 =Query1.recordcount>

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?

  • 1
    \$\begingroup\$ Ok so the aditoponal solution i ll try now > 65K 1) Create 2 temp_tables 2) insert this excel data into the temp tables 3) perform union all on these 2 temp tables 4)delete the temp tables latter on. 5) avoid "select *" \$\endgroup\$ – Fransis Aug 15 '13 at 15:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.