Dumping Large Data Sets to Excel in Reports Dashboard

Permalink 1 user found helpful
I have a client that has 3,000 plus records in reports and he would like to be able to click the Export to Excel link and dump the report to Excel (the standard C5 functionality). The problem is that the server is either running out of memory or timing out during the Export to Excel process because of the number of records and he gets a white screen or memory error. I've tried increasing the memory and timeout using the php.ini but with no luck. Any other suggestions on how to either fix this or pull reports for him?

pixo
 
frz replied on at Permalink Reply
frz
I have the same problem when I need user dumps from this site (Which has about 73,000 members)

I just ask one of my guys to get me the data I want with a custom query. You could likely do the same with phpMyAdmin. I'm afraid at that scale (72k, not sure about 3k) you simply have to build a queue engine to save the file locally on the server when it's done being built and THEN spit it out the requestor. That's what places like paypal do when you request a month's worth of transactions in a report.. "You'll get a link to that file in the next 24 hours"... then it usually only takes a half hour.

regardless, nothing exists in the core to do that quite yet.


for just 3k records I imagine you might be able to tweak timeout settings enough. I'd explore the idea of writing the file locally with a job nightly or something however.
pixo replied on at Permalink Reply
pixo
I kind of ended up doing the same. My work around was to add limit 0, 16000 to the SQL select statement in the code and then run the export to Excel so I could break up the export into multiple files. It isn't a solution that will work for the client however so I will play around with your ideas.
msglueck replied on at Permalink Reply
msglueck
how did you solve it in the end??
pixo replied on at Permalink Reply
pixo
I solved it poorly but at least it allowed me to get the data I wanted. In the SQL select statement I added Limit 0, 16000 so that the Excel data set could be done in chunks and then I just incremented up by 16000 each time until I got all of the data. Doing it this way allowed me to grab about 1500 contest entries each time and I just kept doing that until I got all of them. More than 1,500 entries and the Export would error out. Not elegant but it worked in a crunch. I will eventually add an interface for the client that would allow them to do the same thing through the Dashboard so they can continue to pull the data themselves.
TimDix replied on at Permalink Reply
TimDix
Here's my solution to the problem. I had to export 73k form entries, so I ran out of memory, and timed out... I solved it by slicing the array into more manageable chunks so that PHP has to load less into memory, and each slice gets the time limit expanded.

Note: I haven't fully tested it yet, but I thought I'd share before I forgot about it.

public function excel(){ 
      $dateHelper = Loader::helper('date');
      $this->pageSize=0;
      $this->loadSurveyResponses();
      $textHelper = Loader::helper('text');
      $questionSet=$this->get('questionSet');
      $answerSets=$this->get('answerSets');
      $questions=$this->get('questions');   
      $surveys=$this->get('surveys');    
      $fileName=$textHelper->filterNonAlphaNum($surveys[$questionSet]['surveyName']);
      ini_set("memory_limit", "256M");
      header("Content-Type: application/vnd.ms-excel");
      header("Cache-control: private");
      header("Pragma: public");
      $date = date('Ymd');
pixo replied on at Permalink Reply
pixo
Cool solution. Thanks!
mattvin replied on at Permalink Reply
Please help on this solution.
Where do I actually place this file as I am facing similar challenges with an Extended form for a client. It has over 4k entries they want to export to excel now

Maybe your solution could help.