data-import

Ddeboer Data Import library

This library has been renamed to PortPHP and will be deprecated. Please use PortPHP instead.

Introduction

This PHP library offers a way to read data from, and write data to, a range of file formats and media. Additionally, it includes tools to manipulate your data.

Features

• Read from and write to CSV files, Excel files, databases, and more.
• Convert between charsets, dates, strings and objects on the fly.
• Build reusable and extensible import workflows.
• Decoupled components that you can use on their own, such as a CSV and Excel reader and writer.
• Well-tested code.

Documentation

• Installation
• Usage
  • The workflow
  • The workflow result
  • Readers
    • ArrayReader
    • CsvReader
    • DbalReader
    • DoctrineReader
    • ExcelReader
    • One To Many Reader
    • Create a reader
  • Writers
    • ArrayWriter
    • CsvWriter
    • DoctrineWriter
    • PdoWriter
    • ExcelWriter
    • ConsoleTableWriter
    • ConsoleProgressWriter
    • CallbackWriter
    • AbstractStreamWriter
    • StreamMergeWriter
    • Create a writer
  • Filters
    • CallbackFilter
    • OffsetFilter
    • DateTimeThresholdFilter
    • ValidatorFilter
  • Converters
    • Item converters
      • MappingItemConverter
      • Create an item converter
      • CallbackItemConverter
    • Value converters
      • StringToDateTimeValueConverter
      • DateTimeToStringValueConverter
      • ObjectConverter
      • StringToObjectConverter
      • ArrayValueConverterMap
      • CallbackValueConverter
      • MappingValueConverter
  • Examples
    • Import CSV file and write to database
    • Export to CSV file
• Running the tests
• License

Installation

This library is available on Packagist. The recommended way to install it is through Composer:

$ composer require ddeboer/data-import:@stable

Then include Composer’s autoloader:

require_once 'vendor/autoload.php';

For integration with Symfony2 projects, the DdeboerDataImportBundle is available.

Usage

Broadly speaking, you can use this library in two ways:

• organize your import process around a workflow, or
• use one or more of the components on their own, such as readers, writers or converters.

The workflow

Each data import revolves around the workflow and takes place along the following lines:

1. Construct a reader.
2. Construct a workflow and pass the reader to it, optionally pass a logger as second argument. Add at least one writer to the workflow.
3. Optionally, add filters, item converters and value converters to the workflow.
4. Process the workflow. This will read the data from the reader, filter and convert the data, and write the output to each of the writers. The process method also returns a Result object which contains various information about the import.

In other words, the workflow acts as a mediator between a reader and one or more writers, filters and converters.

Optionally you can skip items on failure like this $workflow->setSkipItemOnFailure(true). Errors will be logged if you have passed a logger to the workflow constructor.

Schematically:

use Ddeboer\DataImport\Workflow;
use Ddeboer\DataImport\Reader;
use Ddeboer\DataImport\Writer;
use Ddeboer\DataImport\Filter;

$reader = new Reader\...;
$workflow = new Workflow($reader, $logger);
$result = $workflow
    ->addWriter(new Writer\...())
    ->addWriter(new Writer\...())
    ->addFilter(new Filter\CallbackFilter(...))
    ->setSkipItemOnFailure(true)
    ->process()
;

The workflow Result

The Workflow Result object exposes various methods which you can use to decide what to do after an import. The result will be an instance of Ddeboer\DataImport\Result. It is automatically created and populated by the Workflow. It will be returned to you after calling the process() method on the Workflow

The Result provides the following methods:

…

Example use cases:

• You want to send an e-mail with the results of the import
• You want to send a Text alert if a particular file failed
• You want to move an import file to a failed directory if there were errors
• You want to log how long imports are taking

Readers

Readers read data that will be imported by iterating over it. This library includes a handful of readers. Additionally, you can easily implement your own.

You can use readers on their own, or construct a workflow from them:

$workflow = new Workflow($reader);

ArrayReader

Reads arrays. Most useful for testing your workflow.

CsvReader

Reads CSV files, and is optimized to use as little memory as possible.

use Ddeboer\DataImport\Reader\CsvReader;

$file = new \SplFileObject('/path/to/csv_file.csv');
$reader = new CsvReader($file);

Optionally construct with different delimiter, enclosure and/or escape character:

$reader = new CsvReader($file, ';');

Then iterate over the CSV file:

foreach ($reader as $row) {
    // $row will be an array containing the comma-separated elements of the line:
    // array(
    //   0 => 'James',
    //   1 => 'Bond'
    //   etc...
    // )
}

Column headers

If one of your rows contains column headers, you can read them to make the rows associative arrays:

$reader->setHeaderRowNumber(0);

