CakePHP 4 Database Seeding From CSV File Tutorial

Inside this article we will see the concept of CakePHP 4 Database Seeding from CSV file. CakePHP 4 database seeding from CSV file is technique to dump test data into tables in bulk.

This tutorial will be super easy to understand and it’s steps are easier to implement in your code as well. Database seeding is the process in which we feed test data to tables. We can insert data either using Faker library, manual data or means of some more data sources like CSV, JSON.

CakePHP 4 Database Seeding from JSON File Tutorial, Click here.

In this tutorial we will use CSV file to seed data into database table using CakePHP 4.

Learn More –

• CakePHP 4 Custom Primary Key For Table Using Migration
• CakePHP 4 Custom Validation Rule For Form Inputs
• CakePHP 4 Database Seeding From JSON File Tutorial
• CakePHP 4 Database Seeding Using Faker Library

Let’s get started.

CakePHP 4 Installation

To create a CakePHP project, run this command into your shell or terminal. Make sure composer should be installed in your system.

$ composer create-project --prefer-dist cakephp/app:~4.0 mycakephp

Above command will creates a project with the name called mycakephp.

Database Connection

Open app_local.php file from /config folder. Search for Datasources. Go to default array of it.

You can add your connection details here to connect with your database. It will be like this –

//...

'Datasources' => [
        'default' => [
            'host' => 'localhost',
            /*
             * CakePHP will use the default DB port based on the driver selected
             * MySQL on MAMP uses port 8889, MAMP users will want to uncomment
             * the following line and set the port accordingly
             */
            //'port' => 'non_standard_port_number',

            'username' => 'root',
            'password' => 'sample@123',

            'database' => 'mydatabase',
            /*
             * If not using the default 'public' schema with the PostgreSQL driver
             * set it here.
             */
            //'schema' => 'myapp',

            /*
             * You can use a DSN string to set the entire configuration
             */
            'url' => env('DATABASE_URL', null),
        ],
  
     //...

//...

You can pass host, username, password and database.

Successfully, you are now connected with the database.

Create Migration

Let’s create a migration file for “countries” table.

Open project into terminal and run this command

$ bin/cake bake migration CountriesTable

This command will create a migration file inside /config/Migrations folder. File name will be prefixed with the timestamp value and look like 20220217021429_CountriesTable.php

Open migration file and write this code into it to create “countries“ table schema.

<?php

declare(strict_types=1);

use Migrations\AbstractMigration;

class CountriesTable extends AbstractMigration
{
    /**
     * Change Method.
     *
     * More information on this method is available here:
     * https://book.cakephp.org/phinx/0/en/migrations.html#the-change-method
     * @return void
     */
    public function change()
    {
        $table = $this->table('countries');

        $table->addColumn('sortname', 'string', [
            'limit' => 10,
            'null' => false,
        ]);

        $table->addColumn('name', 'string', [
            'limit' => 120,
            'null' => false,
        ]);

        $table->addColumn('phonecode', 'string', [
            'limit' => 100,
            'null' => false,
        ]);

        $table->create();
    }
}

Run Migration

Open terminal and run this command to create table schema.

$ bin/cake migrations migrate

It will create countries table in database.

Prepare CSV Data File

As we have table countries in which columns as id, name, sortname, phonecode.

We will take a CSV file in which the columns will be ID, Sortname, Name & Phonecode. Rest we need data into it.

When you will download the given file it will be in .txt format. Rename file into .csv and place this countries.csv file inside /webroot/data folder.

Open countries.csv file, you should see like this OR may be in comma separated values.

Create Data Seeder

To create data seeder, run this command to terminal.

$ bin/cake bake seed Country

It will create a file CountrySeed.php inside /config/Seeds folder.

Open CountrySeed.php and write this code into it.

<?php

declare(strict_types=1);

use Migrations\AbstractSeed;

class CountrySeed extends AbstractSeed
{
    public function run()
    {
        $data = [];

        $csvFile = fopen(WWW_ROOT . "data/countries.csv", "r");

        $firstline = true;

        while (($row = fgetcsv($csvFile, 2000, ",")) !== FALSE) {

            if (!$firstline) {
                $data[] = [
                    "sortname" => $row['1'],
                    "name" => $row['2'],
                    "phonecode" => $row['3']
                ];
            }
            $firstline = false;
        }

        fclose($csvFile);

        $table = $this->table('countries');
        $table->insert($data)->save();
    }
}

WWW_ROOT is CakePHP constant which returns the path up to /webroot folder.

Inside above code we are inserting all rows of countries (json) into table.

Run Seeder

Back to terminal and run this command to seed data into table.

$ bin/cake migrations seed --seed CountrySeed

This command will run the CountrySeed seeder from Seeds folder.

We hope this article helped you to learn about CakePHP 4 Database Seeding from CSV File Tutorial in a very detailed way.

Online Web Tutor invites you to try Skillshike! Learn CakePHP, Laravel, CodeIgniter, Node Js, MySQL, Authentication, RESTful Web Services, etc into a depth level. Master the Coding Skills to Become an Expert in PHP Web Development. So, Search your favourite course and enroll now.

If you liked this article, then please subscribe to our YouTube Channel for PHP & it’s framework, WordPress, Node Js video tutorials. You can also find us on Twitter and Facebook.