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

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

CSV import to database – justlaravel.com