foreach ($reader as $row) {
    // $row will now be an associative array:
    // array(
    //   'firstName' => 'James',
    //   'lastName'  => 'Bond'
    //   etc...
    // )
}

Strict mode

The CSV reader operates in strict mode by default. If the reader encounters a row where the number of values differs from the number of column headers, an error is logged and the row is skipped. Retrieve the errors with getErrors().

To disable strict mode, set $reader->setStrict(false) after you instantiate the reader.

Disabling strict mode means:

1. Any rows that contain fewer values than the column headers are simply padded with null values.
2. Any additional values in a row that contain more values than the column headers are ignored.

Examples where this is useful:

• Outlook 2010: which omits trailing blank values
• Google Contacts: which exports more values than there are column headers

Duplicate headers

Sometimes a CSV file contains duplicate column headers, for instance:

By default, a DuplicateHeadersException will be thrown if you call setHeaderRowNumber(0) on this file. You can handle duplicate columns in one of three ways:

• call setColumnHeaders(['id', 'details', 'details_2']) to specify your own headers
• call setHeaderRowNumber with the CsvReader::DUPLICATE_HEADERS_INCREMENT flag to generate incremented headers; in this case: id, details and details1
• call setHeaderRowNumber with the CsvReader::DUPLICATE_HEADERS_MERGE flag to merge duplicate values into arrays; in this case, the first row’s values will become: [ 'id' => 1, 'details' => [ 'bla', 'more bla' ] ].

DbalReader

Reads data through Doctrine’s DBAL. Your project should include Doctrine’s DBAL package:

$ composer require doctrine/dbal

use Ddeboer\DataImport\Reader\DbalReader;

$reader = new DbalReader(
    $connection, // Instance of \Doctrine\DBAL\Connection
    'SELECT u.id, u.username, g.name FROM `user` u INNER JOIN groups g ON u.group_id = g.id'
);

DoctrineReader

Reads data through the Doctrine ORM:

use Ddeboer\DataImport\Reader\DoctrineReader;

$reader = new DoctrineReader($entityManager, 'Your\Namespace\Entity\User');

ExcelReader

Acts as an adapter for the PHPExcel library. Make sure to include that library in your project:

$ composer require phpoffice/phpexcel

Then use the reader to open an Excel file:

use Ddeboer\DataImport\Reader\ExcelReader;

$file = new \SplFileObject('path/to/excel_file.xls');
$reader = new ExcelReader($file);

To set the row number that headers will be read from, pass a number as the second argument.

$reader = new ExcelReader($file, 2);

To read the specific sheet:

$reader = new ExcelReader($file, null, 3);

OneToManyReader

Allows for merging of two data sources (using existing readers), for example you have one CSV with orders and another with order items.

Imagine two CSV's like the following:

OrderId,Price
1,30
2,15

OrderId,Name
1,"Super Cool Item 1"
1,"Super Cool Item 2"
2,"Super Cool Item 3"

You want to associate the items to the order. Using the OneToMany reader we can nest these rows in the order using a key which you specify in the OneToManyReader.

The code would look something like:

$orderFile = new \SplFileObject("orders.csv");
$orderReader = new CsvReader($file, $orderFile);
$orderReader->setHeaderRowNumber(0);

$orderItemFile = new \SplFileObject("order_items.csv");
$orderItemReader = new CsvReader($file, $orderFile);
$orderItemReader->setHeaderRowNumber(0);

$oneToManyReader = new OneToManyReader($orderReader, $orderItemReader, 'items', 'OrderId', 'OrderId');

The third parameter is the key which the order item data will be nested under. This will be an array of order items. The fourth and fifth parameters are "primary" and "foreign" keys of the data. The OneToMany reader will try to match the data using these keys. Take for example the CSV's given above, you would expect that Order "1" has the first 2 Order Items associated to it due to their Order Id's also being "1".

Note: You can omit the last parameter, if both files have the same field. Eg if parameter 4 is 'OrderId' and you don't specify parameter 5, the reader will look for the foreign key using 'OrderId'

The resulting data will look like:

//Row 1
array(
    'OrderId' => 1,
    'Price' => 30,
    'items' => array(
        array(
            'OrderId' => 1,
            'Name' => 'Super Cool Item 1',
        ),
        array(
            'OrderId' => 1,
            'Name' => 'Super Cool Item 2',
        ),
    ),
);

//Row2
array(
    'OrderId' => 2,
    'Price' => 15,
    'items' => array(
        array(
            'OrderId' => 2,
            'Name' => 'Super Cool Item 1',
        ),
    )
);

Create a reader

You can create your own data reader by implementing the Reader Interface.

Writers

ArrayWriter

Resembles the [ArrayReader](#arrayread