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

Column Mapping and Data Transformation

Translate user-facing headers into canonical fields and normalize values before validation or insertion.

Updated

LevelIntermediateReading time12 minPackagemnb/mnb-phpexcel
suggestColumnMap()transformer()projectColumns()importToSql()

What you will learn

  • Create explicit, auditable column maps.
  • Use mapping suggestions only as a user-assistance feature.
  • Normalize strings, numbers, dates, and enums before persistence.

Start with explicit canonical fields#

PHP
$map = [
    'Product Code' => 'sku',
    'Product Name' => 'name',
    'Unit Price' => 'price',
    'Available' => 'is_active',
];

Use suggestions for interactive mapping#

PHP
$suggestions = MnbExcel::suggestColumnMap(
    sourceColumns: ['Product Code', 'Title', 'Selling Price'],
    targetColumns: ['sku', 'name', 'price'],
    aliases: [
        'sku' => ['product code', 'item code'],
        'name' => ['title', 'product name'],
        'price' => ['selling price', 'unit price'],
    ],
    minConfidence: 0.60
);

Register a reusable transformer#

PHP
MnbExcel::transformer('normalize-product', function (array $row): array {
    $row['sku'] = strtoupper(trim((string) ($row['sku'] ?? '')));
    $row['name'] = trim((string) ($row['name'] ?? ''));
    $row['price'] = round((float) ($row['price'] ?? 0), 2);
    $row['is_active'] = in_array(
        strtolower((string) ($row['is_active'] ?? '')),
        ['1', 'true', 'yes', 'active'],
        true
    );

    return $row;
});

Apply transformations in the import pipeline#

PHP
$result = MnbExcel::largeImportToSql($path, $pdo, 'products', [
    'map' => $map,
    'transformers' => ['normalize-product'],
    'batch_size' => 1000,
]);