Skip to content
PHP 8.1+ Open source Developer documentation
Tools Packagist
Docs v2.0.6
Documentation versions v2.0.6Current v2.0.5Maintenance v2.0.4Maintenance v2.0.3Maintenance v2.0.0Initial v2 Compare versions →
MNB PHPExcelDeveloper Guide
AI

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.

Level
Intermediate
Reading time
14 min
Package
mnb/mnb-phpexcel
Updated
Full packagePDODry run
SqlImporter::importRows()ReadSession::importToSql()ReadSession::dryRunImportToSql()suggestColumnMap()

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.