Сбор и нормализация данных из прайс-листов: Excel, CSV, XML

Поставщик прислал Excel-файл с объединёнными ячейками, CSV в Windows-1251 и XML с нестандартными тегами. Ручная обработка — часы рутины и ошибки синхронизации. Клиент из сферы электроники получал прайс-листы от 12 поставщиков в разных форматах: одни слали Excel со сводными таблицами, другие — CSV в KOI8-R, третьи — XML с пространствами имён. Ручная загрузка занимала 2 дня в неделю, а ошибки приводили к неверным остаткам на складе. Мы написали универсальный парсер с конфигом для каждого поставщика — теперь обновление происходит за 5 минут ежедневно, а экономия времени превышает 40 часов в месяц. За 5 лет мы разработали такие парсеры для 50+ проектов, и вот как это работает.

Какие сложности возникают при парсинге прайс-листов?

• Excel: данные начинаются не с первой строки, заголовки в объединённых ячейках, числа записаны как текст, даты в формате Excel.
• CSV: кодировка Windows-1251, разделитель — точка с запятой, цены с пробелами или точками.
• XML: разные схемы — товары могут быть атрибутами, вложенными элементами или пространствами имён.
• Несогласованные SKU: один поставщик пишет артикул как '12345', другой — как 'AB-123/45'. Парсер приводит всё к единому формату.
• N+1 запрос: если загружать товары по одному, база ляжет. Используем чанки по 500 записей и очереди — это в 10 раз быстрее построчного импорта.

Сравним форматы по ключевым параметрам:

Как парсер обрабатывает объединённые ячейки в Excel?

Используем библиотеку PhpSpreadsheet. Ключевой приём — считывание значения из верхней левой ячейки объединённого диапазона. В конфиге указываем строку заголовков, строку начала данных и псевдонимы колонок. Парсер автоматически сопоставляет заголовки с вашими полями, игнорируя пустые объединённые ячейки. Благодаря этому экономится до 90% времени на подготовку данных.

Почему важен автоопределитель кодировки в CSV?

Поставщики часто используют Windows-1251, а некоторые — KOI8-R. Без автоопределения данные приходят кракозябрами. Наш парсер, используя mb_detect_encoding, перекодирует содержимое в UTF-8 перед разбором. Также автоматически угадываем разделитель (запятая, точка с запятой, табуляция), анализируя первую строку. В результате ошибки кодировки сокращаются до 0.1% случаев.

Как мы проектируем универсальный парсер

Используем библиотеку PhpSpreadsheet для Excel, встроенные функции PHP для CSV и SimpleXML для XML. Классический шаблон Repository Pattern изолирует логику парсинга от источника данных. Ниже — ключевые классы.

Парсер Excel (PhpSpreadsheet)

// app/Services/PriceList/ExcelParser.php use PhpOffice\PhpSpreadsheet\IOFactory; use PhpOffice\PhpSpreadsheet\Cell\Coordinate; class ExcelParser { private array $columnMap = []; public function parse(string $filePath, array $config): array { $spreadsheet = IOFactory::load($filePath); $sheet = $spreadsheet->getSheetByName($config['sheet_name'] ?? null) ?? $spreadsheet->getActiveSheet(); $headerRow = $config['header_row'] ?? 1; $dataStartRow = $config['data_start_row'] ?? 2; // Определяем маппинг колонок по заголовкам $this->detectColumns($sheet, $headerRow, $config['column_aliases']); $products = []; $highestRow = $sheet->getHighestRow(); for ($row = $dataStartRow; $row <= $highestRow; $row++) { $sku = $this->getCellValue($sheet, $this->columnMap['sku'], $row); if (empty($sku)) continue; $products[] = [ 'sku' => trim($sku), 'name' => $this->getCellValue($sheet, $this->columnMap['name'] ?? null, $row), 'price' => $this->parsePrice($this->getCellValue($sheet, $this->columnMap['price'], $row)), 'in_stock' => $this->parseStock($this->getCellValue($sheet, $this->columnMap['stock'] ?? null, $row)), 'category' => $this->getCellValue($sheet, $this->columnMap['category'] ?? null, $row), ]; } return $products; } private function detectColumns(\PhpOffice\PhpSpreadsheet\Worksheet\Worksheet $sheet, int $headerRow, array $aliases): void { $highestCol = $sheet->getHighestColumn(); $highestColIndex = Coordinate::columnIndexFromString($highestCol); for ($col = 1; $col <= $highestColIndex; $col++) { $header = strtolower(trim( $sheet->getCellByColumnAndRow($col, $headerRow)->getValue() ?? '' )); foreach ($aliases as $fieldName => $possibleHeaders) { foreach ($possibleHeaders as $alias) { if (str_contains($header, strtolower($alias))) { $this->columnMap[$fieldName] = $col; break 2; } } } } } private function parsePrice(mixed $value): float { if (is_numeric($value)) return (float) $value; return (float) preg_replace('/[^\d.]/', '', str_replace(',', '.', (string) $value)); } private function parseStock(mixed $value): bool { if (is_null($value)) return true; $s = strtolower(trim((string) $value)); return !in_array($s, ['0', 'нет', 'no', 'false', 'отсутствует', '']); } } 

