Supplier Price List Parser Bot (Excel/CSV/XML)

Price List Parser for Suppliers (Excel/CSV/XML)

Suppliers send price lists in different formats: Excel with non-standard structure, CSV with Cyrillic in various encodings, XML of varying standardization. Parser normalizes this into a unified format and syncs with store catalog.

Typical input file issues

• Excel: data starts on row 3, headers in merged cells
• CSV: windows-1251 encoding, semicolon delimiter, prices with spaces
• XML: non-standard tags, namespaces, attributes instead of values
• Inconsistent SKU formats across suppliers
• Numbers as strings, dates as Excel numbers

Excel Parser (PhpSpreadsheet)

// app/Services/PriceList/ExcelParser.php
class ExcelParser {
    public function parse(string $filePath, array $config): array {
        $spreadsheet = IOFactory::load($filePath);
        $sheet = $spreadsheet->getActiveSheet();

        $this->detectColumns($sheet, $config['header_row'] ?? 1, $config['column_aliases']);

        $products = [];
        for ($row = $config['data_start_row'] ?? 2; $row <= $sheet->getHighestRow(); $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)),
                'stock' => $this->parseStock($this->getCellValue($sheet, $this->columnMap['stock'] ?? null, $row)),
            ];
        }

        return $products;
    }
}

CSV Parser with encoding detection

// app/Services/PriceList/CsvParser.php
class CsvParser {
    public function parse(string $filePath, array $config = []): array {
        $content = file_get_contents($filePath);

        // Auto-detect encoding
        $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);
        }

        // Auto-detect delimiter
        $delimiter = $config['delimiter'] ?? $this->detectDelimiter($content);

        $lines = str_getcsv($content, "\n");
        $headers = str_getcsv(array_shift($lines), $delimiter);

        $products = [];
        foreach ($lines as $line) {
            if (empty(trim($line))) continue;
            $row = str_getcsv($line, $delimiter);
            $data = array_combine($headers, $row);
            $products[] = $this->normalizeRow($data, $config);
        }

        return $products;
    }
}

XML Parser

// app/Services/PriceList/XmlParser.php
class XmlParser {
    public function parse(string $filePath, array $config): array {
        $xml = simplexml_load_file($filePath, 'SimpleXMLElement', LIBXML_NOCDATA);

        $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'] ?? [];

        return [
            'sku'   => (string) $item->xpath($fields['sku'] ?? 'article')[0] ?? '',
            'name'  => (string) $item->xpath($fields['name'] ?? 'name')[0] ?? '',
            'price' => (float) ($item->xpath($fields['price'] ?? 'price')[0] ?? 0),
            'stock' => ((string) ($item->xpath($fields['stock'] ?? 'available')[0] ?? '1')) !== '0',
        ];
    }
}

Automatic file fetching

Price lists arrive via email, FTP, or HTTP:

// app/Jobs/FetchSupplierPriceList.php
class FetchSupplierPriceList implements ShouldQueue {
    public function handle(PriceListFetcher $fetcher, PriceListParser $parser): void {
        $supplier = Supplier::findOrFail($this->supplierId);

        $filePath = match ($supplier->price_list_source) {
            'ftp'   => $fetcher->downloadFromFtp($supplier),
            'url'   => $fetcher->downloadFromUrl($supplier->price_list_url),
            'email' => $fetcher->fetchFromEmail($supplier),
        };

        $config = config("price_list_parsers.{$supplier->config_key}");
        $products = $parser->parse($filePath, $config);

        foreach (array_chunk($products, 500) as $chunk) {
            ImportPriceListChunk::dispatch($supplier->id, $chunk);
        }
    }
}

Development timeline

Single supplier parser (1 format, standard structure): 2-4 business days. Universal dispatcher + 5 suppliers with different formats: 7-10 business days.