How to do CSV ETL using Laravel 5 and PHP-ETL

I recently work on data analytics and is required to do a lot of ETL from OLTP to OLAP database. I found that this PHP-ETL package can be easily integrated into Laravel 5 application.

You may wonder what is ETL?

ETL is a type of data integration that refers to the three steps (extract, transform, load) used to blend data from multiple sources. It’s often used to build a data warehouse.

During this process, data is taken (extracted) from a source system, converted (transformed) into a format that can be analyzed, and stored (loaded) into a data warehouse or other system (source: Sas.com).

So how to do CSV ETL with Laravel 5?

#1 Run this composer command inside your Laravel 5 project.

composer require marquine/php-etl

#2 Create a migration class to create your table as required.

Assuming you don’t have any table yet, create the migration class. You can skip this if your table is already exists. Run command as follows:

php artisan make:migration create_table_contacts –create=contacts

For example, I have a list of contact persons in CSV file and I wanted to load this data into my DB as follows:

#3 Create a seeder class to read, transform, and load CSV data into the database.

php artisan make:seeder ContactsTableSeeder

In the seeder file, you need to:

• Locate the CSV file’s location
• Create a new ETL object
• Extract, transform, and load data into database