Из Excel в базу данных: строим конвейер импорта с заголовками, валидацией и вставкой батчами
Пользователь загружает прайс в Excel. На тестовом файле импорт работает, а в следующем колонки уже переставлены, вместо цены стоит «по запросу» и между товарами остались пустые строки. Нужно сохранить корректные записи и показать пользователю, какие строки он должен исправить.
Соберём такой импорт на avadim/fast-excel-reader: сопоставим колонки по заголовкам, проверим значения, запишем данные батчами и выгрузим ошибки в CSV с номерами строк.
Примеры проверены на FastExcelReader 4.5.0, PHP 8.4 и MySQL 8.4. SQL ниже рассчитан на MySQL 8.0.19+ и таблицу InnoDB; для других СУБД запрос вставки нужно адаптировать.
Установим версию, для которой написан пример:
composer require avadim/fast-excel-reader:4.5.0
В примерах используем Excel::openXlsx() — явный метод открытия XLSX, добавленный в 4.5. Метод Excel::open() по-прежнему доступен и выбирает формат по содержимому файла.
Шаг 1. Первая строка — как ключи массива
Если обращаться к ячейкам как к $row['A'] и $row['B'], перестановка колонок меняет смысл данных: вместо цены можно прочитать количество. Для файлов с заголовками удобнее использовать их как ключи массива.
use avadim\FastExcelReader\Excel;
$excel = Excel::openXlsx('products.xlsx');
$sheet = $excel->sheet();
foreach ($sheet->withHeader()->nextRow() as $rowNum => $row) {
// $row = ['Артикул' => 'A-100', 'Название' => '...', 'Цена' => 990.0]
}
withHeader() берёт значения первой строки области чтения как ключи для последующих строк. Эти ключи повторяют названия в файле. Если порядок колонок фиксирован, можно сразу задать имена полей приложения:
foreach ($sheet->withHeader(['sku', 'name', 'price', 'qty'])->nextRow() as $rowNum => $row) {
// $row = ['sku' => 'A-100', 'name' => '...', 'price' => 990.0, 'qty' => 5]
}
Строка заголовка по-прежнему пропускается, но ключами становятся ваши имена. Имена применяются позиционно — по порядку заполненных колонок заголовка в области чтения. Такой вариант требует фиксированного порядка и полной шапки: перестановку колонок он не распознает. В полном примере ниже сначала прочитаем readFirstRow(), найдём колонки по названиям и проверим обязательные и повторяющиеся заголовки.
Тот же результат можно получить и классическим способом через флаги:
foreach ($sheet->nextRow(['A' => 'sku', 'B' => 'name', 'C' => 'price', 'D' => 'qty'], Excel::KEYS_FIRST_ROW) as $rowNum => $row) {
// ...
}
Шаг 2. Чистим данные на входе
Реальные файлы полны «почти пустых» строк: где-то пробел, где-то пустая строка после удаления значения. Обрезаем пробелы и не добавляем пустые строки в батч:
$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;
}
// Обрабатываем непустую строку; значение 0 сохраняется.
}
TRIM_STRINGS обрезает пробелы в строковых значениях, а TREAT_EMPTY_STRING_AS_EMPTY_CELL не добавляет пустое строковое значение в строку результата. Эти флаги сами по себе не гарантируют пропуск строки целиком, поэтому проверка в цикле остаётся.
Если в файле есть даты (например, «действует с»), задайте формат до чтения:
$sheet->setDateFormat('Y-m-d');
Он применяется к ячейкам, которые ридер распознал как даты по формату Excel. Произвольную строку вроде «завтра» этот вызов не разберёт; текстовые даты требуют отдельной проверки.
Шаг 3. Валидация с номерами строк
В отчёте об ошибке нужен номер строки исходного файла. В показанном режиме генератор nextRow() отдаёт именно номер строки листа — используем его:
function validateRow(array $row): array
{
$errors = [];
$sku = (string)($row['sku'] ?? '');
if ($sku === '' || mb_strlen($sku) > 64) {
$errors[] = 'артикул обязателен, максимум 64 символа';
}
if (mb_strlen((string)($row['name'] ?? '')) > 255) {
$errors[] = 'название длиннее 255 символов';
}
$price = (string)($row['price'] ?? '');
if (!preg_match('/^(?:0|[1-9][0-9]{0,9})(?:\.[0-9]{1,2})?$/D', $price)) {
$errors[] = 'цена: неотрицательное число, до 10 цифр и 2 знаков после точки';
}
$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[] = 'количество: целое число от 0 до 2147483647';
}
}
return $errors;
}
Пустая ячейка может отсутствовать в результате или иметь значение null из шаблона строки. Читайте необязательные значения через ??, как в функции выше.
В этом контракте цена — неотрицательное десятичное число с точкой и не более чем двумя дробными знаками, количество — неотрицательное целое. Пустое количество считаем нулём. Строки вида «1 234,50», валютные знаки и лишние дробные знаки отклоняем, а не угадываем их смысл. Для артикулов с ведущими нулями исходная ячейка должна быть текстовой: после сохранения числа 00123 как 123 ридер не восстановит исходное значение.
Шаг 4. Вставка батчами
Вставка по одной записи создаёт отдельный запрос на каждую строку и много обращений к БД. Один запрос на весь файл может упереться в max_allowed_packet или лимит плейсхолдеров. Начнём с батчей по 500 строк; подходящий размер зависит от длины значений и настроек сервера.
Перед запуском создайте таблицу (или адаптируйте запрос под свою). Уникальный ключ по артикулу нужен для повторной загрузки:
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;
Полный скрипт ниже ожидает products.xlsx в текущем рабочем каталоге и заголовки «Артикул», «Название», «Цена», «Количество» в первой строке. Их порядок можно менять. Настройки подключения берём из переменных окружения IMPORT_DSN (например, mysql:host=127.0.0.1;dbname=app;charset=utf8mb4), IMPORT_USER и IMPORT_PASSWORD. Отчёт записывается в import-errors.csv и перезаписывается при каждом запуске; при параллельных импортах задавайте отдельное имя файла.
Скрипт целиком, включая ту же функцию валидации:
<?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[] = 'артикул обязателен, максимум 64 символа';
}
if (mb_strlen((string)($row['name'] ?? '')) > 255) {
$errors[] = 'название длиннее 255 символов';
}
$price = (string)($row['price'] ?? '');
if (!preg_match('/^(?:0|[1-9][0-9]{0,9})(?:\.[0-9]{1,2})?$/D', $price)) {
$errors[] = 'цена: неотрицательное число, до 10 цифр и 2 знаков после точки';
}
$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[] = 'количество: целое число от 0 до 2147483647';
}
}
return $errors;
}
// Настройте IMPORT_DSN, IMPORT_USER и IMPORT_PASSWORD в окружении.
$dsn = getenv('IMPORT_DSN');
if (!$dsn) {
throw new RuntimeException('Задайте IMPORT_DSN для 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();
// Находим колонки по заголовкам, а не по их позиции.
$expected = ['Артикул' => 'sku', 'Название' => 'name', 'Цена' => 'price', 'Количество' => '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('Повторяется заголовок: ' . $heading);
}
$columnMap[$column] = $key;
}
}
$missing = array_diff(array_values($expected), $columnMap);
if ($missing) {
throw new RuntimeException('Нет обязательных колонок: ' . implode(', ', $missing));
}
$errorFile = fopen('import-errors.csv', 'wb');
if ($errorFile === false) {
throw new RuntimeException('Не удалось открыть отчёт об ошибках');
}
$writeError = static function (int $rowNum, array $messages) use ($errorFile): void {
if (fputcsv($errorFile, [$rowNum, implode('; ', $messages)], ';', '"', '') === false) {
throw new RuntimeException('Не удалось записать отчёт об ошибках');
}
};
$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) {
// Отсеиваем пустую строку после очистки; 0 не считаем пустым.
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'], // строка для DECIMAL, не дополнительный 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("Обработано корректных строк: %d; отклонено: %d\n", $imported, $rejected);
У этого скрипта есть несколько особенностей:
- Строки и ошибки не накапливаются целиком. Код хранит текущую строку и батч; ошибки сразу записываются в CSV. При этом FastExcelReader 4.5.0 загружает таблицу общих строк XLSX (
sharedStrings) в память, поэтому расход памяти всё же зависит от файла. - Ошибки валидации не прерывают импорт. Неверные строки попадают в отчёт с исходными номерами, корректные — в БД. Ошибка SQL останавливает скрипт; уже подтверждённые батчи сохраняются.
- Повторная загрузка обновляет данные по артикулу. Это обеспечивают
PRIMARY KEY (sku)иON DUPLICATE KEY UPDATE. Если артикул встречается несколько раз, последняя корректная строка обновит предыдущую. Счётчик показывает число обработанных корректных строк, а не число новых товаров. - SQL привязан к MySQL. Используем алиас входящей строки
AS incoming; устаревающую функциюVALUES(column)в части обновления не вызываем. Подробности — в документации MySQL.
Шаг 5. Транзакции — по батчу, не по файлу
В полном примере каждый батч уже обёрнут в транзакцию. При ошибке его изменения откатываются, исключение передаётся вызывающему коду. Счётчик увеличивается только после успешного commit().
Длинная транзакция на весь файл может удерживать блокировки и увеличивать объём undo-лога. Транзакции по батчам ограничивают эти издержки, но допускают частичный результат: ошибка позднего батча не откатывает ранние. Это осознанный выбор для прайса, который можно загрузить повторно.
Если нужна строгая атомарность «всё или ничего», двух проходов недостаточно: после успешной валидации во втором проходе всё равно возможна ошибка БД. Нужна одна транзакция на все изменения в целевых таблицах либо загрузка в staging-таблицу с последующим атомарным применением. Первый проход позволяет заранее обнаружить ошибки данных, но не заменяет транзакцию. Файл между проходами должен оставаться неизменным.
Шаг 6. Выбираем лист и область чтения
Таблица может находиться на другом листе или начинаться ниже названия отчёта. Укажем лист и область, в которой первая строка содержит заголовки:
// нужный лист по имени
$sheet = $excel->selectSheet('Прайс');
// данные начинаются не с A1: шапка таблицы в строке 4, колонки B..E
$sheet->setReadArea('B4:E100000', true); // true — первая строка области содержит заголовки
foreach ($sheet->nextRow() as $rowNum => $row) {
// Ключи — заголовки из строки 4, номера строк — исходные номера листа.
}
В этом коротком примере true включает заголовки сразу. В полном скрипте шапку нужно сначала прочитать для проверки и сопоставления, поэтому настройка другая. Замените $sheet = $excel->sheet(); следующими строками перед readFirstRow():
$sheet = $excel->selectSheet('Прайс');
$sheet->setReadArea('B4:E100000'); // без true: readFirstRow() должен вернуть саму шапку
Заголовок пропустит флаг KEYS_FIRST_ROW в основном цикле. Если включить режим заголовков до readFirstRow(), метод вернёт уже строку данных. Верхняя граница 100000 обрежет всё, что ниже: выбирайте её по контракту файла.
Что ещё учесть перед запуском
Проверка цены работает со значением, которое вернул ридер, а не с тем, как ячейка выглядит в Excel. Числовую ячейку библиотека может вернуть как int или float; преобразование в строку перед PDO не восстанавливает точность, уже потерянную в Excel или при чтении. Если по контракту нужна точная десятичная запись, передавайте цены текстовыми ячейками с точкой и проверяйте их формат.
Формулы FastExcelReader сам не вычисляет: для формульных цен нужен актуальный сохранённый результат расчёта. Скрипт проверяет значение цены, но не может определить, устарел ли этот результат.
Описание API — в документации версии 4.5.0. Поведение заголовков, флагов и номеров строк сверено с AbstractSheet.php, открытие файла и загрузка общих строк — с Excel.php.