In CSV file you can store data in comma-separated string format.
Sometimes require to import existing CSV file data to MySQL database.
For this, you can either directly read the file or upload and then read the file for insert data.
In this tutorial, I show how you can upload and import CSV file data in your MySQL database in Laravel 7 without any package.
Contents
1. Table structure
I am using users table in the example.
CREATE TABLE `users` ( `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, `username` varchar(80) NOT NULL, `name` varchar(80) NOT NULL, `gender` varchar(10) NOT NULL, `email` varchar(80) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
2. Database Configuration
Open .env file.
Specify the host, database name, username, and password.
DB_CONNECTION=mysql DB_HOST=127.0.0.1 DB_PORT=3306 DB_DATABASE=tutorial_laravel DB_USERNAME=root DB_PASSWORD=
3. Model
Create a Page Model.
php artisan make:model Page
To access DB need to add use Illuminate\Support\Facades\DB;.
Create a single method –
- insertData – This method takes an Array type parameter.
Check username already exists in users table or not. If not then pass $data in insert() method to insert a record to the users table.
Completed Code
<?php
namespace App;
use Illuminate\Support\Facades\DB;
use Illuminate\Database\Eloquent\Model;
class Page extends Model {
public static function insertData($data){
$value=DB::table('users')->where('username', $data['username'])->get();
if($value->count() == 0){
DB::table('users')->insert($data);
}
}
}
4. Controller
Create a PagesController Controller.
php artisan make:controller PagesController
Add use App\Page to access Page Model and use Session to access Session in the controller.
Create two methods –
- index – Load
indexview. - uploadFile – This method call on
<form >submit.
Upload file –
Read file element using $request->file('file') and store in $file.
Get file details and specify valid extension "csv" in $valid_extension Array and assign max file size to $maxFileSize in Bytes.
If has valid extension and file size then specify upload location in $location='uploads'.
Use move() method to store file.
NOTE – Uploaded file store in
public/uploads/directory.
Import Data –
Read the uploaded file from the public directory using public_path().
Now read the file rows and store in $importData_arr Array.
After reading all rows from CSV file loop on $importData_arr and prepare array according to MySQL table structure for inserting records.
Pass the $insertData in Page::insertData() for insert.
Completed Code
<?php
namespace App\Http\Controllers;
use Session;
use Illuminate\Http\Request;
use App\Page;
class PagesController extends Controller{
public function index(){
return view('index');
}
public function uploadFile(Request $request){
if ($request->input('submit') != null ){
$file = $request->file('file');
// File Details
$filename = $file->getClientOriginalName();
$extension = $file->getClientOriginalExtension();
$tempPath = $file->getRealPath();
$fileSize = $file->getSize();
$mimeType = $file->getMimeType();
// Valid File Extensions
$valid_extension = array("csv");
// 2MB in Bytes
$maxFileSize = 2097152;
// Check file extension
if(in_array(strtolower($extension),$valid_extension)){
// Check file size
if($fileSize <= $maxFileSize){
// File upload location
$location = 'uploads';
// Upload file
$file->move($location,$filename);
// Import CSV to Database
$filepath = public_path($location."/".$filename);
// Reading file
$file = fopen($filepath,"r");
$importData_arr = array();
$i = 0;
while (($filedata = fgetcsv($file, 1000, ",")) !== FALSE) {
$num = count($filedata );
// Skip first row (Remove below comment if you want to skip the first row)
/*if($i == 0){
$i++;
continue;
}*/
for ($c=0; $c < $num; $c++) {
$importData_arr[$i][] = $filedata [$c];
}
$i++;
}
fclose($file);
// Insert to MySQL database
foreach($importData_arr as $importData){
$insertData = array(
"username"=>$importData[1],
"name"=>$importData[2],
"gender"=>$importData[3],
"email"=>$importData[4]);
Page::insertData($insertData);
}
Session::flash('message','Import Successful.');
}else{
Session::flash('message','File too large. File must be less than 2MB.');
}
}else{
Session::flash('message','Invalid File Extension.');
}
}
// Redirect to index
return redirect()->action('PagesController@index');
}
}
5. Route
Open routes/web.php file.
Here, define two routes –
- /
- /uploadFile – This is post type route which uses on
<form >action.
Completed Code
<?php
Route::get('/', 'PagesController@index'); // localhost:8000/
Route::post('/uploadFile', 'PagesController@uploadFile');
6. View
Create a new index.blade.php file in resources/views/ directory.
If Session message variable if it is set then display value in <p>.
Create a <form > and pass '/uploadFile' in action attribute.
In <form > add {{ csrf_field() }}, a file element and a submit button.
Completed Code
<!doctype html>
<html>
<head>
<title>Import CSV Data to MySQL database with Laravel</title>
</head>
<body>
<!-- Message -->
@if(Session::has('message'))
<p >{{ Session::get('message') }}</p>
@endif
<!-- Form -->
<form method='post' action='/uploadFile' enctype='multipart/form-data' >
{{ csrf_field() }}
<input type='file' name='file' >
<input type='submit' name='submit' value='Import'>
</form>
</body>
</html>
7. Output
8. Conclusion
If in your CSV file the first row stored column names then you can skip the 0 index while reading rows from CSV file from the controller.
If you found this tutorial helpful then don't forget to share.