Data From XML

Import from XML function is used to get data from an XML file format.

SheetKraft contains the Data From XML option which can be accessed by the Import From Button present in the SheetKraft Toolbar (See figure)

There is no UI Support for XML as of now, and you will be directed to functions UI of Microsoft Excel (See figure)

File Location: Data from single/multiple file(s) with similar structure can be imported by selecting the excel range that specifies the file paths.

Root: A parent root which contains all the elements and attributes which are required in the import

Pattern: Arrangement of all the field names which will be required from the XML file and it acts as the headers when sorted as a tabular data.

Exclude Column Headers: TRUE Excludes the Column headers, FALSE includes column header into the imported data.

Sample XML Format:

Note: The Root in this XML file is //Customers/Customer

Before selecting the option for DataFromXML, the data in the XML file should be made familiar with and a root element must be selected which contains the elements and attributes required. Once the root is identified, the elements and attributes can be provided as patterns, and if no Pattern is provided, it will reflect the entire data in a tabular form by itself with the column headers.

And if pattern is selected for the same.

Notes: There should be ‘@’ before the Attributes. Both of them are Case-Sensitive. For blank rows or comments which are not contained in elements should have a pattern column looking for text; it should be taken as text().

In a similar way, you can select only the required columns.

Part 2

There might be cases where a required object is part of array contained inside another object. (See figure)

Sheetkraft will normally reflect the object as a text

To further tabulate the data in the proper form, you can separate the parent object and the child object with a slash (/)

Or the other way around to it is to extract the text from the column with another DataFromXML formula with pattern representing the child object contained inside parent string and fill the rest of the data down. (See figure)

And when there is an optional data, you assign the first option as [1] and then continue with [2], [3] and likewise, for each of the other options. (See figure)

Please note the two <Phone> objects.