reading Excel (93-97) sheet with more than 65536 rows using cfspreadsheet

Question

Hi I have to deal with data that is more than 65536 rows. and hence it comes into 2 different Excel Sheets, named as "Details" and "Details_1".

Basically whats happening is uploading Excel sheets, and using "cfspreadsheet" to "read" this data. Once read this is inserted into SQL table.

I am using a component-function to read these 1/2 sheets. The idea is cfif Query recordcount () from "cfspreadsheet" is more than 65533, then read the second sheet too. Then use QoQ and UNION ALL to create a combined query.(Most of the cases there are just 1 sheet but in some cases it is more than 2 sheets.)

It worked fine for till for some time. then all of a sudden it stopped working. I am not sure about wrong/error had crept into it that is causing it to stop. the following is my code

<cftry>
        <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
                </cfquery>
                <!---total record count for "sheet1" & "sheet2"--->
                <cfset rowCount =#excelQuery.recordcount#>              
        <cfelse>                
                <!---this case there is just 1 query "Query1" ;rename it "excelQuery"--->
                <cfquery dbtype="query" name="excelQuery">
                  SELECT * FROM Query1 
                </cfquery>
                <!--- recordcount for "sheet1"--->
                <cfset rowCount =#excelQuery.recordcount#>  
        </cfif> 

        <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>
    </cfcatch>   
</cftry>

** I have done the following:- a) tried to dump individual Query's Query1 and Query2 of each sheet ! Still if its more than 65536 rows the IE page hangs up Unable to read both the sheets 1 and 2.

b) I have placed t he error handling to catch specific Errors such as "Database"

c) When i reduce the number of rows below 65536 or remove the Sheet with larger number of Rows it works.

As i said earlier it was a code that was working and went kaput all of a sudden. **

Answer 1

Perhaps the issue is with JVM's heap size? You might try increasing the maximum heap size, if your environment can handle it.