wingee/yii2-csv-importer

Yii2 Excel helper to import csv files to tables

Yii2 CSV Importer to Database

Helper for CSV imports to tables.

Installation

The preferred way to install this extension is through composer.

Either run

php composer.phar require --prefer-dist ruskid/yii2-csv-importer "dev-master"

or add

"ruskid/yii2-csv-importer": "dev-master"

to the require section of your composer.json file.

Usage

$importer = new CSVImporter;

//Will read CSV file
$importer->setData(new CSVReader([
    'filename' => $this->file->tempName,
    'fgetcsvOptions' => [
        'delimiter' => ';'
    ]
]));

//Import multiple (Fast but not reliable). Will return number of inserted rows
$numberRowsAffected = $importer->import(new MultipleImportStrategy([
    'tableName' => VendorSwType::tableName(),
    'configs' => [
        [
            'attribute' => 'name',
            'value' => function($line) {
                return $line[1];
            },
            'unique' => true, //Will filter and import unique values only. can by applied for 1+ attributes
        ]
    ],
]));

//Import Active Records (Slow, but more reliable). Will return array of primary keys
$primaryKeys = $importer->import(new ARImportStrategy([
    'className' => BusinessType::className(),
    'configs' => [
        [
            'attribute' => 'name',
            'value' => function($line) {
                return $line[2];
            },
        ],
        [
            'attribute' => 'items',
            'virtual' => true, //set non AR DB attribute (behavior or public model attribute) 
            'value' => function($line) {
                return Item::find()->select(['id'])->column('id');
            },
        ]
    ],
]));


// More advanced example. You can use queries to set related data.
// Use query caching for performance
$importer->import(new MultipleImportStrategy([
    'tableName' => ProductInventory::tableName(),
    'configs' => [
        [
            'attribute' => 'product_name',
            'value' => function($line) {
                //You cand perform your filters and excludes here. Empty exclude example:
                return $line[7] != "" AppHelper::importStringFromCSV($line[7]) : null;
            },
        ],
        [
            'attribute' => 'id_vendor_sw_type',
            'value' => function($line) {
                $name = AppHelper::importStringFromCSV($line[1]);
                $vendor = VendorSwType::getDb()->cache(function ($db) use($name) {
                    return VendorSwType::find()->where(['name' => $name])->one();
                });
                return isset($vendor) ? $vendor->id : null;
            },
        ],   
    ],
]));

//Special case only available with Active Record Strategy.
//Get primary key list of new imported items for later use.
$primaryKeys = $importer->import(new ARImportStrategy([
    'className' => Fabrica::className(),
    'configs' => [
        [
            'attribute' => 'name',
            'value' => function($line) {
                return $line[0];
            },
        ]
    ],
]));

//You can use the primary key list for the next import of related data.
//The order of primary key items will be the same as in csv file.
$importer->import(new MultipleImportStrategy([
    'tableName' => Product::tableName(),
    'configs' => [
        [
            'attribute' => 'id_fabrica',
            'value' => function($line) use (&$primaryKeys) {
                return array_shift($primaryKeys);
            },
            'unique' => true,
        ],     
    ],
]));


//You can skip CSV Row / Active Record import if some csv row doesn't meet the requirements.
$importer->import(new MultipleImportStrategy([
    'tableName' => ProductCategory::tableName(),
    'configs' => [
        [
            'attribute' => 'name',
            'value' => function($line) {
                return $line[0];
            },
        ],
        [
            'attribute' => 'description',
            'value' => function($line) {
                return $line[1];
            },
        ],
        [
            'attribute' => 'estado',
            'value' => function($line) {
                return $line[2];
            },
        ]
    ],
    //All ACTIVE categories that don't have name set to 'MS_SQLSERVER' or empty will be imported.
    'skipImport' => function($line){
        if($line[0] == 'MS_SQLSERVER' || $line[0] == ""){
            return true;
        }
        if($line[2] == 'NOT ACTIVE'){
            return true;
        }
    }           
]));

//Import or update multiple (Fast but not reliable). Will return number of inserted, updated and unchanged rows
//ARUpdateStrategy can be used instead of MultipleUpdateStrategy if you want to use AR validation.
//The returned value will be the number of inserted, updated and unchanged rows in both cases.
$records = $importer->import(new MultipleUpdateStrategy([
	'className' => Customer::className(),
	'csvKey' => function ($line) {
		return $line[0];
	},
	'rowKey' => function ($row) {
		return $row['gecom_id'];
	},
	'skipImport' => function ($line) {
		return !$line[0];
	},
	'configs' => [
		[
			'attribute' => 'customer_id',
			'value' => function($line) {
				return $line[0];
			},
		],
	],
]));