4 Replies Latest reply: May 15, 2014 10:11 AM by warlok RSS

    Threshold in excel spreadsheets

    warlok

      Hi all,

       

      This is more of a general query and truthfully there may not be a solution at all.

       

      I currently have a Monarch program that strips data and appends it to a spreadsheet. It currently writes about 14000 records a day. The end user is using Excel 2003 and anyone who uses this product can plainly see that after 5 or 6 days, the spreadsheet has maxed out the number of rows it can use 65536 I believe is the row limit. The process is designed to drop the information from the spreadsheet every 30 days. SO there is a big short fall between the data I'm able to collect and the data I'm wanting to collect. The one solution of upgrading the Version of Excel to 2007 or 2010 would suffice and I'm pretty sure prevent the limits from being reached, but I was wondering perhaps if there was a secondary storage option of data availble to use with Monarch. Do you think using a .PDF file would work? Bare in mind I'm neding to append daily data to the storage output.

       

      Is it possible to read in data from a text input and append the output to a pdf report? I get the feeling The only solution will be the Excel upgrade But I figured I'd pose the question and see if anyone had any other ideas.

        • Threshold in excel spreadsheets
          elginreigner _

          There is an easier solution. Monarch can export using the XLSX format. Excel 2003 can open this format using the converter, which is available free from Microsoft. This will cover everything you need to know about XLS/XLSX conversions, http://office.microsoft.com/en-us/excel-help/use-office-excel-2007-with-earlier-versions-of-excel-HA010077561.aspx[/url] .

           

          Another solution would be separate tabs, one per day would also solve the limit.

           

          FYI: Just as you have a limit in XLS (65,536 rows or 256 squared) you also have a limit in XLSX (1,048,576 rows or 1024 squared)

            • Threshold in excel spreadsheets
              RalphB _

              Warlock,

               

              You can also export out to an Access database which as a max of somthing like 10,000,000 records which would be a solution for you.  That limit is available in your version of Office 2003.

               

              Your profile shows you have Monarch V8.  It has been quite some time since I've used V8 that I don't remember which versions of Excel you can export to.  You may be limited to the Office 2003 version of Excel.

               

              HTH

                • Threshold in excel spreadsheets
                  warlok

                  Thanks for the suggestions.

                   

                  I've already been looking into the change to the .XLSX format. I didn't realize that the row limitaion would change in Excel 2003 if I used the newer format but thinking back it does kind of make sense that it would. It's something for me to test and see.

                   

                  If not I may have to give the Access database a shot and see. I had thought the limitation was smaller than Excel but in all honesty hadn't looked too deep into this solution.

                   

                  Both are excellent suggestions I hadn't thought of. Thanks to you both.

                    • Threshold in excel spreadsheets
                      warlok

                      Just wanted to update, I've fixed the issue by switching the excel 2003 spreadsheet with an access 2003 database. One big issue I hadwas that once the fields were linked to a certain field length. I had a hard time getting thme switched in the link to accept the new length. Ultimately I had to delete the link and reenter it again from scratch for it to work correctly, but in the end it works like a charm. Thanks again to all that posted suggestions.