9 Replies Latest reply on Nov 16, 2016 11:29 PM by FernandoReyes

    PI Datalink - Output exceeds sheet dimension

    viritha.jonnala


      Hi All,

      I am trying to extract data from a PI tag in excel for every second over a 48 hour period. The error I get is that the ‘Outpu t exceeds sheet dimension. Try changing output cell or changing data orientation or decreasing number of events.’ I have tried doing this on a blank spreadsheet and so assume that it means that this is just too many data points? What is the maximum number of points I can extr act at 1 time? I also tried 1s intervals over a 24 hour period and this also did not work. I am able to retrive only 18hrs data, if i try  to chage the end date then i get an error "Outpu t exceeds sheet dimension. Try changing output cell or changing data orientation or decreasing number of events"

      Thanks Inadvance.

       

      Regards,

      Viritha.

        • Re: PI Datalink
          Eugene Lee

          Hi Viritha,

           

          Are you using the old Excel workbook format? Pre Excel 2007, a worksheet can only contain 65536 rows. For your query, you will get 172800 rows. So this could be causing the problem.

            • Re: PI Datalink
              dtayler

              Hi Viritha,

               

              As well as checking whether you are exceeding the row limit like Eugene mentioned, you may be hitting a limit from the PI server on the maximum number of events allowed to be returned from an archive call. This number can be adjusted from the archive tuning parameters tab in PI-SMT using the ArcMaxCollect parameter. This is set to 1,500,000 by default but would be worth checking too.

              • Re: PI Datalink
                viritha.jonnala

                Hi Eugene and David,

                Thanks for your reply .My excel sheet has  172809 rows.

                Only 18hrs of data am able to fetch and near to 80,000 rows.

                please advise me howo fetch 24hrs of data.

                 

                Thanks.

              • Re: PI Datalink - Output exceeds sheet dimension
                pthivierge

                Hello Viritha,

                 

                You may be facing the know issue 26747OSI8 - VBA function 'dlresize' gives message box when attempting to resize to greater than 65535 rows.

                 

                • You could split the values in two separate Datalink calls to have less than 65000 values per call
                • Or you can re-size the array formula to make it big enough to contain the values and click on the refresh button to update the values.

                 

                Let us know if this works for you

                --

                How to manually resize the array formula ( of a PI  DATALINK function ) :

                • Select the existing data and continue the selection till you have expanded to the space you need. ( see image for example )

                2015-01-07_13-58-24_Book1 - Excel.png

                • Then press F2 to enter edit mode of the array formula
                • Press Ctrl+Alt+Enter to finish and extend the formula to the newly selected range
                • Right click on the selection and select Re-Calculate to refresh the data.
                  • Re: PI Datalink - Output exceeds sheet dimension
                    FernandoReyes

                    Hi! I got kind of the same problem. I wrote a code in VBA to extract Data in an automatic way, but when exceeding 65K rows or so I got that error. is there a way to solve it so the code runs smoothly and that i doesn't involve splitting the data?

                     

                    And its kind of weird but when I call DLRSEIZE from vba code it doesn't generate the data and sends the error but when I call the Resieze manually (right click then reseize) it does generate the data correctly but it still sends the error.

                     

                    hope for some help.

                     

                    Cheers

                    Fernando

                  • Re: PI Datalink - Output exceeds sheet dimension
                    viritha.jonnala

                    Hi Patrice,

                    I have tried your solution to re-size the array formula, But when i right click on value am not able to see the ReCalculation option and am not able to modify the function also for *-2d.

                    • Re: PI Datalink - Output exceeds sheet dimension
                      viritha.jonnala

                      Hi Patrice,

                      GoodDay.

                      Thank you for your reply.

                      Hey sorry i have tried your 1st solution again, the re-calculate option was showing when i right click on values. But still the same error is showing (‘Outpu t exceeds sheet dimension. Try changing output cell or changing data orientation or decreasing number of events').

                      My excel version is 2010 and DataLink 4.1.1.

                        • Re: PI Datalink - Output exceeds sheet dimension
                          pthivierge

                          Hello Viritha,

                           

                          • Does it work if you change the Compress Data function start time and end time, so it retrive less data?
                          • I think that you might need to expand your array formula again.  How big is it now?  How much did you expend it? I recommend that you use the Calculated Data function first, use the same start time and end time as for the compressed data you want to achieve.  Select calculation mode: count. Once you have that, resize the array formula so its rows count is bigger than the event count that you just calculated.

                           

                          Hope this helps.