Конфигурация под конкретного поставщика:

// config/price_list_parsers.php return [ 'supplier_abc' => [ 'format' => 'excel', 'sheet_name' => 'Прайс', 'header_row' => 2, 'data_start_row' => 3, 'column_aliases' => [ 'sku' => ['Артикул', 'SKU', 'Код товара'], 'name' => ['Наименование', 'Название', 'Товар'], 'price' => ['Цена', 'Цена, руб.', 'Стоимость'], 'stock' => ['Остаток', 'Наличие', 'Кол-во'], 'category' => ['Категория', 'Группа', 'Раздел'], ], ], ]; 

Парсер CSV с автоопределением кодировки

// app/Services/PriceList/CsvParser.php class CsvParser { public function parse(string $filePath, array $config = []): array { $content = file_get_contents($filePath); // Автоопределение кодировки $encoding = mb_detect_encoding($content, ['UTF-8', 'Windows-1251', 'KOI8-R'], true); if ($encoding && $encoding !== 'UTF-8') { $content = mb_convert_encoding($content, 'UTF-8', $encoding); } // Автоопределение разделителя $delimiter = $config['delimiter'] ?? $this->detectDelimiter($content); $lines = str_getcsv($content, "\n"); $headers = str_getcsv(array_shift($lines), $delimiter); $headers = array_map('trim', $headers); $products = []; foreach ($lines as $line) { if (empty(trim($line))) continue; $row = str_getcsv($line, $delimiter); if (count($row) !== count($headers)) continue; $data = array_combine($headers, $row); $products[] = $this->normalizeRow($data, $config); } return $products; } private function detectDelimiter(string $content): string { $firstLine = strtok($content, "\n"); $counts = [ ',' => substr_count($firstLine, ','), ';' => substr_count($firstLine, ';'), "\t" => substr_count($firstLine, "\t"), ]; return array_key_first(array_filter($counts, fn($c) => $c === max($counts))); } private function normalizeRow(array $row, array $config): array { $map = $config['field_map'] ?? []; return [ 'sku' => trim($row[$map['sku'] ?? 'sku'] ?? ''), 'name' => trim($row[$map['name'] ?? 'name'] ?? ''), 'price' => $this->parsePrice($row[$map['price'] ?? 'price'] ?? 0), 'in_stock' => !empty($row[$map['stock'] ?? 'stock']), ]; } } 

Парсер XML

// app/Services/PriceList/XmlParser.php class XmlParser { public function parse(string $filePath, array $config): array { $xml = simplexml_load_file($filePath, 'SimpleXMLElement', LIBXML_NOCDATA); if ($xml === false) { throw new \RuntimeException("Не удалось разобрать XML: " . $filePath); } // XPath для извлечения товаров $itemXpath = $config['item_xpath'] ?? '//item'; $items = $xml->xpath($itemXpath); return array_map(fn($item) => $this->extractItem($item, $config), $items); } private function extractItem(\SimpleXMLElement $item, array $config): array { $fields = $config['fields'] ?? []; $extract = function(string $path) use ($item): string { // Поддержка атрибутов (@attr) и дочерних элементов (tag) if (str_starts_with($path, '@')) { return (string) ($item->attributes()[substr($path, 1)] ?? ''); } $nodes = $item->xpath($path); return $nodes ? trim((string) $nodes[0]) : ''; }; return [ 'sku' => $extract($fields['sku'] ?? 'article'), 'name' => $extract($fields['name'] ?? 'name'), 'price' => (float) $extract($fields['price'] ?? 'price'), 'in_stock' => $extract($fields['stock'] ?? 'available') !== '0', 'category' => $extract($fields['category'] ?? 'category'), ]; } } 

Фасад-диспетчер форматов

// app/Services/PriceList/PriceListParser.php class PriceListParser { public function parse(string $filePath, array $config): array { $format = $config['format'] ?? $this->detectFormat($filePath); return match ($format) { 'excel', 'xlsx', 'xls' => app(ExcelParser::class)->parse($filePath, $config), 'csv' => app(CsvParser::class)->parse($filePath, $config), 'xml' => app(XmlParser::class)->parse($filePath, $config), default => throw new \InvalidArgumentException("Неизвестный формат: {$format}"), }; } private function detectFormat(string $filePath): string { return match (strtolower(pathinfo($filePath, PATHINFO_EXTENSION))) { 'xlsx', 'xls', 'ods' => 'excel', 'csv', 'txt' => 'csv', 'xml' => 'xml', default => 'csv', }; } }