popov/php-importer

Universal fast importer tables for different formats, such as Excel or CSV

0.1.2 2016-08-30 10:11 UTC

Requires

• php: >=5.5
• agerecompany/db: ~0.1

MIT

• Sergiy Popov

• excel
• csv
• importer
• agere

README

Universal importer for different table formats like excel or csv

Installation

Install it with composer

composer require popov/php-importer -o

Supported drivers

1. LibXl (commercial)
2. Excel
3. Soap
4. Csv (not implemented yet)

Requirements

Importer use INSERT ... ON DUPLICATE KEY UPDATE Syntax in background for reduce number of queries to database.

You should have only one unique field in your table otherwise you can get undesirable result. If you need to have several unique fields you should group them with UNIQUE Constraint such as UNIQUE (field_1, field_2, ...)

Usage

Example import File

Nominal	Serial
3%	3002345
3%	3002346
3%	3002346
5%	5002344
5%	5002345

Standalone

use Popov\Importer\Factory\DriverCreator;
use Popov\Importer\Importer;
use Popov\Db\Db;

$config = [
    'tasks' => [
        'discount-card' => [
            'driver' => 'libxl',
            'fields' => [
                [
                    // mapping fields in file to db fields with apply filters
                    'Nominal' => ['name' => 'discount', '__filter' => ['percentToInt']],
                    'Serial' => 'serial',
                    
                    // table where save imported data
                    '__table' => 'discount_card',
                    // shortcut name
                    '__codename' => 'discount',
                    // unique field name for avoid duplicate
                    '__identifier' => 'serial'
                ],
            ],
        ],
    ],
];

$pdo = new PDO('mysql:host=myhost;dbname=mydb', 'login', 'password'); 
$db = (new Db())->setPdo($pdo);

$factory = new DriverCreator($config);
$importer = new Importer($factory, $db);

if ($importer->import('discount-card', '/path/to/file.xls')) {
  echo 'Success import!';
} else {
  var_dump($importer->getErrors());
}

Advanced Usage

Most popular PHP frameworks implement IoC pattern and they also implement standard interface Interop\Container\ContainerInterface. This library support this functionality. You can pass your own IoC to Factory and be happy with creating objects.

$pdo = new PDO('mysql:host=myhost;dbname=mydb', 'login', 'password'); 
$db = (new Db())->setPdo($pdo);

$container = /* getYourContainer */;
$factory = new DriverCreator($config, $container);
$importer = new Importer($factory, $db);

Configuration

Options marked with * are required.

driver *

Driver is handler for data from source. You can use one of the registered drivers or create your own.

['driver' => 'Excel']

driver_options

You can pass any custom options to driver, there is no limit for it.

Excel options

[
    'driver' => 'Excel',
    "driver_options" => [
        "path" => "data/path/to/excel.xlsx",
        "sheet" => [
            "name" => "Sheet Name",
            "skip" => 2,
        ],
    ],
]

path

Path to file which should be handled.

sheet: name

Name of the sheet which should be handled. By default first sheet is taken.

sheet: skip

Skip first N rows in file. By default first row is taken.

###fields Mapping fields from one resource to new (MySQL, CSV, Excel)

The simples mapping can be written as:

// from   =>   to
['Serial' => 'serial']

Fields filtration and preparation can be grouped in chain

[
    'Nominal' => ['name' => 'discount', '__filter' => ['trim', 'percentToInt']]
]

__filter - reserved name for filtration

__prepare - reserved name for preparation

All reserved options begin with "__" (double underscore).

__table

'__table' => 'table_name',

Required. A table where to save imported data.

__codename

'__codename' => 'discount',

Required. Shortcut unique name for config related to table.

__identifier

'__identifier' => 'serial',
// or
'__identifier' => ['asin', 'marketplace'],

Unique field name for avoid duplicated items. Identifier can be as one field such as multiple fields.

__ignore

'__ignore' => ['comment'],

Fields which should be ignored in save operation. These fields can be used in data filtration.

__exclude

'__exclude' => false,

Bool. Exclude table from save operation. All fields can be used in data filtration.

__exclude

'__foreign' => ['customer_table' => 'customerId'],

This option is actual if set up minimum two group of fields in config. For example, if you have customer and review info, you put customer info in first group of fields and review info in second group of fields. When first group will be saved the ID will be marked in memory and second group can use this value.

Options

mode

'__options' => [
    'mode' => 'save'
]

save - save new and excited data

update - only update excited data

Integration with ZF2

There's a module for that!