Import CSV Data to MySQL Database with Laravel 7

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
2. Database Configuration
3. Model
4. Controller
5. Route
6. View
7. Output
8. Conclusion

---

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

View 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.