Sometimes we have the raw data in any format which can be inserted directly into the database. In other cases, data inserting manually one by one into the database is a time taking process. If you are creating any inventory or shopping cart application in which you have to insert product details. So the single-single product entry into the database from the dashboard is tedious work. Why don’t we try data importing directly? Yes, this will be a simple task through the data importing through a CSV sheet. Today, I am here with a new tutorial on Laravel 6 for importing CSV File data into MySQL Database.
Prerequisites
For creating a Laravel 6 project, you will require the following tools-
- PHP version >=7.2.5
- MySQL (version > 5)
- Apache/Nginx Server
- VS Code Editor
Now, let us create a project to import CSV file data in Laravel 6.
Find Nearest Location By Latitude and Longitude in Laravel 6
Create New Laravel 6 Project For Importing CSV File
For creating a new project in Laravel 6, just open the terminal or command prompt and hit the below command.
composer create-project --prefer-dist laravel/laravel import-csvIt will create a new folder and inside the folder will install the required libraries. So, be patient while Laravel 6 installation completes.
After creating the project, let’s create a database in which we’ll import the CSV File data. I am going to import some leads into the database this is just a demonstration purpose. So, I will create a data sheet for the Leads. The sheet format will be CSV. After importing the data will be inserted into the database table. So, let’s start step by step.
Laravel 6 Custom Login and Registration with Session
Create a New Database in MySQL
I am using the MySQL command prompt for creating the new Database. You can use phpMyAdmin or SQL WorkBench.
CREATE DATABASE laravel6_csv_import;Now, you have the database created with the above command. So, let’s configure it inside the Laravel project.
DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=laravel6_csv_import
DB_USERNAME=root
DB_PASSWORD=rootSo, here our project is connected with the MySQL database. Hence, let’s move to the next steps. Now, we will create a Model and migration.
How to Implement Google Autocomplete Address in Laravel 6
Create a Model and Migration
In this project, I want to import some leads inside the database using a CSV file. So I will have one model and one table for Lead that’s it.
php artisan make:model Lead --migrationThe above command will create a Model and a database migration file.
Move to the database->migrations and open the create_leads_table.php. Here, we’ll define the schema of the table.
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
class CreateLeadsTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('leads', function (Blueprint $table) {
$table->bigIncrements('id');
$table->string('first_name');
$table->string('last_name');
$table->string('full_name');
$table->string('email');
$table->string('phone');
$table->string('alternate_phone');
$table->string('address');
$table->string('city');
$table->string('requirement');
$table->timestamps();
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::dropIfExists('leads');
}
}
Here, the schema for the leads table has been defined. Now, we’ll need to migrate the tables. After migration, all the specified tables will be created with their fields.
RESTful APIs in Laravel 6 with Passport Authentication
Migrate Tables in Laravel 6
For migrating all the tables into the database, just hit the below artisan command.
php artisan migrate
After successful migration, we have the tables inside the database.
Add Mass Assignment in the Model
In this step, we will specify the fields which are going to insert through the CSV file. So, here we will put the field name as those are defined inside the leads table.
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class Lead extends Model
{
protected $fillable = [
"first_name",
"last_name",
"full_name",
"email",
"phone",
"alternate_phone",
"address",
"city",
"requirement"
];
}
After the above steps, we will create a Controller in which we will write the function for reading the CSV file. Then, we will extract the column’s value for inserting it into the table. So, let’s go ahead.
How to Send Email in Laravel 6 Via Gmail Using SMTP
Create a Controller
We will create a controller for importing the CSV file data. So just hit the below command for creating a controller inside the project.
php artisan make:controller LeadControllerSo, just wait while the controller is creating.
After creating the controller, we will need to create the functions. So, here I have created two functions. Just add these functions to your controller.
<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\Lead;
class LeadController extends Controller
{
public function index() {
return view('import');
}
// ------------- [ Import Leads ] ----------------
public function importLeads(Request $request) {
$data = array();
$lead_id = "";
$first_name = "";
$last_name = "";
// file validation
$request->validate([
"csv_file" => "required",
]);
$file = $request->file("csv_file");
$csvData = file_get_contents($file);
$rows = array_map("str_getcsv", explode("\n", $csvData));
$header = array_shift($rows);
foreach ($rows as $row) {
if (isset($row[0])) {
if ($row[0] != "") {
$row = array_combine($header, $row);
$full_name = $row["full_name"];
$full_name_array = explode(" ", $full_name);
$first_name = $full_name_array[0];
if (isset($full_name_array[1])) {
$last_name = $full_name_array[1];
}
// master lead data
$leadData = array(
"first_name" => $first_name,
"last_name" => $last_name,
"full_name" => $row["full_name"],
"email" => $row["email"],
"phone" => str_replace("'", "", $row["phone_no"]),
"alternate_phone" => $row["alternate_phone_no"],
"address" => $row["address"],
"city" => $row["city"],
"requirement" => $row["requirements"],
);
// ----------- check if lead already exists ----------------
$checkLead = Lead::where("email", "=", $row["email"])->first();
if (!is_null($checkLead)) {
$updateLead = Lead::where("email", "=", $row["email"])->update($leadData);
if($updateLead == true) {
$data["status"] = "failed";
$data["message"] = "Leads updated successfully";
}
}
else {
$lead = Lead::create($leadData);
if(!is_null($lead)) {
$data["status"] = "success";
$data["message"] = "Leads imported successfully";
}
}
}
}
}
return back()->with($data["status"], $data["message"]);
}
}
In the above controller file, the first function is loading the view. Then in the next function, I have read the CSV file that will be chosen from the view (form). Then after parsing the file, I have extracted the data from the CSV column. Finally, with the help of a loop iteration, one by one data will be inserted into the database.
Drag and Drop File Upload in Laravel 6 Using Dropzone js
Create a View
After creating the above function, let’s create a view inside the Resources->view. Then name it import.blade.php. Now, paste the below snippet there.
<!doctype html>
<html lang="en">
<head>
<title>Import CSV File Data | Programming Fields</title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">
</head>
<body>
<div class="container mt-5">
<div class="row">
<div class="col-xl-6 col-md-6 col-12 m-auto">
@if(Session::has("success"))
<div class="alert alert-success <div class="alert alert-primary alert-dismissible fade show" role="alert">
<button type="button" class="close" data-dismiss="alert" aria-label="Close">
<span aria-hidden="true">×</span>
<span class="sr-only">Close</span>
</button>
<strong>Success!</strong> {{Session::get("success")}}
</div>
@elseif(Session::has("failed"))
@endif
<form method="post" action="{{url('parse-csv')}}">
@csrf
<div class="card shadow">
<div class="card-header">
<h4> Import CSV File Data </h4>
</div>
<div class="card-body">
<div class="form-group">
<input type="file" name="file" class="form-control">
</div>
</div>
<div class="card-footer">
<button type="submit" class="btn btn-success" name="submit">Import Data </button>
</div>
</div>
</form>
</div>
</div>
</div>
<script src="https://code.jquery.com/jquery-3.3.1.slim.min.js" integrity="sha384-q8i/X+965DzO0rT7abK41JStQIAqVgRVzpbzo5smXKp4YfRvH+8abtTE1Pi6jizo" crossorigin="anonymous"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.7/umd/popper.min.js" integrity="sha384-UO2eT0CpHqdSJQ6hJty5KVphtPhzWj9WO1clHTMGa3JDZwrnQq4sF86dIHNDz0W1" crossorigin="anonymous"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js" integrity="sha384-JjSmVgyd0p3pXB1rRibZUAYoIIy6OrQ6VrjIEaFf/nJGzIxFDsf4x0xIM+B07jRM" crossorigin="anonymous"></script>
</body>
</html>
For executing the functions of the controller, we will need to create two routes. Hence, we will create routes.
Create Routes
For creating the routes for a web application just navigate to the routes/web.php file.
Route::get('import-leads', 'LeadController@index');
Route::post("parse-csv", "LeadController@importLeads");After creating the routes, just save and run your project. Now, hit the above route (URL) that is
http://localhost:8000/import-leadsYou will get the result like this. You will have a form for selecting the CSV file. Here is the sample of the data sheet.
Now, just choose the CSV file that you have created for importing the data.
Now, click to button and wait for the response.
As a result, you can the response message as a success. It means the CSV file data has been imported successfully. You can check the table inside the database.
If the sheet data has been imported and you will try to import it again then it will update the same data. That means it will not insert any duplicate data which have been imported already.
Create a CRUD Application in Laravel 7 For Beginners
Conclusion
Hence through this post, we have learned to import the data using the CSV file in Laravel 6. This is a good approach if you have the large data and you don’t want to insert manually one by one. This will save precious time and in one shot you can import entire data from the sheet.
Leave a Reply