How to import CSV data to local database in laravel

08. September 2016 Intermediate, Laravel 2
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, firstname, lastname, 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, firstname, lastname, 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,

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.

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 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,

Now model is created, we can interact with database.

Coming to the while loop above which has all the data in the $data array.

We use the model we just created and store the values in the database. Now our values are successfully stored in database.

In next step we show them in laravel view.

Show the data from database :

Here comes the final step, showing data from 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,

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,

 

Working Demo      Project on Github

CSV import to database - justlaravel.com
CSV import to database – justlaravel.com

 



2 thoughts on “How to import CSV data to local database in laravel”

Leave a Reply