MNB PHPExcelDeveloper Guide
v2.0.5
Documentation versions v2.0.5Current v2.0.4Maintenance v2.0.3Maintenance v2.0.0Initial v2 Compare versions →
Database XLSX integration

Stream Large XLSX Rows into PDO

Import one worksheet or a workbook map in bounded-memory chunks with validation, mapping, transactions, progress, and duplicate handling.

Updated

LevelAdvancedReading time12 minPackagemnb/mnb-phpexcel
LargeExcelDatabaseImportEngine::importToSql()importWorkbookToSql()

What you will learn

  • Stream one named worksheet into a database table.
  • Map multiple worksheets to multiple tables.
  • Configure chunk and batch boundaries independently.

Stream one sheet into a table#

PHP
use Mnb\PHPExcel\Large\LargeExcelDatabaseImportEngine;

$engine = new LargeExcelDatabaseImportEngine();
$result = $engine->importToSql('orders.xlsx', $pdo, 'orders', [
    'sheet' => 'Orders',
    'with_header' => true,
    'chunk_size' => 2000,
    'batch_size' => 500,
    'transaction_per_chunk' => true,
    'duplicate_strategy' => 'update',
    'unique_by' => ['order_id'],
    'manifest_path' => __DIR__ . '/storage/orders-import.json',
    'failed_rows_csv' => __DIR__ . '/storage/orders-failed.csv',
    'resume' => true,
]);

Import multiple worksheets#

PHP
$result = $engine->importWorkbookToSql(
    'erp-export.xlsx',
    $pdo,
    [
        'Customers' => 'customers',
        'Orders' => 'orders',
        'Order Lines' => 'order_lines',
    ],
    [
        'chunk_size' => 1000,
        'resume' => true,
    ]
);

Separate chunk size from SQL batch size#

chunk_size controls how many worksheet rows are delivered to one orchestration cycle. batch_size controls prepared SQL insertion batches. Tune them independently based on row width, validation cost, transaction duration, and database limits.