README

Importing a CSV file is easy to code until real users attempt to import real data.

CSV Importer aims to handle validations, column mapping, import and reporting.

Rationale

Importing CSV files seems easy until you deal with real users uploading their real CSV file. You then have to deal with ASCII-8BIT formats, missing columns, empty rows, malformed headers, wild separators, etc. Reporting progress and errors to the end-user is also key for a good experience.

I went through this many times so I decided to build CSV Importer to save us the trouble.

CSV Importer provides:

• a DSL to define the mapping between CSV columns and your model
• good reporting to the end user
• support for wild encodings and CSV formats.

It is compatible with Eloquent 5.8+.

Usage;

Define your CSVImporter:

class ImportUserCSV extends CSVImporter
{
    public function __construct($stream, $quote_char, $model_class)
    {
        parent::__construct($stream, $quote_char, $model_class);
        $this->setColumn('email');
        $this->setColumn('first_name', ['required' => true, 'type' => 'string'], "/^first_name(\[.+\])?$/i");
        $this->setColumn('last_name', ['required' => true, 'type' => 'string'], ["/last.?name/i", "nom"]);
        $this->setColumn('address', ['required' => false], "/^address(\[.+\])?$/i", function($model, $value, $column) { $model->profile->address = $value; });
        $this->setIdenfifier('email'); # will update or create via email
    }
}

Run the import:

    $import = CSVFileImport::load($csvFilePath, User::class);
    $import->run();
    $import->getReport()->hasErrors() # => false
    $report = $import->getReport();

Installation

Add this line to your application's composer.json:

composer require 'forian/csv-importer:dev-master'

Usage

Create an Importer

Create a class and include extends from CSVImporter.

class ImportUserCSV extends CSVImporter
{
...
}

Define columns and their mapping

This is where the fun begins.

class ImportUserCSV extends CSVImporter
{
    public function __construct($stream, $quote_char, $model_class)
    {
        parent::__construct($stream, $quote_char, $model_class);
        $this->setColumn('email');
        .......
    }
}

This will map the column named email to the email attribute. By default, we downcase and strip the columns so it will work with a column spelled " EMail ".

Now, email could also be spelled "e-mail", or "mail", or even "courriel" (oh, canada). Let's give it a couple of aliases then:

  $this->setColumn('email', ['required' => false, 'type' => 'string'], ["/e-email/i", "mail", "courriel"]);

Nice, emails should be downcased though, so let's do this.

  $this->setColumn('email', ['required' => true], "/^email(\[.+\])?$/i", function($model, $value, $column) { $model->email = strtolower($value); });

Like very advanced stuff? We grant you access to the [column](See Column::class for more information) object itself which contains the column name – quite handy if you want to support arbitrary columns.

  # Pass options in brackets, example email[super_admin]
  $this->setColumn('email', ['required' => true], "/^email(\[.+\])?$/i", function($model, $value, $column) { $model->email = strtolower($value); $model->super_admin = 1 if $column->data == 'super_admin' });

Now, what if the user does not provide the email column? It's not worth running the import, we should just reject the CSV file right away. That's easy:

class ImportUserCSV extends CSVImporter
{
    public function __construct($stream, $quote_char, $model_class)
    {
        parent::__construct($stream, $quote_char, $model_class);
        $this->setColumn('email', 'required' => true');
        .......
    }
}

 $import = CSVFileImport::load($csvFilePath, User::class);
 $import->run();
 $import->getReport()->hasErrors() # => true
 $report = $import->getReport() # "The following columns are required: 'email'";

Update or Create

You often want to find-and-update-or-create when importing a CSV file. Just provide an identifier, and we'll do the hard work for you.

class ImportUserCSV extends CSVImporter
{
    public function __construct($stream, $quote_char, $model_class)
    {
        parent::__construct($stream, $quote_char, $model_class);
        $this->setColumn('email', 'required' => true');
        $this->setIdentifier('email');
    }
}

You can also define a composite identifier:

  # Update records with matching company_id AND employee_id
  $this->setIdentifier('company_id');
  $this->setIdentifier('employee_id');

Skip or Abort on error

By default, we skip invalid records and report errors back to the user. There are times where you want your import to be an all or nothing. The whenInvalid() option is here for you.

    $this-whenInvalid(function($row){
        $row->skip();
    });

afterBuild, afterSaveBlock, afterImportBlock callbacks

You can preset attributes (or perform any changes to the model) at configuration or runtime using afterBuild

    $this->afterBuild(function($row){
        $row...
    });
    
    $this->afterSaveBlock(function($row){
        $row...
    });
    
    $this-afterImportBlock(function($rows){
        $rows...
    });

# assuming `Auth::user()` is available

    $this->afterBuild(function($row){
        $row->user_id = Auth::user()->id
    });

The afterSaveBlock callback is run after each call to the method save no matter it fails or succeeds. It is quite handy to keep track of progress.

    $progressBar = new ProgressBar()
    
    $this->afterSaveBlock(function($row) use ($progressBar){
        $progressBar->increment();
    });

Skip import

You can skip the import of a model by calling skip in an afterBuild block:

    $this->afterBuild(function($row){
        $row->skip();
    });

Validate the header

On a web application, as soon as a CSV file is uploaded, you can check if it has the required columns. This is handy to fail early and provide the user with a meaningful error message right away.

    $import = CSVFileImport::load($csvFilePath, User::class);
    $import->validHeader? # => false
    $import->getReport() # => "The following columns are required: "email""

Run the import and provide feedback to the user

    $import = CSVFileImport::load($csvFilePath, User::class);
    $import->validHeader? # => false
    $import->getReport() # => "Import completed. 4 created, 2 updated, 1 failed to update"