How to import CSV data to local database in laravel

08. September 2016 Intermediate, Laravel 9
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,

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

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.

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,

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

 



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

  • 1
    Ashley on June 20, 2017 Reply

    im getting class Csvdata not found, any ideas?

  • 3
    Adam on June 25, 2017 Reply

    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 🙂

  • 4
    igor on August 11, 2017 Reply

    Thank you for sharing !

  • 5
    marcellopato on March 8, 2018 Reply

    How do I delete the first row automatically? Great job, by the way!

  • 6
    Aya on March 16, 2018 Reply

    Thanks for your effor 🙂

  • 7
    Jeroen van der Holst on April 4, 2018 Reply

    @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?

    • 8
      marcellopato on April 4, 2018 Reply

      I think I’ve solved using a parameter, but don’t recall which…

  • 9
    anakinvn0 on April 18, 2018 Reply

    how to solve the memory issue? I got the error “Allowed memory size of 134217728 bytes exhausted (tried to allocate 10489856 bytes)”. Thank you

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.