How to convert a price list: XLS → CSV → XML → YML

Why is it necessary to convert price list formats?

Each system and platform has its own preferences for data formats. Understanding the reasons for conversion will help you choose the right approach and tools.

Basic conversion scenarios

Integration with marketplaces:

• Your price list in Excel → YML for Yandex.Market, Goods.ru
• CSV from 1C → XML for Amazon, eBay
• Any format → specific platform format

Working with suppliers:

• XLS from supplier → CSV for uploading to your store
• XML from major distributors → Excel for analysis by managers
• Many different formats → a single standard for processing

Performance optimization:

• Large XLSX → CSV for 10-20x faster imports
• Excel with formulas → "flat" format with ready-made values
• XML → JSON for Modern APIs and Web Applications

Process automation:

• Manual Excel → programmatically processed CSV/XML
• Unstructured data → standardized format
• Preparing data for bulk processing with scripts

Conversion methods: from manual to automatic

The choice of conversion method depends on the volume of data, the frequency of conversions, and the technical skills of the team.

1. Manual conversion

When to use: one-time conversions, small files (up to 1000 positions), no budget for tools.

Advantages:

• No special tools required
• Complete control over the process
• Possibility of adjustments "on the fly"

Flaws:

• Takes a lot of time for large volumes
• High risk of human error
• Ineffective for frequent conversions
• Difficulty with XML/YML formats

2. Online converters

When to use: Irregular conversions, medium volumes (up to 50,000 lines), simplicity is needed.

Popular services:

• Convertio.co: XLS/XLSX ↔ CSV, support for 300+ formats
• CloudConvert: Bulk conversion, API for automation
• ZAMZAR: simple interface, free up to 50 MB
• OnlineConvertFree: no registration, fast processing

Advantages:

• No software installation required
• Works from any device
• Often free for basic use
• Simple drag-and-drop interface

Flaws:

• File size limits (usually 100 MB)
• Security Considerations When Uploading Commercial Data
• Internet and download speed dependency
• Limited conversion settings

3. Automatic systems

When to use: Regular conversions, large volumes (50,000+ items), multiple suppliers.

Types of solutions:

• ETL systems: Pentaho, Talend - for corporate integrations
• Specialized platforms: Elbuz, DataFeedWatch - for e-commerce
• Scripts and programs: Python, PHP - for custom solutions
• CMS plugins: ready-made modules for popular platforms

Advantages:

• Instant processing of large volumes
• Setting up mapping and transformation rules
• Scheduling automatic conversions
• Integration with other systems
• Logging and error handling

Flaws:

• Initial setup required
• Cost of licenses or development
• May require technical knowledge

XLS/XLSX → CSV conversion

The most common conversion in e-commerce: Excel data from suppliers needs to be converted to CSV for quick processing by import systems.

Method 1: Excel "Save As"

Step-by-step instructions:

1. Open the XLS/XLSX file in Microsoft Excel or LibreOffice Calc
2. File → Save As → Select "CSV (comma delimited)" or "CSV UTF-8"
3. Enter a file name and click "Save"
4. Excel will warn you about formatting loss - confirm

Method 2: Programmatic Conversion (Python)

 import pandas as pd # Reading an Excel file df = pd.read_excel('supplier_price.xlsx', sheet_name='Price') # Saving to CSV with the required encoding df.to_csv('supplier_price.csv', index=False, # without an index column encoding='utf-8-sig', # UTF-8 with BOM for Excel sep=';') # semicolon separator print(f"{len(df)} rows converted")

Method 3: Online converters

Upload XLSX to Convertio.co or CloudConvert, select CSV, and download the result. Suitable for one-time conversions.

What's lost when converting XLS to CSV?

• Formatting: colors, fonts, alignment
• Formulas: only calculated values remain
• Multiple sheets: only the active sheet is saved
• Images: images and logos are removed
• Merged cells: are broken down into separate
• Macros and scripts: are completely removed

Common problems and solutions

Problem: The Cyrillic alphabet turns into “kryakozyabra” (R абота)

Solution: Use UTF-8 with BOM when saving, or convert encoding after creating CSV.

Problem: Dates are converted to numbers (44925 instead of 2023-01-15)

Solution: Before saving, format the column as text or use the TEXT() formula to convert it.

Problem: Leading zeros are removed (00123 → 123)

