Version 5 supported

Import CSV data

Introduction

CSV import can be easily achieved through PHP's built-in fgetcsv() method, but this method doesn't know anything about your datamodel. In Silverstripe CMS, this can be handled through the a specialized CSV importer class that can be customised to fit your data.

The CsvBulkLoader class

The CsvBulkLoader class facilitate complex CSV-imports by defining column-mappings and custom converters. It uses PHP's built-in fgetcsv() function to process CSV input, and accepts a file handle as an input.

Feature overview:

  • Custom column mapping
  • Auto-detection of CSV-header rows
  • Duplicate detection based on custom criteria
  • Automatic generation of relations based on one or more columns in the CSV-Data
  • Definition of custom import methods (e.g. for date conversion or combining multiple columns)
  • Optional deletion of existing records if they're not present in the CSV-file
  • Results grouped by "imported", "updated" and "deleted"

Usage

You can use the CsvBulkLoader without subclassing or other customizations, if the column names in your CSV file match $db properties in your dataobject. For example a simple import for the Member class could have this data in a file:

FirstName,LastName,Email
Donald,Duck,donald@disney.com
Daisy,Duck,daisy@disney.com

The loader would be triggered through the load() method:

use SilverStripe\Dev\CsvBulkLoader;

$loader = CsvBulkLoader::create('Member');
$result = $loader->load('<my-file-path>');

By the way, you can import Member and Group data through https://www.example.com/admin/security interface out of the box.

Permission checks

CsvBulkLoader does not respect permissions by default. If you want the permissions of the current user to be respected (i.e. if the bulk loader is being used as part of user interaction), you will need to pass true to the CsvBulkLoader::setCheckPermissions() method.

Import through ModelAdmin

The simplest way to use CsvBulkLoader is through a ModelAdmin interface - you get a secured upload form out of the box.

namespace App\Admin;

use App\Model\Player;
use SilverStripe\Admin\ModelAdmin;
use SilverStripe\Dev\CsvBulkLoader;

class PlayerAdmin extends ModelAdmin
{
    private static $managed_models = [
      Player::class,
    ];

    private static $model_importers = [
      Player::class => CsvBulkLoader::class,
    ];

    private static $url_segment = 'players';
}

The new admin interface will be available under https://www.example.com/admin/players, the import form is located below the search form on the left.

Import through a custom controller

You can have more customised logic and interface feedback through a custom controller. Let's create a simple upload form (which is used for MyDataObject instances). You'll need to add a route to your controller to make it accessible via URL (see Routing).

Don't forget to perform permission checks if the data is provided by users.

namespace App\Control;

use App\Model\MyDataObject;
use SilverStripe\Control\Controller;
use SilverStripe\Dev\CsvBulkLoader;
use SilverStripe\Forms\FieldList;
use SilverStripe\Forms\FileField;
use SilverStripe\Forms\Form;
use SilverStripe\Forms\FormAction;

class MyController extends Controller
{
    private static $url_segment = 'my_controller';

    private static $allowed_actions = [
        'getForm',
    ];

    private static $url_handlers = [
        'Form' => 'getForm',
    ];

    protected $template = 'BlankPage';

    public function getForm()
    {
        $form = Form::create(
            $this,
            'Form',
            FieldList::create(
                FileField::create('CsvFile', false)
            ),
            FieldList::create(
                FormAction::create('doUpload', 'Upload')
            )
        );
        return $form;
    }

    public function doUpload($data, $form)
    {
        $loader = CsvBulkLoader::create(MyDataObject::class);
        $results = $loader->load($_FILES['CsvFile']['tmp_name']);
        $messages = [];
        if ($results->CreatedCount()) {
            $messages[] = sprintf('Imported %d items', $results->CreatedCount());
        }
        if ($results->UpdatedCount()) {
            $messages[] = sprintf('Updated %d items', $results->UpdatedCount());
        }
        if ($results->DeletedCount()) {
            $messages[] = sprintf('Deleted %d items', $results->DeletedCount());
        }
        if (!$messages) {
            $messages[] = 'No changes';
        }
        $form->sessionMessage(implode(', ', $messages), 'good');

        return $this->redirectBack();
    }
}

Column mapping and relation import

We're going to use our knowledge from the previous example to import a more sophisticated CSV file.

Sample CSV Content

"Number","Name","Birthday","Team"
11,"John Doe",1982-05-12,"FC Bayern"
12,"Jane Johnson", 1982-05-12,"FC Bayern"
13,"Jimmy Dole",,"Schalke 04"

Datamodel for Player

namespace App\Model;

use SilverStripe\ORM\DataObject;

class Player extends DataObject
{
    private static $db = [
      'PlayerNumber' => 'Int',
      'FirstName' => 'Text',
      'LastName' => 'Text',
      'Birthday' => 'Date',
    ];

    private static $has_one = [
      'Team' => FootballTeam::class,
    ];
}

Datamodel for FootballTeam:

namespace App\Model;

use SilverStripe\ORM\DataObject;

class FootballTeam extends DataObject
{
    private static $db = [
      'Title' => 'Text',
    ];

    private static $has_many = [
      'Players' => Player::class,
    ];
}

Sample implementation of a custom loader. Assumes a CSV-file in a certain format (see below).

  • Converts property names
  • Splits a combined "Name" fields from the CSV-data into FirstName and Lastname by a custom importer method
  • Avoids duplicate imports by a custom $duplicateChecks definition
  • Creates Team relations automatically based on the Gruppe column in the CSV data
namespace App\Admin;

use App\Model\FootballTeam;
use SilverStripe\Dev\CsvBulkLoader;

class PlayerCsvBulkLoader extends CsvBulkLoader
{
    public function __construct($objectClass)
    {
        $this->columnMap = [
            'Number' => 'PlayerNumber',
            'Name' => '->importFirstAndLastName',
            'Birthday' => 'Birthday',
            'Team' => 'Team.Title',
        ];

        $this->duplicateChecks = [
            'Number' => 'PlayerNumber',
        ];

        $this->relationCallbacks = [
            'Team.Title' => [
                'relationname' => 'Team',
                'callback' => 'getTeamByTitle',
            ],
        ];

        parent::construct($objectClass);
    }

    public static function importFirstAndLastName(&$obj, $val, $record)
    {
        $parts = explode(' ', $val);
        if (count($parts) != 2) {
            return false;
        }
        $obj->FirstName = $parts[0];
        $obj->LastName = $parts[1];
    }

    public static function getTeamByTitle(&$obj, $val, $record)
    {
        return FootballTeam::get()->filter('Title', $val)->First();
    }
}

Building off of the ModelAdmin example up top, use a custom loader instead of the default loader by adding it to $model_importers. In this example, CsvBulkLoader is replaced with PlayerCsvBulkLoader.

namespace App\Admin;

use SilverStripe\Admin\ModelAdmin;

class PlayerAdmin extends ModelAdmin
{
    private static $managed_models = [
      'Player',
    ];

    private static $model_importers = [
      'Player' => PlayerCsvBulkLoader::class,
    ];

    private static $url_segment = 'players';
}

Related

Edit on Github