How to import CSV data to local database in laravel

Hello readers of justlaravel.com, am back with a new post, here we will see how to read data from a CSV file, parse it and store it in our local database and finally display them in our view.
While working with data we come across different data formats like XML, CSV, JSON, SQL, and others. CSV file, a comma separated value file is one the most common file type. Now we work with it, gets the data from the file and will store it database for our use.
The CSV file we work now contains headers like id, first name, last name, email, gender, it can be found here. So we need to create an appropriate database table for it to store that data.
Working Demo Project on Github
-
Step 1: Create a database table
-
Step 2: Read the CSV data
-
Step3: Store the data in database
-
Step 4: Show the data from database
Create a database table :
We now create a database table with fields id, first name, last name, email, and gender as the content in the CSV file has these data.
We use migrations to create a new table, in the project root, we run the following command,
php artisan make:migration create_csv_data_table
Now a new file in /database/migrations will be created, here in the up()
function we create all the fields we require,
public function up() { Schema::create ( 'csvData', function ($table) { $table->integer ( 'id' ); $table->string ( 'firstname' ); $table->string ( 'lastname' ); $table->string ( 'email' ); $table->string ( 'gender' ); } ); }
Now to migrate this to our local database, we run the following command,
php artisan migrate
The command creates a new table in our database. Before running this command one need to change the database details in .env
file about database name, password, host etc. for more info about migrations and database setup look previous tutorials on it.
Read the CSV data :
We generate a CSV file with random mock data using mockaroo.com, and place that file in /public directory of the app. Here the name of the CSV file is MOCK_DATA.csv.
We open the csv file using fopen() function and read the csv file using fgetcsv() function.
if (($handle = fopen ( public_path () . '/MOCK_DATA.csv', 'r' )) !== FALSE) { while ( ($data = fgetcsv ( $handle, 1000, ',' )) !== FALSE ) { //saving to db logic goes here } fclose ( $handle ); }
In the wile loop we read each line in the csv file,
$data[0] contains |
id value |
$data[1] contains |
firstname value |
$data[2] contains |
lastname value |
$data[3] contains |
email value |
$data[4] contains |
gender value |
So we have all the values we got to save it database we now save them.
Working Demo Project on Github
Store the data in the database :
To access the database we need a model, so we create one by running the following command,
php artisan make:model Csvdata
This will create a new file in /app with name Csvdata.php, in that file place the following code,
<?php namespace App; use Illuminate\Database\Eloquent\Model; class Csvdata extends Model { protected $table = 'csvData'; public $timestamps = false; }
Now the model is created, we can interact with the database.
Coming to the while loop above which has all the data in the $data array.
if (($handle = fopen ( public_path () . '/MOCK_DATA.csv', 'r' )) !== FALSE) { while ( ($data = fgetcsv ( $handle, 1000, ',' )) !== FALSE ) { $csv_data = new Csvdata (); $csv_data->id = $data [0]; $csv_data->firstname = $data [1]; $csv_data->lastname = $data [2]; $csv_data->email = $data [3]; $csv_data->gender = $data [4]; $csv_data->save (); } fclose ( $handle ); }
We use the model we just created and store the values in the database. Now our values are successfully stored in the database.
In next step, we show them in laravel view.
Show the data from the database :
Here comes the final step, showing data from the database, we have done this in many tutorials, so this is a very simple step.
In the previous step, the if loop which saves the data, here we get that data, so after that loop,
$finalData = $csv_data::all (); return view ( 'welcome' )->withData ( $finalData );
We get all that data and pass it to our view.
So in the view we create a table, and loop it to show all the data, in /resources/views/welcome.blade.php,
<table class="table"> <thead> <tr> <th>#</th> <th>Firstname</th> <th>Lastname</th> <th>Email</th> <th>Gender</th> </tr> </thead> <tbody> @foreach($data as $item) <tr> <td>{{$item->id}}</td> <td>{{$item->firstname}}</td> <td>{{$item->lastname}}</td> <td>{{$item->email}}</td> <td>{{$item->gender}}</td> </tr> @endforeach </tbody> </table>
Working Demo Project on Github

im getting class Csvdata not found, any ideas?
Make sure you have this line at the top `use App\Csvdata;`
You can go through the code at github: https://github.com/avinashn/ImportCSVdata-laravel
This is the only tutorial I’ve found that works, thanks! so how could I change it from public path to getRealPath or something so the use could upload it? I have to do this project to get my first junior developer job and importing a csv is the only thing i’m stuck on! Thanks 🙂
Thank you for sharing !
How do I delete the first row automatically? Great job, by the way!
Thanks for your effor 🙂
@marcellopato I have the same question, but I also have the problem where it only reads the first line. Maybe it has something to do with line end it doesnot get?
I think I’ve solved using a parameter, but don’t recall which…
how to solve the memory issue? I got the error “Allowed memory size of 134217728 bytes exhausted (tried to allocate 10489856 bytes)”. Thank you