Bulk Exporting Data from Codeigniter to CSV using Database Utility Class

Question

Here is my code:

function export_csv()
{
$st = $this->input->get('st');   //Start Date 
$en = $this->input->get('en');   //End Date

$sTable = 'TABLE_NAME';

$this->load->dbutil();

$aColumns = array('tempdisplayid AS ucid','uui','campaign_name','location','caller_id','skill','calltime','answertime','TIMEDIFF(answertime,calltime) as timetoanswer','endtime','talktime','duration','fallback_rule','dialed_number','type','agent','agent_did','disposition','status','hangup_by','transfer','transfer_to','comments','dial_status','customer_status','agent_status','audio','AgentStatus','CustomerStatus','user_response');

$this->db->select('SQL_CALC_FOUND_ROWS '.str_replace(' , ', ' ', implode(', ', $aColumns)), false);

$query = $this->db->get_where($sTable, array('date(calltime) >=' =>$st,'date(calltime) <=' =>$en));

$new_report = $this->dbutil->csv_from_result($query);

write_file('/csv/records-'.$st.'to'.$en.'.csv', $new_report);

$this->load->helper('download'); 

force_download('records-'.$st.'to'.$en.'.csv', $new_report); 

}

There is 64,145 Records in last 30 days. When I try to download the link becomes dead.is there any other method to bulk export unlimited record to csv.

I tested this code upto 30000 after setting ini_set('max_execution_time', 0);It works fine.

Anything other than CSV like xls which can show bulk records.

try creating a zip file of your csv and than download.. you can use codeigniter's encoding class check it here ellislab.com/codeigniter/user-guide/libraries/zip.html — Nishant Solanki
– Nishant Solanki, Commented Jun 9, 2015 at 6:27
@Kiranarya have you tried limiting the data?? if thats working?? — Nishant Solanki
– Nishant Solanki, Commented Jun 9, 2015 at 7:01

Akshay Hegde · Accepted Answer · 2015-06-09 06:42:32Z

1

// top of your controller
ini_set('max_execution_time', 0);

// Also you can increase memory
ini_set('memory_limit','2048M');

Download this helper and place in system/helpers/

and finally create csv like this

$this->db->select('*'); 
$query = $this->db->get('your_table');
$this->load->helper('csv');
query_to_csv($query, TRUE, 'filename.csv');

edited Jun 9, 2015 at 6:42

answered Jun 9, 2015 at 6:32

Akshay Hegde

17.1k2 gold badges25 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Bugfixer Over a year ago

Upto 10 MB CSV with 24000 records my code is working fine but not more than that

Akshay Hegde Over a year ago

Make a try with this, and let me know

Akshay Hegde Over a year ago

dead link meaning ? try to increase memory as well

Bugfixer Over a year ago

Dead link means nothing just white blank page

Bugfixer Over a year ago

I increased max_execution_time and memory_limit both.but page is loading from last 4-5 minutes.I think it worked hopefully by the end of the this day i will get my CSV file

|

Saty · Accepted Answer · 2015-06-09 06:29:09Z

0

I think there is problem in max_execution_time of codeignator

You can increase it by using below code

Go to the file

system/core/CodeIgniter.php

Ans search for set_time_limit you find below code. Here you can increse your time

if (function_exists("set_time_limit") == TRUE AND @ini_get("safe_mode") == 0)
{
    @set_time_limit(300);// increase according to your requirmrnt
}

answered Jun 9, 2015 at 6:29

Saty

22.5k7 gold badges35 silver badges52 bronze badges

3 Comments

Bugfixer Over a year ago

@set_time_limit(1000); @set_time_limit(10000); but still white blank page

Nishant Solanki Over a year ago

300 means 5 minutes.. check this php.net/manual/en/function.set-time-limit.php... and if dont want to timeout the page than one should use set_time_limit(0);..

Saty Over a year ago

Thanks @NishantSolanki for your link am bit confuse in second or minutes now it clear

Collectives™ on Stack Overflow

Bulk Exporting Data from Codeigniter to CSV using Database Utility Class

2 Answers 2

7 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related