Universal importer for different table formats like excel or csv
Install it with composer
composer require popov/php-importer -o- LibXl (commercial)
- Excel
- Soap
- Csv (not implemented yet)
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, ...)
| Nominal | Serial |
|---|---|
| 3% | 3002345 |
| 3% | 3002346 |
| 3% | 3002346 |
| 5% | 5002344 |
| 5% | 5002345 |
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());
}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);
Options marked with * are required.
Driver is handler for data from source.
You can use one of the registered drivers or create your own.
['driver' => 'Excel']You can pass any custom options to driver, there is no limit for it.
[
'driver' => 'Excel',
"driver_options" => [
"path" => "data/path/to/excel.xlsx",
"sheet" => [
"name" => "Sheet Name",
"skip" => 2,
],
],
]
Path to file which should be handled.
Name of the sheet which should be handled. By default first sheet is taken.
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_name',Required. A table where to save imported data.
'__codename' => 'discount',Required. Shortcut unique name for config related to table.
'__identifier' => 'serial',
// or
'__identifier' => ['asin', 'marketplace'],Unique field name for avoid duplicated items. Identifier can be as one field such as multiple fields.
'__ignore' => ['comment'],Fields which should be ignored in save operation. These fields can be used in data filtration.
'__exclude' => false,Bool. Exclude table from save operation. All fields can be used in data filtration.
'__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' => 'save'
]save - save new and excited data
update - only update excited data
There's a module for that!