0

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. **

Fransis
  • 275
  • 1
  • 5
  • 15
  • 3
    Can you pls quantify "stopped working" does it error (if so, post the full error)? Does it produce incorrect results? Does it apparently do absolutely nothing? Also, what troubleshooting have you thusfar done? This seems like more of a "fix my code" exercise than asking a specific question. – Adam Cameron Aug 15 '13 at 07:11
  • If you post this code on Code Review (http://codereview.stackexchange.com/), I have a few not-related-to-this-issue observations to make. – Adam Cameron Aug 15 '13 at 07:13
  • I have done acodrdingly – Fransis Aug 15 '13 at 12:46
  • 1
    What version of Excel are you using? – vas Aug 15 '13 at 12:47
  • 1
    Have you tried making the excel a datasource and not use cfspreadsheet? – Cory Fail Aug 15 '13 at 13:25
  • Getting the errors while dumping the result:- "upon dumping it is giving `-XX:-UseGCOverheadLimit coldfusion` and `Java Heap Space` error." – Fransis Aug 15 '13 at 14:16
  • 1
    Not to throw this way off topic, but instead of xls format is using .csv an option? This will get you past the 65k row limits – steve Aug 15 '13 at 14:35
  • 1
    Now on CodeReview: http://codereview.stackexchange.com/questions/29790/reading-excel-93-97-sheet-with-more-than-65536-rows-using-cfspreadsheet – duncan Aug 15 '13 at 14:51
  • 1
    Check the bug database. There are a few memory issues reported w/cfspreadsheet and moderate size files: [1](http://forums.adobe.com/thread/574932) , [2](https://bugbase.adobe.com/index.cfm?event=bug&id=3041105), possibly due to a bug in POI. Also, what are you doing with the results? If you are inserting the records into a db table, have you considered other options like @CoryFail mentioned, such as an Excel DSN or tools like OPENROWSET (if you are using SQL Server)? – Leigh Aug 15 '13 at 14:55
  • 1
    *Once read this is inserted into SQL table* Oops, missed that line. The options depend on your DBMS. Which one/version are you using? – Leigh Aug 15 '13 at 16:22

1 Answers1

4

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

bwhet
  • 158
  • 2
  • 12