Import CSV Data to MySQL Database with Laravel ~ Example

5

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 with Laravel.

Contents

  1. Table structure
  2. Database Configuration
  3. Model
  4. Controller
  5. Route
  6. View

1. Table structure

I am using users table in the example.


2. Database Configuration

Open .env file.

Specify the host, database name, username, and password.


3. Model

Create a Page Model.

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


4. Controller

Create a PagesController Controller.

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


5. Route

Open routes/web.php file.

Here, define two routes –

  • /
  • /uploadFile – This is post type route which uses on <form > action.

Completed Code


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

Leave A Reply

Your email address will not be published.