Board index   FAQ   Search  
Register  Login
Board index PHP PHP Scripts

Export huge amount of data to CSV

Links for php scripts

Moderators: macek, egami, gesf

Export huge amount of data to CSV

Postby adwairi » Mon May 14, 2012 8:46 am

Hi all

I want to export data to CSV file, the data contain more than 20000 records, and an errors appear said:
"Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 80 bytes)"
OR
"Fatal error: Maximum execution time of 30 seconds exceeded".
I already increase the memory size and I wouldn't increase the execution time, I just want to export the data in multiple CSV files under .zip file.

note: I already use header() function in my code.

Thank you.
adwairi
New php-forum User
New php-forum User
 
Posts: 4
Joined: Mon May 14, 2012 8:25 am

Re: Export huge amount of data to CSV

Postby Nullsig » Mon May 14, 2012 11:24 am

Where is the data stored?
User avatar
Nullsig
php-forum Fan User
php-forum Fan User
 
Posts: 981
Joined: Thu Feb 17, 2011 6:52 am
Location: Racine, WI

Re: Export huge amount of data to CSV

Postby adwairi » Mon May 14, 2012 11:45 am

@ Nullsig
data returned from database as multidimensional array, this array contain more than 20000 nodes and each node consider as record
adwairi
New php-forum User
New php-forum User
 
Posts: 4
Joined: Mon May 14, 2012 8:25 am

Re: Export huge amount of data to CSV

Postby adwairi » Mon May 14, 2012 12:35 pm

really I need your help, just give me an Ideas .. no need for code.
adwairi
New php-forum User
New php-forum User
 
Posts: 4
Joined: Mon May 14, 2012 8:25 am

Re: Export huge amount of data to CSV

Postby Nullsig » Tue May 15, 2012 5:37 am

Why don't you just dump the data directly from the database?

If that isn't possible because you have to process the data with a script before dumping it then you could do this to segregate the information over multiple files:

Code: Select all
$sql = "SELECT blah FROM blah WHERE blah blah blah";
if($rs = mysql_query($sql)){
   $i = 1; //this will be appended to the end of the file name.
   //This will be used to detect when to create a new file
   //initialize at zero to trigger the file creation at the beginning
   $j = 0;
   while($row = mysql_fetch_assoc($rs)){
      if($j % 10000 == 0){ //The 10000 here means that each file will contain 10000 rows
         if(isset($fp)){
            //first iteration the $fp variable will not be set,
            //therefore we will not try to close it.
            fclose($fp);
         }
         
         //initialize the current file
         $fp = fopen('randomFile' . $i . ".csv", w+);
         $i++;
         
         //technically you don't need to do this but for
         //REALLY large datasets you may overflow the integer value.
         //This also helps if you aren't iterating $j on every row
         $j = 1;
      }
      
      
      //Process your row and insert the processed data here
      
      
      $j++;//last line will iterate your current row count. This can be iterated on successful write to the file too.
   }
}
 
User avatar
Nullsig
php-forum Fan User
php-forum Fan User
 
Posts: 981
Joined: Thu Feb 17, 2011 6:52 am
Location: Racine, WI

Re: Export huge amount of data to CSV

Postby adwairi » Tue May 15, 2012 6:45 am

@Nullsig
Yes I have to process the data with a script before.

Thank you Nullsig, I will try it now.
adwairi
New php-forum User
New php-forum User
 
Posts: 4
Joined: Mon May 14, 2012 8:25 am


Return to PHP Scripts

Who is online

Users browsing this forum: Majestic-12 [Bot] and 1 guest

Sponsored by Sitebuilder Web hosting and Traduzioni Italiano Rumeno and antispam for cPanel.