Excel to XML: build an XML file from a template

What you need

• Your data in a spreadsheet (Excel, Google Sheets, LibreOffice), one record per row: a product, a URL, a price.
• A template XML: a sample of the expected file, with at least two records and every field you need. The safest is an export of a few records from the system that will import the file (Business Manager, Magento, PrestaShop…), or the sample given in the target platform's documentation.
• A free ExcelifyXML account: transforming the template costs 1 credit, reconstruction is free and unlimited.

Why a template?

A table is flat; an XML file has a structure: a root element, nested tags, attributes, namespaces (g:), sometimes a header. None of that is in your Excel columns.

The template brings that structure. ExcelifyXML turns it into two files: data.csv, the columns to fill, and skeleton.json, the structure used to write your XML.

The steps

1. Drop your template XML on the Upload page. In the free preview, check there's one record per row (a product, a URL…). If it shows a single row, pick the record's tag through "Not the right row?" (item in a Google Shopping feed). Confirm (1 credit) and unzip the bundle: data.csv and skeleton.json.
2. Open data.csv. The first line names each column: keep it, and delete the template's rows.
3. Paste your data column by column, under the right name. An empty cell creates no tag. For a field the template doesn't have, add a column named after the tag, with its prefix if it has one (g:color): the tag is created at the end of each record. A name that can't be a tag's (one with a space, say) stops the rebuild, which points it out.
4. Save as "CSV UTF-8": it's the format that guarantees your accented characters are read correctly, especially when the template has none (Excel for Mac's plain "CSV" would scramble them). On the Rebuild page, choose "Separate files" and drop this data.csv with the template's skeleton.json: you get your XML back.

An example: a Google Shopping feed

The template has two products. Their rows are replaced with three products from the spreadsheet, and a g:color column is added. The feed's title, link and description come from the template.

<rss version="2.0" xmlns:g="http://base.google.com/ns/1.0">
  <channel>
    <title>Ma boutique</title>
    <link>https://www.exemple.fr</link>
    <description>Flux produits</description>
    <item>
      <g:id>MODELE-1</g:id>
      <title>Exemple 1</title>
      <g:price>1.00 EUR</g:price>
      <g:availability>in_stock</g:availability>
    </item>
    <item>…</item>
  </channel>
</rss>

g:id , title , g:price , g:availability , g:color
SKU-1 , Veste , 59.90 EUR , in_stock , bleu
SKU-2 , Pull , 39.90 EUR , ,
SKU-3 , Chemise , 29.90 EUR , out_of_stock , vert

    <item>
      <g:id>SKU-1</g:id>
      <title>Veste</title>
      <g:price>59.90 EUR</g:price>
      <g:availability>in_stock</g:availability>
      <g:color>bleu</g:color>
    </item>
    <item>
      <g:id>SKU-2</g:id>
      <title>Pull</title>
      <g:price>39.90 EUR</g:price>
    </item>

What the template decides

• Everything outside the rows (root element, namespaces, feed title, catalog header) is taken from the template as is: fix it in the template XML before transforming it.
• Each record follows the template's shape: same tags, same order; added columns come last.
• Lists within a record (several images, several prices) use the template's numbered columns: image[0], image[1]… A numbered column added beyond them can't be written: the rebuild stops and points it out. Pick a template where one record has the largest number of items.
• Keep EAN and GTIN codes as text when you paste them into Excel, otherwise it turns them into scientific notation.
• The file follows the template's shape, not a schema: have it validated by the system that imports it, on a test environment first.

And next time?

Keep data.csv and skeleton.json. For the next delivery, update the rows and rebuild: no need to transform the template again, nor to spend a credit.

• Edit a large XML file: the "sample first" method
• EAN, GTIN and lost zeros in Excel
• What reconstruction keeps, and what may change