Exporting large amount of data into CSV in CodeIgniter

Question

I have a large amount of data in i.e (around 10 million records) and trying to export in .csv but its completely not working and page continuously loading and then results in a blank page. I set max_execution_time,memory_limit in php.ini file but still its not working then I tried different approaches but no success.

In CodeIgniter I used array to CSV library like this:

$this->load->helper('csv') and also built in function

$this->load->dbutil()

but still it's not working,

so any idea how to achieve this large amount data manage and export CSV file with out an any issue.

Please put the error log here so we can get more idea.Have you check the export limit in php.ini? — Nency, Oct 14 '13 at 08:23
@MarkBaker nothing shows in log,if i give limit to resulting data then its working. — Bhavik Patel, Oct 14 '13 at 08:23
My guess would be either a timeout or a memory limit being exceeded — Mark Baker, Oct 14 '13 at 08:24
@MarkBaker I set this then also not working `set_time_limit(0);` `ini_set('memory_limit', '1G');` — Bhavik Patel, Oct 14 '13 at 08:25
Specify Server,platform,host etc. Also memory 1GB ? Are you sure about that ? — Red, Oct 14 '13 at 10:59
@MarkBaker i set maximum memory size. Do u have any other idea ? — Bhavik Patel, Oct 14 '13 at 11:25

score 4 · Answer 1 · answered Oct 15 '13 at 09:24

You don't say which database you're using, but I've often found with large numbers of records that it's far faster to bypass the framework and export the CSV directly from the query.

In MySQL, this would look like:

SELECT id, name, price INTO OUTFILE '/tmp/products.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
FROM products WHERE 1

score 2 · Answer 2 · answered Oct 14 '13 at 09:08

2

Add "max_input_vars = 3000" or "max_input_vars = 5000" to your php.ini file and then try. Hope this helps you. Do not forget to restart your apache server. You may extend this limit if the error persist. [Note: I had faced a similar issue while exporting a large csv file through browser. The above implementation fixed my issue]

answered Oct 14 '13 at 09:08

Mahavir Munot

1,464
2
22
47

@BhavikPatel Did you restart apache server? if Yes then you need to turn on the error reporting to see the error. This is achievable. Also, Can you share how you are exporting the csv file, is it through browser? Please provide the exact steps that you following – Mahavir Munot Oct 14 '13 at 11:53
yes i am restarting apacher server and here is my code `$this->load->dbutil();` `$delimiter = ",";` `$newline = "\r\n";` `$query = $this->db->query("SELECT * FROM table_name");` `header ("Content-disposition: attachment; filename=csvoutput_" . time() . ".csv");` `header("Content-type: application/csv-tab-delimited-table; charset=utf-8" );` `echo $this->dbutil->csv_from_result($query, $delimiter, $newline);` – Bhavik Patel Oct 14 '13 at 13:13
@BhavikPatel First of all, Check whether the query returns any result before generating the csv. Would love to help you on this tomorrow :) – Mahavir Munot Oct 14 '13 at 13:29
@BhavikPatel you may join http://chat.stackoverflow.com/rooms/39238/codeigniter-issues to discuss about the issue you are facing. – Mahavir Munot Oct 15 '13 at 06:41

score 1 · Accepted Answer · answered Oct 15 '13 at 09:50

You may to generate the csv file using the below example query and then export it:

SELECT id, client, project, task, description, time, date INTO OUTFILE '/path/to/file.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM ts

score 0 · Answer 4 · edited May 23 '17 at 12:09

I don't know what you want to achieve, but changing the set_time_limit is often not recommended. This can lead to an ever running script, and if you call the script every x hours will crash you server in some time.

But as I understand you want to generate a csv file for an user. What you do now is (I guess) an user goes to a link and you start rendering the csv.

What you could do (using javascript) is that when you click the link there starts a progressbar, and you start rendering the file in the background (can even send an email when finished). This way the user can still use the system, also the user won't press reload so you start rendering multiple csv.

Second, I don't know the codeigniter framework, native functions are always faster. But if you need to render 1.000+ rows perhaps you can look into sharing the load. So load 100, insert 100 rows, load 100, insert 100 .... (you get the point I hope) (100 is just a number)

Hope this tips can help

edit --- some code example ---

Well it took some time but here is some code examples of the above.

First we will check every x seconds (i do 60 in the example)(so every min) if the csv exists. To check if the file exists we use this stackoverflow anser and just add a simple javascript interval.

var checkFile = self.setInterval(function(){checkFile()},60000); // interval uses milli seconds as far as I know

function checkFile() {
    if (UrlExists(url)) { // url exists is the function of the answer given
        checkFile = window.clearInterval(checkFile);
        // add other stuff to do
    }
}

With this code you can check if the file is renderd and if so, you can set a message to the user or something else

have you ever try like this and if then give me more info so i can do like above — Bhavik Patel, Oct 14 '13 at 10:37
Well actually not, I had the "luck" that the data wasn't to big, but I will look if I can make an example — MKroeders, Oct 14 '13 at 11:57
Yes you can use pastebin if you have any example or something help full to me — Bhavik Patel, Oct 14 '13 at 13:48

besin · Answer 5 · 2014-01-15T05:58:44.940

0

The simple solution for exporting CSV files with data from database.. Follow the below link..

http://writephp.tuxkiddos.com/2013/02/export-to-csv-ci-helper-file.html https://gist.github.com/opnchaudhary/4744797#file-csv_helper-php for GitHub link

edited Jan 15 '14 at 05:58

answered Jan 15 '14 at 05:51

besin

106
4

Exporting large amount of data into CSV in CodeIgniter

5 Answers5