Автоматизація обробки даних постачальників: Excel, CSV, XML

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

Складнощі при парсингу прайс-листів

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

Порівняємо формати за ключовими параметрами: парсер на PHP в 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', }; } }