This tutorial will help you have a basic understanding of how to import data from a csv or excel file into your database using Maatwebsite package.
Step 1. Download laravel8
composer create-project laravel/laravel laravel-file-importer or composer create-project laravel/laravel=8 laravel-file-importer—prefer-dist
Step 2. Execute command to enter project directory
cd laravel-file-importer
Step 3. Establish connection to database by configuring .env file db_connection setting
DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=laravel
DB_USERNAME=root
DB_PASSWORD=
Step 4. install the package
composer require maatwebsite/excel
Step 5. Register plugin services. Place the follow lines of code in config/app.php file
//place this line within providers array below "Package Service Providers..." comment
Maatwebsite\Excel\ExcelServiceProvider::class,
//place this line within aliases array
'Excel' => Maatwebsite\Excel\Facades\Excel::class,
Step 6. publish the config by executing command
php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider"
Population of the database will be done through running a seeder. The next few steps will show how this was done.
Step 7. Create migration for address table.
php artisan make:migration create_addresses_table
This is how it looks like in the database/migrations/2021_05_04_122835_create_addresses_table.php file.
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
class CreateAddressesTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('addresses', function (Blueprint $table) {
$table->id();
$table->string('address');
$table->string('city');
$table->string('postal_code')->nullable();
$table->string('state');
$table->timestamps();
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::dropIfExists('addresses');
}
}
Step 8. Execute command to create table in database
php artisan migrate
Step 9. Create import class by executing command
php artisan make:import AddressImport --model=Address
This is how it looks like in the app/Import/AddressImport.php file.
namespace App\Imports;
use App\Models\Address;
use Illuminate\Database\Eloquent\Model;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
class AddressImport implements ToModel, WithHeadingRow
{
/**
* @param array $row
*
* @return Model|null
*/
public function model(array $row)
{
return new Address([
'address' => $row['address'],
'city' => $row['city'],
'postal_code' => $row['postal_code'],
'state' => $row['state'],
]);
}
}
The WithHeadingRow interface let you collect the data from each column by using the headers within the file.
For instance, $row['address'] is the package using the header "Address" to get all the data from that column of the file.
Make sure to replace all uppercase letters of the header name to lowercase letters and gaps in the name to underscores.
For example the header name "Postal Code" becomes "postal_code"
Step 10. Create Address Controller
php artisan make:controller AddressController
This is how it looks like in the app/Http/Controllers/AddressController.php file
namespace App\Http\Controllers;
use App\Imports\AddressImport;
use Maatwebsite\Excel\Facades\Excel;
class AddressController extends Controller
{
public function importAddressData($file)
{
Excel::import(new AddressImport(), $file);
}
}
Step 11. Place excel or csv file to be used for testing in storage folder
The path should look like this storage/address.csv
Step 12. Create address seeder in seeders folder
This is how it looks like in the database/seeders/AddressTableSeeder.php file.
namespace Database\Seeders;
use App\Http\Controllers\AddressController;
use Illuminate\Database\Seeder;
class ImportAddressTableSeeder extends Seeder
{
/**
* Run the database seeds.
*
* @return void
*/
public function run()
{
$file = storage_path('addresses.csv');
$addressImport = new AddressController();
$addressImport->importAddressData($file);
}
}
Step 13. Execute seeder command
php artisan db:seed --class=ImportAddressTableSeeder
Now if you check your database you should see data in addresses table
If you wish to download this tutorial go to https://github.com/wecode101/csv_and_excel_importer_laravel_8