MNB PHPExcelDeveloper Guide
AI Assistant
v2.0.6
Documentation versions v2.0.6Current v2.0.5Maintenance v2.0.4Maintenance v2.0.3Maintenance v2.0.0Initial v2 Compare versions →
Imports

Import Excel to MySQL in PHP

Import Excel to MySQL in PHP by mapping spreadsheet columns to SQL fields, validating batches, and choosing insert or update behavior.

Updated

Full packagePDODry run
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.