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];
			},
		],
	],
]));