.. _mapping-xml-to-ss: Mapping XML to spreadsheet ========================== In this tutorial, we will go over how to use the ``orcus-xml`` command to map an XML content into a spreadsheet document. We will be using :download:`this sample XML document ` throughout this tutorial. Examining the structure of input XML document --------------------------------------------- First, let's examine the general structure of this XML document: .. code-block:: XML Tab Limpenny true Male Kazakh Manda Hadgraft false Female Bislama ... It starts with the ```` element as its root element, which contains recurring ```` elements each of which contains multiple fields. By looking at each ```` element structure, you can easily infer how the record content is structured. You can also run ``orcus-xml`` in structure mode in order to detect the structure of its content. Running the following command .. code-block:: orcus-xml --mode structure example.xml should generate the following output: .. code-block:: /dataset /dataset/record[*] /dataset/record[*]/@id /dataset/record[*]/name /dataset/record[*]/name/first /dataset/record[*]/name/last /dataset/record[*]/active /dataset/record[*]/gender /dataset/record[*]/language This output lists the paths of all encountered "leaf node" items one item per line, in order of occurrence. Each path is expressed in a XPath-like format, except for recurring "anchor" elements which are suffixed with the ``[*]`` symbols. An anchor element in this context is defined as a recurring non-leaf element that contains either an attribute or a leaf element. You can think of anchor elements as elements that define the individual record boundaries. Auto-mapping the XML document ----------------------------- Mapping this XML document to a spreadsheet document can be done by simply running ``orcus-xml`` in map mode. You also need to specify the output format type and the output directory in order to see the content of the mapped spreadsheet document. Running the command: .. code-block:: orcus-xml --mode map -f flat -o out example.xml will create an output file named ``out/range-0.txt`` which contains the following: .. code-block:: --- Sheet name: range-0 rows: 21 cols: 6 +--------+-----------+-------------+--------+--------+----------------+ | id | first | last | active | gender | language | +--------+-----------+-------------+--------+--------+----------------+ | 1 [v] | Tab | Limpenny | true | Male | Kazakh | +--------+-----------+-------------+--------+--------+----------------+ | 2 [v] | Manda | Hadgraft | false | Female | Bislama | +--------+-----------+-------------+--------+--------+----------------+ | 3 [v] | Mickie | Boreham | false | Male | Swahili | +--------+-----------+-------------+--------+--------+----------------+ | 4 [v] | Celinka | Brookfield | false | Female | Gagauz | +--------+-----------+-------------+--------+--------+----------------+ | 5 [v] | Muffin | Bleas | false | Female | Hiri Motu | +--------+-----------+-------------+--------+--------+----------------+ | 6 [v] | Jackelyn | Crumb | false | Female | Northern Sotho | +--------+-----------+-------------+--------+--------+----------------+ | 7 [v] | Tessie | Hollingsbee | true | Female | Fijian | +--------+-----------+-------------+--------+--------+----------------+ | 8 [v] | Yank | Wernham | false | Male | Tok Pisin | +--------+-----------+-------------+--------+--------+----------------+ | 9 [v] | Brendan | Lello | true | Male | Fijian | +--------+-----------+-------------+--------+--------+----------------+ | 10 [v] | Arabel | Rigg | false | Female | Kyrgyz | +--------+-----------+-------------+--------+--------+----------------+ | 11 [v] | Carolann | McElory | false | Female | Pashto | +--------+-----------+-------------+--------+--------+----------------+ | 12 [v] | Gasparo | Flack | false | Male | Telugu | +--------+-----------+-------------+--------+--------+----------------+ | 13 [v] | Eolanda | Polendine | false | Female | Kashmiri | +--------+-----------+-------------+--------+--------+----------------+ | 14 [v] | Brock | McCaw | false | Male | Tsonga | +--------+-----------+-------------+--------+--------+----------------+ | 15 [v] | Wenda | Espinas | false | Female | Bulgarian | +--------+-----------+-------------+--------+--------+----------------+ | 16 [v] | Zachary | Banane | true | Male | Persian | +--------+-----------+-------------+--------+--------+----------------+ | 17 [v] | Sallyanne | Mengue | false | Female | Latvian | +--------+-----------+-------------+--------+--------+----------------+ | 18 [v] | Elizabet | Hoofe | true | Female | Tswana | +--------+-----------+-------------+--------+--------+----------------+ | 19 [v] | Alastair | Hutchence | true | Male | Ndebele | +--------+-----------+-------------+--------+--------+----------------+ | 20 [v] | Minor | Worland | true | Male | Dutch | +--------+-----------+-------------+--------+--------+----------------+ We are using the ``flat`` format type which writes the data range of a sheet in a human-readable grid output. The mapped sheet content is the result of the automatic mapping of the original XML document. In automatic mapping, all attributes and element contents that can be mapped as field values will be mapped, and the sheet name will be automatically generated. Although not applicable to this particular example, if the source XML document contains multiple mappable ranges, they will get mapped to multiple sheets, one sheet per range. Custom-mapping using map file ----------------------------- Generating map file ^^^^^^^^^^^^^^^^^^^ Automatic-mapping should work reasonably well in many cases, but sometime you may need to customize how you map your data, and this section will go over how you could do just that. The short answer is that you will need to create a map definition file and pass it to the ``orcus-xml`` command via ``-m`` or ``--map`` option. The easiest way to go about it is to have one generated for you. Running the following command: .. code-block:: orcus-xml --mode map-gen -o map.xml example.xml will generate a map file ``map.xml`` which contains the mapping definition based on the auto-detected structure. The content of ``map.xml`` generated from the example XML document should look like this: .. code-block:: XML Note that since the original map file content does not include any line breaks, you may want to run it through an XML reformatting tool such as `xmllint `_ to "prettify" its content before viewing. Map file structure ^^^^^^^^^^^^^^^^^^ Hopefully the structure of the map file is self-explanatory, but let us go over it a little. The ``map`` element is the root element which contains one or more ``sheet`` elements and one or more ``range`` elements. The ``sheet`` elements specify how many sheets should be created in the spreadsheet model, and what their names should be via their ``name`` attributes. The ordering of the ``sheet`` elements will reflect the ordering of the sheets in the final spreadsheet document. Each ``range`` element defines one mapped range of the source XML document, and this element itself stores the top-left position of the range in the final spreadsheet document via ``sheet``, ``row`` and ``column`` attributes. The ``range`` element then contains one or more ``field`` elements, and one or more ``row-group`` elements. Each ``field`` element defines one field within the mapped range and the path of the value in the source XML document. The path is expressed in XPath format. The ordering of the ``field`` elements reflects the ordering of the field columns in the final spreadsheet document. Each ``row-group`` element defines the path of an anchor element. For a simple XML document such as our current example, you only need one ``row-group`` element. But an XML document with more complex structure may need more than one ``row-group`` element to properly map nested recurring elements. Modifying map file ^^^^^^^^^^^^^^^^^^ Let's make some changes to this map file. First, the default sheet name ``range-0`` doesn't look very good, so we'll change it to ``My Data``. Also, let's assume we aren't really interested in the ID values or the "active" values (whatever they may mean), so we'll drop those two fields. Additionally, since we don't like the default field labels, which are taken literally from the names of the corresponding attributes or elements, we'll define custom field labels. And finally, we'll add two empty rows above the data range so that we can edit in some nice title afterward. The modified map file will look like this: .. code-block:: XML We'll save this as ``map-modified.xml``, and pass it to the ``orcus-xml`` command this time around like so: .. code-block:: ./src/orcus-xml --mode map -m map-modified.xml -o out -f flat example.xml This will output the content of the sheet to ``out/My Data.txt``, which will look like this: .. code-block:: --- Sheet name: My Data rows: 23 cols: 4 +------------+-------------+--------+----------------+ | | | | | +------------+-------------+--------+----------------+ | | | | | +------------+-------------+--------+----------------+ | First Name | Last Name | Gender | Language | +------------+-------------+--------+----------------+ | Tab | Limpenny | Male | Kazakh | +------------+-------------+--------+----------------+ | Manda | Hadgraft | Female | Bislama | +------------+-------------+--------+----------------+ | Mickie | Boreham | Male | Swahili | +------------+-------------+--------+----------------+ | Celinka | Brookfield | Female | Gagauz | +------------+-------------+--------+----------------+ | Muffin | Bleas | Female | Hiri Motu | +------------+-------------+--------+----------------+ | Jackelyn | Crumb | Female | Northern Sotho | +------------+-------------+--------+----------------+ | Tessie | Hollingsbee | Female | Fijian | +------------+-------------+--------+----------------+ | Yank | Wernham | Male | Tok Pisin | +------------+-------------+--------+----------------+ | Brendan | Lello | Male | Fijian | +------------+-------------+--------+----------------+ | Arabel | Rigg | Female | Kyrgyz | +------------+-------------+--------+----------------+ | Carolann | McElory | Female | Pashto | +------------+-------------+--------+----------------+ | Gasparo | Flack | Male | Telugu | +------------+-------------+--------+----------------+ | Eolanda | Polendine | Female | Kashmiri | +------------+-------------+--------+----------------+ | Brock | McCaw | Male | Tsonga | +------------+-------------+--------+----------------+ | Wenda | Espinas | Female | Bulgarian | +------------+-------------+--------+----------------+ | Zachary | Banane | Male | Persian | +------------+-------------+--------+----------------+ | Sallyanne | Mengue | Female | Latvian | +------------+-------------+--------+----------------+ | Elizabet | Hoofe | Female | Tswana | +------------+-------------+--------+----------------+ | Alastair | Hutchence | Male | Ndebele | +------------+-------------+--------+----------------+ | Minor | Worland | Male | Dutch | +------------+-------------+--------+----------------+ The new output now only contains four fields, with custom labels at the top, and now we have two empty rows above just like we intended.