How to Export data from MySQL to CSV file in Laravel

 

Here we will discuss about how to export data in .csv format from the database in laravel.Suppose you have a table in database which contains few records. So when you want to show that table data on any listing page, instead of displaying all the data, you show some sample data from the database table. So, if you want to see the whole data present in the table, you can give an option to export them in a .csv file. Using laravel you can export your data in different ways.

Database
Create your country table with below sql code.

CREATE TABLE country (
     country_id INT NOT NULL AUTO_INCREMENT,
     country_name VARCHAR(50) NOT NULL,    
     PRIMARY KEY (country_id)
);

Sample Data
Insert sample country data with below sql code.

INSERT INTO country (country_id,country_name) VALUES
(1, 'Afghanistan'),
(2, 'Armenia'),
(3, 'India'),
(4, 'Bahrain'),
(5, 'Bangladesh'),
(6, 'Bhutan');

Routes:
Add get route in your “app/Http/routes.php” file.

Route::get('download', 'CountryController@download');

Controller:
Create your CountryController.php controller file under “app/Http/Controllers” folder.Then add the below code.

<?php 
namespace App\Http\Controllers;
use App\Http\Controllers\Controller;
use Illuminate\Http\Request;
use DB;

class CountryController extends Controller {
	
	/*Export Data*/
	public function export(Request $request){		
		$countries=DB::table('country')->select('country_id','country_name')->get();
		$tot_record_found=0;
		if(count($countries)>0){
			$tot_record_found=1;
			//First Methos			
			$export_data="Country Id,Country Name\n";
			foreach($countries as $value){
				$export_data.=$value->country_id.','.$value->country_name."\n";
			}
			return response($export_data)
				->header('Content-Type','application/csv')				
				->header('Content-Disposition', 'attachment; filename="download.csv"')
				->header('Pragma','no-cache')
				->header('Expires','0');						
		}
		return view('download',['record_found' =>$tot_record_found]);	
	}
	
}	
?>

Here I will discuss 2 different ways to export data from database.

Using response method :
When user request for any action all routes or controllers sent back HTTP response to the user’s browser.So response methods send response to your browser as well.If you want to use this method for export your data then do not forget to attached the HTTP header method.Using header method you can set different headers to the response.The header method accept 2 arguments. First argument should be your header type and second parameter should be your header value.

Using download method :
In this method, laravel generates a response and it forces the browser to download the file at the given location.The download method accept upto 4 parameters.The first parameter is full file path, second parameter is the name of the file which is seen by the user while downloading the file, third parameter is the header which should be an array of parameters and last one is disposition (which support either attachment or inline).By default it uses attachment parameter for content disposition. Below is the code which uses download method. Just replace your export method in your countrycontroller with below code.

    /*Export Data*/
	public function export(Request $request){		
		$countries=DB::table('country')->select('country_id','country_name')->get();
		$tot_record_found=0;
		if(count($countries)>0){
			$tot_record_found=1;
			
			$CsvData=array('Country Id,Country Name');			
			foreach($countries as $value){				
				$CsvData[]=$value->country_id.','.$value->country_name;
			}
			
			$filename=date('Y-m-d').".csv";
			$file_path=base_path().'/'.$filename;	
			$file = fopen($file_path,"w+");
			foreach ($CsvData as $exp_data){
			  fputcsv($file,explode(',',$exp_data));
			}	
			fclose($file);			
	
			$headers = ['Content-Type' => 'application/csv'];
			return response()->download($file_path,$filename,$headers );
		}
		return view('download',['record_found' =>$tot_record_found]);	
	}

Blade Template
Create your “download.blade.php” template file under “app/resources/view” folder.

If there is no any record found from your database then you can show “no record found” message to the user.Its depends on you whether you want to show message to the user or not.

@if(isset($record_found) && $record_found==0)
	No record found.
@endif

COMMENTS ( 24 )

Somebody essentially assist to create seriously
posts I’d state. Which is the initial time I frequented your website page and
up to now? I amazed together with the research you made to make this actual post amazing.
Wonderful job!

Reply

Hi there friends, its fantastic post regarding tutoringand entirely defined, keep it up all the time.

Reply

I’m unsure where you happen to be getting the information, but good topic.
I needs to spend some time learning considerably more or understanding more.
Many thanks for magnificent information I wanted this information for my mission.

Reply

Leave a comment

SUBSCRIBE TO NEWSLETTER

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Categories