CakePHP 4.x shell script to insert CSV data in database

We will create a cakephp command or shell script to insert data from CSV file. For this article we will follow cakephp version 4.x

To create a Shell or Command script, we will give a bake command for generate a command file for us. Let’s gives the command like below

cake bake command Excel

After executed this command, a Command folder will be create in src directory.
You will get a file with name ExcelCommand.php in command directory.

for more see cakephp documentation creating command

If you open ExcelCommand.php file you will get some php code like below which has auto generated

<?php
declare(strict_types=1);
namespace App\Command;

use Cake\Command\Command;
use Cake\Console\Arguments;
use Cake\Console\ConsoleIo;
use Cake\Console\ConsoleOptionParser;

class ExCommand extends Command
{
   public function buildOptionParser(ConsoleOptionParser $parser): ConsoleOptionParser
   {
          $parser = parent::buildOptionParser($parser);
          return $parser;
   }

   public function execute(Arguments $args, ConsoleIo $io)
   {
   }
}

We will start our code in execute method !
Example we have a csv file in directory  webroot/files location.

Assume file name is user_phone.csv

csv file looking like below , here first field is uid 2nd filed is user phone number.

"455917","88016293384"
"455916","88018670911"
"455915","88018277648"
...
...

Our steps will be

• Open CSV file using PHP SplFileObject.
• Read CSV
• Insert in database.

For open CSV file we will use PHP SplFileObject. CakePHP has a file and folder class. But it has deprecated !

So we can use existing PHP  SplFileObject.

1st question how we can use existing PHP class  ? It’s very simple we just need a back slash (\). like below example

Here,

Root = project root directory.
Using SplFileObject we have already opened user_phone.csv file from location \webroot\files folder.

Now we can read CSV file using setFlags method like below example

Now for insert data in database we have to load our model. So, we will use LocatorAwareTrait for load our model

we will use it in our class like

use Cake\ORM\Locator\LocatorAwareTrait;

Then we will use our Trait class In our ExcelCommand class like below example

class ExcelCommand extends Command
{
    use LocatorAwareTrait;
    ...

So, Our execute file will be like below

public function execute(Arguments $args, ConsoleIo $io)
{
      $phone = $this->getTableLocator()->get('UserTels');
      $io->out("Starting.....");
      $file = new \SplFileObject(ROOT.'\webroot\files\user_phone.csv');
      $file->setFlags(\SplFileObject::READ_CSV);

      $i = 0;
      foreach ($file as $key => $row) {
          $i++;
          $io->out($i);
          list($id,$tel) = $row; 
          $tel = $phone->newEmptyEntity();
          $tel->uid = $id;
          $tel->phone = $tel;
          $phone->save($tel);
       }
}

Now Command is ready, We just need to give a command to save all data in our user_tels table. Command will be look like below

\bin>cake excel

Are you seeing counting in cmd ? SO, we have executed !