MNB PHPExcelDeveloper Guide
v2.0.5
Imports

Import Spreadsheet Rows into a Database

Map spreadsheet columns to SQL fields, validate batches, and choose insert or update behavior.

Updated

LevelIntermediateReading time14 minPackagemnb/mnb-phpexcel
SqlImporter::importRows()ReadSession::importToSql()ReadSession::dryRunImportToSql()suggestColumnMap()

What you will learn

  • Map worksheet headers to database columns.
  • Choose duplicate, transaction, and batch behavior.
  • Run a dry-run or preview before changing data.

Before you start

  • Install mnb/mnb-phpexcel:^2.0 and the source-format module you need.
  • Enable the appropriate PDO driver.
  • Create the target table and unique indexes.

Create a PDO connection explicitly#

database.php
$pdo = new PDO(
    'mysql:host=127.0.0.1;dbname=demo;charset=utf8mb4',
    getenv('DB_USER'),
    getenv('DB_PASSWORD'),
    [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES => false,
    ]
);

Dry-run the import first#

PHP
$preview = MnbExcel::read('products.xlsx')
    ->sheet('Products')
    ->withHeaderRow(1)
    ->dryRunImportToSql($pdo, 'products', [
        'map' => [
            'SKU' => 'sku',
            'Product Name' => 'name',
            'Price' => 'price',
        ],
        'unique_by' => ['sku'],
    ]);

print_r($preview);

Run a normal session import#

PHP
$result = MnbExcel::read('products.xlsx')
    ->sheet('Products')
    ->withHeaderRow()
    ->importToSql(
        $pdo,
        'products',
        [
            'map' => [
                'SKU' => 'sku',
                'Product Name' => 'name',
                'Price' => 'price',
            ],
            'batch_size' => 1000,
            'duplicate_strategy' => 'update',
            'unique_by' => ['sku'],
        ]
    );

Design database constraints before importer options#

  • Create a unique index for business keys such as SKU or email.
  • Use database types that match normalized spreadsheet values.
  • Keep nullable and required rules consistent between validation and SQL schema.
  • Decide whether a missing spreadsheet field means “leave unchanged,” “set null,” or “use default.”
  • Prefer batch transactions for resumability; use one full-file transaction only when lock duration is acceptable.