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
index
view. - 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.
Great example. Let’s just say we have an unknown number of columns in the csv and we do not know the names of each column (i.e. each user submit different column names in their csv). How would we anticipate this in laravel?
Good question
Looks good, but why not use a migration to create the table, rather than a MySQL statement?
Hi Nathan,
I will update the tutorial with migration and Eloquent soon.
how do we retrieve each informations from the database by each id(email) where user login and get its own details and reply it like in email format
Thanks in advance this is helpfuly
Love it! Thank you!
You’re welcome.
I want generate a csv file of form data when form submit. It will be save in storage. Then get the link of that csv file. Which is send to email as a attachement and then user download it from that link. Using full laravel standard.
Please give me solution ASAP.
Thanks in advance
I am trying this out and have this error message:
Undefined offset: 4
I created my own tabled called Hours. It has a column that references the user_id in the users table.
Schema::create(‘hours’, function (Blueprint $table) {
$table->bigIncrements(‘id’);
$table->unsignedBigInteger(’employee_num’);
$table->date(‘date’);
$table->time(‘in’);
$table->time(‘out’);
$table->timestamps();
$table->foreign(’employee_num’)->references(‘id’)->on(‘users’)->onDelete(‘cascade’);
});
$insertData = array(
“employee_num” => $importData[1],
“date” => $importData[2],
“in” => $importData[3],
“out” => $importData[4],
);
start at index zero, [0], rather than [1].
I’m gettinf an error at –>. Page::insertData($insertData);
Thanks a lot!! You saved my day. 🙂
You’re welcome.
how to download data in csv from sql?