7 Replies Latest reply on Jun 17, 2014 1:40 PM by Carl Von Stetten

    cfspreadsheet 400000 lines

    plarts Level 1

      How to read an Excel file ".xlsx" of 420 000 lines ?

      I used cfspreadsheet but,

      over 4000 lines  it fails, saying nothing.


      I tried the CFC (custom tag) : POIutility.cfc from Ben Nadel.

      but it seems it does not work with xlsx,

      does work fine with ".xls" , I can read 65 000 lines

      because ".xls" it limited to 65 000 lines.


      so I have no solution to read 400 000 :lines.


      Thanks for help. ideas ?


        • 1. Re: cfspreadsheet 400000 lines
          Kaif Akbar Level 1

          Hi Plarts,


          In such kind of scenario, you can try to populate the large data by using array of cfspreadsheet so that it can work for your requirement.

          But as populating/processing excel sheet for such kind of scenario may also need more memory so you may increase max heap allocation to CF server if required.



          Kaif Akbar

          • 2. Re: cfspreadsheet 400000 lines
            plarts Level 1


            what do you call : using array of cfspreadsheet

            Also POIutility.cfc  does work fine with current CF, but only with xls not with xlsx



            • 3. Re: cfspreadsheet 400000 lines
              plarts Level 1

              Waiting for a soltution to get an automated process,

              I did this manually, converting from Excel to Access,

              then accessing Access DB.


              But I will need soon a way to automate this, means to be able to read

              the 400 000 lines of Excel sheet.


              Any advise, what tool to try ? (CF tags or custom tags , or any thing else in CF)

              Thnaks, Pierre.

              • 4. Re: cfspreadsheet 400000 lines
                Carl Von Stetten Adobe Community Professional & MVP



                You're operating in "edge case" territory, as processing 400,000 line Excel files is not all that common in ColdFusion (as you say, the older .xls format was limited to 65,000 rows and hitting that limit was not all that common).  Where are these Excel spreadsheets coming from?  Is there a more appropriate format for storing that data besides Excel?

                -Carl V.

                • 5. Re: cfspreadsheet 400000 lines
                  Reed Powell Level 3

                  Carl is correct.  What are you trying to do with the data?  I presume it is going into a database?  If you are talking about either MS SQL Server or Oracle, then you already have great ETL tools available.  Even if you need to take a couple of hours to learn the basics of SSIS for MS SQL Server in order to process a spreadsheet and write the data to a table, it will serve you well.


                  Having said that, the CFSPREADSHEET tag is close to the top of my list of biggest CF disappointments in recent years.  I can open a HUGE spreadsheet on my laptop, manipulate it, sort it, save it, etc.  But CF running on my server just falls apart trying to do the same thing.



                  • 6. Re: cfspreadsheet 400000 lines
                    plarts Level 1

                    This big spreadsheet is the input data I have (4 big files), coming from the client,

                    and the client get it from the government. These data will go to a database, yes.

                    I can have it in text file too.


                    I will try again with the text file.

                    I can't remember what happened.


                    I let you know when I have other results.


                    • 7. Re: cfspreadsheet 400000 lines
                      Carl Von Stetten Adobe Community Professional & MVP



                      If you can get it in a decent text file format (.csv or the like), there are techniques to load the file line-by-line rather than all-at-once (using the FileOpen() function and then using the FileReadLine() functions inside a loop).  That should significantly improve the memory footprint of processing the data.  That is if you are compelled to use ColdFusion to to this.  As @Reed suggested, using the ETL tools (SSIS for SQL Server or whatever Oracle uses) is more efficient and will reduce your headaches.  With SSIS, you can save your import design and reuse it over and over again to add data or make updates to your database.


                      -Carl V.

                      1 person found this helpful