From Excel to MySQL in PHP: header mapping, validation, and batch inserts
A user uploads an Excel price list. Your import works on the test file, but the next upload has reordered columns, “on request” in a price cell, and blank rows between products. You need to save the valid records and tell the user which rows need fixing.
We’ll build that import with avadim/fast-excel-reader: map columns by their headers, validate values, write batches to MySQL, and export errors to CSV with the original row numbers.
The examples were checked with FastExcelReader 4.5.0, PHP 8.4, and MySQL 8.4. The SQL requires MySQL 8.0.19+ and an InnoDB table; adapt the insert statement if you use another database.
Install the version used in this article:
composer require avadim/fast-excel-reader:4.5.0
The examples use Excel::openXlsx(), an explicit XLSX factory introduced in 4.5. Excel::open() is still available and selects the reader based on the file contents.
Step 1. Use the header row as array keys
If you access cells as $row['A'] and $row['B'], moving a column changes what your code reads: a quantity can end up where you expected a price. For files with headers, using those headers as array keys makes the mapping easier to follow.
use avadim\FastExcelReader\Excel;
$excel = Excel::openXlsx('products.xlsx');
$sheet = $excel->sheet();
foreach ($sheet->withHeader()->nextRow() as $rowNum => $row) {
// $row = ['SKU' => 'A-100', 'Name' => '...', 'Price' => 990.0]
}
withHeader() uses the values in the first row of the read area as keys for subsequent rows. Those keys match the labels in the file. If the column order is fixed, you can supply your application’s field names instead:
foreach ($sheet->withHeader(['sku', 'name', 'price', 'qty'])->nextRow() as $rowNum => $row) {
// $row = ['sku' => 'A-100', 'name' => '...', 'price' => 990.0, 'qty' => 5]
}
The header row is still skipped, but your names become the keys. Names are applied by position, in the order of populated header columns within the read area. This requires a fixed column order and a complete header row: it does not detect reordered columns. In the full example below, we’ll call readFirstRow(), find columns by name, and check for missing or duplicate required headers.
You can also use an explicit column map with a flag:
foreach ($sheet->nextRow(['A' => 'sku', 'B' => 'name', 'C' => 'price', 'D' => 'qty'], Excel::KEYS_FIRST_ROW) as $rowNum => $row) {
// ...
}
Step 2. Clean up incoming values
Uploaded files often contain rows that look empty but still hold spaces or empty strings. Trim string values and keep empty rows out of the batch:
$flags = Excel::TRIM_STRINGS | Excel::TREAT_EMPTY_STRING_AS_EMPTY_CELL;
foreach ($sheet->withHeader(['sku', 'name', 'price', 'qty'])->nextRow([], $flags) as $rowNum => $row) {
if (!array_filter($row, static fn ($value) => $value !== null && $value !== '')) {
continue;
}
// Process a nonempty row; preserve zero values.
}
TRIM_STRINGS trims string values, while TREAT_EMPTY_STRING_AS_EMPTY_CELL leaves empty string values out of the resulting row. These flags do not guarantee that the entire row is skipped, so the check inside the loop is still needed.
If the file includes dates, such as an “Effective from” column, set the format before reading:
$sheet->setDateFormat('Y-m-d');
This applies to cells the reader identifies as dates from their Excel number format. It will not parse arbitrary text such as “tomorrow”; text dates need separate validation.
Step 3. Report validation errors with row numbers
An error report needs the row number from the source file. In the mode used here, nextRow() yields the original worksheet row number:
function validateRow(array $row): array
{
$errors = [];
$sku = (string)($row['sku'] ?? '');
if ($sku === '' || mb_strlen($sku) > 64) {
$errors[] = 'SKU is required, maximum 64 characters';
}
if (mb_strlen((string)($row['name'] ?? '')) > 255) {
$errors[] = 'name exceeds 255 characters';
}
$price = (string)($row['price'] ?? '');
if (!preg_match('/^(?:0|[1-9][0-9]{0,9})(?:\.[0-9]{1,2})?$/D', $price)) {
$errors[] = 'price: a nonnegative number, up to 10 integer digits and 2 decimal places';
}
$qty = $row['qty'] ?? null;
if ($qty !== null && $qty !== '') {
if (!preg_match('/^(?:0|[1-9][0-9]{0,9})$/D', (string)$qty) || (float)$qty > 2147483647) {
$errors[] = 'quantity: an integer from 0 to 2147483647';
}
}
return $errors;
}
An empty cell may be absent from the result or have a null value from the row template. Read optional values with ??, as the function above does.
For this import, a price must be a nonnegative decimal value with a period and no more than two decimal places. Quantity must be a nonnegative integer; an empty quantity means zero. Values such as “1,234.50”, currency symbols, and extra decimal places are rejected rather than interpreted. A SKU with leading zeros must come from a text cell: once 00123 has been stored as the number 123, the reader cannot recover the original value.
Step 4. Insert rows in batches
Inserting one record at a time means a separate query and database round trip for every row. A single query for the whole file can hit max_allowed_packet or the placeholder limit. We’ll start with batches of 500 rows; the right size depends on the length of the values and your server settings.
Create the table before running the script, or adapt the query to your schema. The unique SKU key makes repeated imports possible:
CREATE TABLE products (
sku VARCHAR(64) NOT NULL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
price DECIMAL(12, 2) NOT NULL,
qty INT UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
The complete script expects products.xlsx in the current working directory, with “SKU”, “Name”, “Price”, and “Quantity” in the first row. The columns can appear in any order. Connection settings come from the IMPORT_DSN environment variable, for example mysql:host=127.0.0.1;dbname=app;charset=utf8mb4, plus IMPORT_USER and IMPORT_PASSWORD. Errors go to import-errors.csv, which is overwritten on each run; use a separate filename for each concurrent import.
Here is the complete script, including the same validation function:
<?php
require __DIR__ . '/vendor/autoload.php';
use avadim\FastExcelReader\Excel;
const BATCH_SIZE = 500;
function validateRow(array $row): array
{
$errors = [];
$sku = (string)($row['sku'] ?? '');
if ($sku === '' || mb_strlen($sku) > 64) {
$errors[] = 'SKU is required, maximum 64 characters';
}
if (mb_strlen((string)($row['name'] ?? '')) > 255) {
$errors[] = 'name exceeds 255 characters';
}
$price = (string)($row['price'] ?? '');
if (!preg_match('/^(?:0|[1-9][0-9]{0,9})(?:\.[0-9]{1,2})?$/D', $price)) {
$errors[] = 'price: a nonnegative number, up to 10 integer digits and 2 decimal places';
}
$qty = $row['qty'] ?? null;
if ($qty !== null && $qty !== '') {
if (!preg_match('/^(?:0|[1-9][0-9]{0,9})$/D', (string)$qty) || (float)$qty > 2147483647) {
$errors[] = 'quantity: an integer from 0 to 2147483647';
}
}
return $errors;
}
// Set IMPORT_DSN, IMPORT_USER, and IMPORT_PASSWORD in the environment.
$dsn = getenv('IMPORT_DSN');
if (!$dsn) {
throw new RuntimeException('Set IMPORT_DSN for MySQL 8.0.19+');
}
$pdo = new PDO($dsn, getenv('IMPORT_USER') ?: '', getenv('IMPORT_PASSWORD') ?: '', [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_EMULATE_PREPARES => false,
]);
$excel = Excel::openXlsx('products.xlsx');
$sheet = $excel->sheet();
// Find columns by header labels rather than position.
$expected = ['SKU' => 'sku', 'Name' => 'name', 'Price' => 'price', 'Quantity' => 'qty'];
$columnMap = [];
foreach ($sheet->readFirstRow() as $column => $heading) {
$heading = trim((string)$heading);
if (isset($expected[$heading])) {
$key = $expected[$heading];
if (in_array($key, $columnMap, true)) {
throw new RuntimeException('Duplicate header: ' . $heading);
}
$columnMap[$column] = $key;
}
}
$missing = array_diff(array_values($expected), $columnMap);
if ($missing) {
throw new RuntimeException('Missing required columns: ' . implode(', ', $missing));
}
$errorFile = fopen('import-errors.csv', 'wb');
if ($errorFile === false) {
throw new RuntimeException('Cannot open the error report');
}
$writeError = static function (int $rowNum, array $messages) use ($errorFile): void {
if (fputcsv($errorFile, [$rowNum, implode('; ', $messages)], ';', '"', '') === false) {
throw new RuntimeException('Cannot write the error report');
}
};
$insertBatch = static function (array $batch) use ($pdo): void {
if (!$batch) {
return;
}
$placeholders = implode(',', array_fill(0, count($batch), '(?, ?, ?, ?)'));
$sql = "INSERT INTO products (sku, name, price, qty) VALUES $placeholders AS incoming
ON DUPLICATE KEY UPDATE name = incoming.name, price = incoming.price, qty = incoming.qty";
$values = [];
foreach ($batch as $row) {
array_push($values, $row['sku'], $row['name'], $row['price'], $row['qty']);
}
$pdo->beginTransaction();
try {
$pdo->prepare($sql)->execute($values);
$pdo->commit();
} catch (Throwable $e) {
if ($pdo->inTransaction()) {
$pdo->rollBack();
}
throw $e;
}
};
$flags = Excel::KEYS_FIRST_ROW | Excel::TRIM_STRINGS | Excel::TREAT_EMPTY_STRING_AS_EMPTY_CELL;
$batch = [];
$imported = $rejected = 0;
try {
foreach ($sheet->nextRow($columnMap, $flags) as $rowNum => $row) {
// Skip rows that are empty after trimming; zero is not empty.
if (!array_filter($row, static fn ($value) => $value !== null && $value !== '')) {
continue;
}
$rowErrors = validateRow($row);
if ($rowErrors) {
$writeError($rowNum, $rowErrors);
$rejected++;
continue;
}
$batch[] = [
'sku' => (string)$row['sku'],
'name' => (string)($row['name'] ?? ''),
'price' => (string)$row['price'], // pass a string for DECIMAL, without an extra float cast
'qty' => (int)($row['qty'] ?? 0),
];
if (count($batch) >= BATCH_SIZE) {
$insertBatch($batch);
$imported += count($batch);
$batch = [];
}
}
if ($batch) {
$insertBatch($batch);
$imported += count($batch);
}
} finally {
fclose($errorFile);
}
printf("Valid rows processed: %d; rejected: %d\n", $imported, $rejected);
A few details affect how this script behaves:
- Rows and errors are not collected for the entire file. The code holds the current row and a batch; errors are written directly to CSV. FastExcelReader 4.5.0 still loads the XLSX shared string table (
sharedStrings) into memory, so memory use depends on the file. - Validation errors do not stop the import. Invalid rows go to the report with their original row numbers, and valid rows go to the database. An SQL error stops the script; previously committed batches remain saved.
- Importing again updates products by SKU.
PRIMARY KEY (sku)andON DUPLICATE KEY UPDATEprovide this behavior. If a SKU appears more than once, the last valid row updates the earlier one. The counter reports valid rows processed, not newly created products. - The SQL is specific to MySQL. It uses the
AS incomingrow alias instead of the deprecatedVALUES(column)function in the update clause. See the MySQL documentation.
Step 5. Use one transaction per batch
The full example already wraps each batch in a transaction. If a batch fails, its changes are rolled back and the exception is rethrown. The counter increases only after a successful commit().
A transaction covering the entire file can hold locks for a long time and increase undo log usage. Transactions per batch limit those costs but allow a partial result: a failed later batch does not roll back earlier batches. That tradeoff suits a price list you can import again.
If you need all-or-nothing behavior, two passes are not enough: even after validation succeeds, the second pass can still hit a database error. Use one transaction for all changes to the target tables, or load a staging table and apply the changes atomically. A validation pass can catch data errors in advance, but it does not replace a transaction. The file must remain unchanged between passes.
Step 6. Select a worksheet and read area
The table may be on another worksheet or start below a report title. Select the sheet and an area whose first row contains the headers:
// Select the worksheet by name.
$sheet = $excel->selectSheet('Price list');
// The header is in row 4, with data in columns B through E.
$sheet->setReadArea('B4:E100000', true); // true: the first row of the area contains headers
foreach ($sheet->nextRow() as $rowNum => $row) {
// Keys come from row 4; row numbers are the original worksheet row numbers.
}
In this short example, true enables header mode immediately. The full script needs to read the header itself for validation and mapping, so its setup is different. Replace $sheet = $excel->sheet(); with these lines before readFirstRow():
$sheet = $excel->selectSheet('Price list');
$sheet->setReadArea('B4:E100000'); // omit true: readFirstRow() must return the header itself
The KEYS_FIRST_ROW flag in the main loop will skip the header. If you enable header mode before calling readFirstRow(), that method returns a data row instead. The upper bound of 100000 excludes everything below it, so choose a limit that matches the agreed file format.
Before running this on your data
Price validation checks the value returned by the reader, not the way Excel displays the cell. The library may return a numeric cell as an int or float; casting it to a string before passing it to PDO cannot recover precision already lost in Excel or during reading. If your input format requires an exact decimal representation, supply prices as text cells with a period and validate their format.
FastExcelReader does not calculate formulas. Formula-based prices need an up-to-date cached result. The script validates the price value, but it cannot tell whether that cached result is stale.
For the API reference, see the version 4.5.0 documentation. Header handling, flags, and row numbers were checked against AbstractSheet.php; file opening and shared string loading were checked against Excel.php.