3 Replies Latest reply: May 15, 2014 10:07 AM by Olly Bond RSS

    Work Around for Export to Excel Exceeds Max Records

    Diane Miller

      When using Project Exports, I received the error "Row count exceeds maximum records (65536) for this format". I understand why I'm receiving the error; Excel 2003 has a maximum of 65536 rows on a worksheet and the data I'm want to export is well over 100,000 records (even if we were allowed to update to the newest version of Excel I would still surpass the max number of lines).


      I had a great idea but it didn't work. The idea was to add to my filter an expression to limit the results to the first 65000 rows "Rowno()<65000" then do the same thing for the second 65000 and so on. In therory it should work, however I received another error "Invalid filter expression. A filter may not depend on the Rowno() function either directly or indirectly."


      Next thought was to create a calculated field called Row Number where the results simply returned Rowno(). The calculated field worked great but when I tried to include this in my filter "[Row Number]<65000", I got the same invalid filter expression error as I did with Rowno().


      So long story short... Using Project Exports, I want to export to an Excel worksheet the first 65,000 records from a filtered table, then export records 65,001 through 130,000 to another Excel worksheet, then export records 130,001 through 195,000 to another one, etc.


      I know I can export all of the records to an Acess database but I'm still stuck with the info not being included in my reconciliation spreadsheet.

        • Work Around for Export to Excel Exceeds Max Records
          Data Kruncher

          Just be clear, Excel 2007/2010 supports over one million rows per sheet. I'd hate to be the person doing a detailed transactional reconciliation with that many rows.


          As you've discovered, you cannot build a filter that relies on RowNo() in any way, shape, or form.


          In the past, solutions to this sort of challenge rely on filtering with some other key field, such as Customer names starting with "A", then "B", then "C", etc. or some other device, so as to develop smaller selections.


          To facilitate the export, when you do get a set of filters that satisfy your requirement, try to duplicate the Table structure (assuming that you're trying to export from the Table) as a summary, then duplicate the initial summary, changing the associated filter as necessary. Then you can build a project export that exports all summaries as different sheets in a single destination file.


          What field can you use in your data set to use as the basis for smaller sets?

            • Work Around for Export to Excel Exceeds Max Records
              Diane Miller

              I was afraid it wouldn't work any other way and had my heart so set on doing this that I didn't even think about sorting based on an alpha split on the consumer's name. As for exporting all of the filters into a single workbook, I'm already doing that.


              Thanks so much for your help. You are so knowledgeable about Monarch that I know I can depend on what you suggest.


              Have an awesome day.

              • Work Around for Export to Excel Exceeds Max Records
                Olly Bond

                Hello everyone,


                Filtering won't work with Rowno as the Row Number is only calculated after the filter is applied. It will work fine using Recno - the Record Number is the row from the unfiltered table.


                A calculated field WORKSHEET = 1+INT((Recno()-1)/65000) will give a value of 1 for the first 65,000 rows, 2 for the next and so on.  You can then export filters for different values of WORKSHEET.


                Hope this helps,