Solution: Add an apostrophe before the number ('00123) or enclose it in quotation marks ("00123").

CSV → XML conversion

Converting simple CSV tabular data into structured hierarchical XML is required for B2B integrations and enterprise systems.

Method 1: Online converters

ConvertCSV.com:

1. Upload a CSV file or paste data
2. Select "CSV to XML"
3. Customize the root element and tag names
4. Download the result

Method 2: Python script

 import pandas as pd import xml.etree.ElementTree as ET from xml.dom import minidom # Read CSV df = pd.read_csv('products.csv', encoding='utf-8', sep=';') # Create XML structure root = ET.Element('price_list') root.set('date', '2025-10-21') for _, row in df.iterrows(): product = ET.SubElement(root, 'product') for column in df.columns: element = ET.SubElement(product, column.lower().replace(' ', '_')) element.text = str(row[column]) # Formatting and saving xml_str = minidom.parseString(ET.tostring(root)).toprettyxml(indent=" ") with open('products.xml', 'w', encoding='utf-8') as f: f.write(xml_str) print("XML file created successfully")

Conversion example

Original CSV:

 SKU;Name;Price;Stock A001;Laptop HP;799;12 B002;Mouse Logitech;29;45

XML Result:

<?xml version="1.0" encoding="UTF-8"?><price_list date="2025-10-21"><product><sku> A001</sku><name> HP Laptop</name><price> 799</price><stock> 12</stock></product><product><sku> B002</sku><name> Logitech Mouse</name><price> 29</price><stock> 45</stock></product></price_list>

Method 3: Excel Power Query

Excel can export data to XML via "Data → From Text/CSV → Load to XML." This is suitable for simple transformations without programming.

XML → YML conversion

YML (Yandex Market Language) is a specialized XML format for marketplaces. Converting regular XML to YML requires adherence to a strict specification.

YML file structure

YML requires the following elements:

• yml_catalog: root element with date
• shop: store information
• Currencies: list of currencies and rates
• categories: category tree
• offers: product offerings

Method 1: Manual conversion (small volumes)

For small catalogs, you can create a YML template and fill it with XML data manually or using search and replace in a text editor.

Method 2: XSLT transformation

XSLT (eXtensible Stylesheet Language Transformations) allows you to programmatically transform one XML into another according to specified rules.

<?xml version="1.0" encoding="UTF-8"?><xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"><xsl:template match="/"><yml_catalog date="{current-date()}"><shop><name> MyStore</name><url> https://mystore.com </url><offers><xsl:for-each select="//product"><offer id="{sku}" available="true"><price><xsl:value-of select="price"/></price><name><xsl:value-of select="name"/></name></offer></xsl:for-each></offers></shop></yml_catalog></xsl:template></xsl:stylesheet>

Method 3: Dedicated YML Generators

E-commerce platforms usually have built-in YML feed generators:

• OpenCart, PrestaShop, WooCommerce: YML generation plugins
• 1C-Bitrix: Yandex.Market export module
• Elbuz: Automatic YML generation from any format
• Custom scripts: PHP/Python generators for your structure

Required elements of a YML offer

<offer id="12345" available="true"><price> 799</price><currencyId> EUR</currencyId><categoryId> 10</categoryId><picture> https://example.com/image.jpg</picture><delivery> true</delivery><name> Laptop HP 15</name><vendor> HP</vendor><description> Description here</description></offer>

Reverse conversions

Sometimes it is necessary to transform data in the opposite direction - from complex formats to simple ones.

YML/XML → CSV

Why is it necessary: Data analysis in Excel, import into accounting systems, report creation.

Python script for XML → CSV:

 import xml.etree.ElementTree as ET import pandas as pd # XML parsing tree = ET.parse('products.xml') root = tree.getroot() # Data extraction data = [] for product in root.findall('.//product'): row ={ 'SKU': product.find('sku').text, 'Name': product.find('name').text, 'Price': product.find('price').text, 'Stock': product.find('stock').text } data.append(row) # Create CSV df = pd.DataFrame(data) df.to_csv('products_from_xml.csv', index=False, encoding='utf-8-sig') print(f"Extracted {len(data)} products")

CSV → Excel (with formatting)

Creating XLSX with automatic formatting:

 import pandas as pd from openpyxl.styles import Font, PatternFill # Read CSV df = pd.read_csv('products.csv', encoding='utf-8') # Create Excel writer with pd.ExcelWriter('products_formatted.xlsx', engine='openpyxl') as writer: df.to_excel(writer, sheet_name='Price', index=False) # Formatting workbook = writer.book worksheet = writer.sheets['Price'] # Headings header_fill = PatternFill(start_color='366092', end_color='366092', fill_type='solid') header_font = Font(color='FFFFFF', bold=True) for cell in worksheet[1]: cell.fill = header_fill cell.font = header_font # Autowidth columns for column in worksheet.columns: max_length = max(len(str(cell.value)) for cell in column) worksheet.column_dimensions[column[0].column_letter].width = max_length + 2 print("Excel file with formatting created")

YML → Excel for analysis

Use online tools like "YML to CSV converter" or libraries to parse XML and export to a tabular format.

Conversion tools

Online services

Computer programs

For Windows:

• Microsoft Excel: Basic XLS ↔ CSV conversion
• Altova MapForce: visual mapping between formats
• Advanced CSV Converter: specialized tool
• Total Excel Converter: batch file conversion

Cross-platform:

• LibreOffice Calc: free alternative to Excel
• Oxygen XML Editor: professional work with XML
• DB Browser for SQLite: import/export of different formats

Libraries for developers

Python:

• pandas: universal work with tabular data
• openpyxl: reading/writing Excel files
• lxml: fast XML parsing
• xmltodict: Converting XML to dictionaries

PHP:

• PhpSpreadsheet: working with Excel
• SimpleXML: built-in XML parser
• League\Csv: Advanced CSV workflow

JavaScript/Node.js:

• xlsx: Parsing Excel in the browser and Node.js
• xml2js: XML ↔ JSON conversion
• papaparse: powerful CSV parser

Common conversion errors

1. Problems with encoding

Symptoms:

• Russian letters are displayed as "РџСЂРѕРґСѓРєС‚"
• Euro signs (€) turn into question marks
• Special characters are broken

Reasons:

• The original file is in Windows-1251, but is read as UTF-8.
• No encoding was specified during conversion.
• Excel on Windows creates CSV in ANSI

Solutions:

• Always use UTF-8 (preferably with BOM for Excel)
• Check encoding in Notepad++ (Encodings → Convert to UTF-8)
• In LibreOffice, explicitly specify the encoding when saving CSV
• Use iconv or recode to resave files

2. Loss of leading zeros

Problem: Articles like 00123, 001 become 123, 1

Solutions:

• In Excel, format the column as "Text" before entering it.
• Add an apostrophe: '00123
• In CSV, enclose values in quotation marks: "00123"
• For programmatic processing, use zfill(): "123".zfill(5) → "00123"

3. Incorrect separators

Problem: CSV with commas opens in one column, or vice versa

Reasons:

• Windows regional settings (in the Russian version, the separator is a semicolon)
• There is a mismatch between the separator in the file and the one expected by the program.

Solutions:

• Open CSV via "Data → From Text" and specify the delimiter
• Use universal editors (LibreOffice, Google Sheets)
• When processing programmatically, explicitly specify delimiter=';'

4. Distortion of dates and numbers

Problems:

• Dates turn into numbers: 44925 instead of 2023-01-15
• Floating-point numbers are rounded: 19.99 → 20
• Fractions are interpreted as dates: 1/2 → 01.Feb

Solutions:

• Use the standard ISO 8601 date format: YYYY-MM-DD
• For prices, use a number format with two decimal places.
• When importing, explicitly specify data types for columns.
• In Python, use dtype when reading CSV

5. Data loss with multiple sheets

Problem: Excel with multiple sheets converts only one (active)

Solutions:

• Convert each sheet separately
• Use scripts to batch convert all sheets
• Combine sheets into one before converting
• Use pandas to loop through all sheets

6. Problems with XML structure

Errors:

• "XML parsing error: not well-formed"
• "Invalid character in entity name"
• "Unclosed tag"

Solutions:

• Escape special characters: & → &,< → <, > → >
• Close all tags: 100
• Use CDATA for texts with HTML: ]]>
• Validate XML before use

Conclusion

Converting price list formats is an inevitable part of running an online store, given the diversity of systems and platforms. Choosing the right conversion method saves time, prevents errors, and ensures data integrity.

Key recommendations

For small volumes (up to 1000 items):

• Use manual conversion via Excel/LibreOffice
• Online converters for quick conversions
• Check encoding and delimiters carefully

For medium volumes (1000-50000 items):

• Customizable online services
• Python/PHP scripts for automation
• Create templates for regular conversions

For large volumes (50,000+ items):

• Automated systems like Elbuz
• Custom scripts with error logging
• Batch processing with results validation

Conversion checklist

1. Before conversion:
   • Create a backup copy of the original file
   • Check the encoding of the source data
   • Define the target structure
2. During conversion:
   • Use UTF-8 for Cyrillic
   • Keep leading zeros in article numbers
   • Check date and price formats
3. After conversion:
   • Check the number of lines (must match)
   • Open the file in the target program
   • Check several random positions
   • Validate XML/YML before uploading