Importing XML files & Transforming them to Rows and Columns

June 15, 2018

Importing XML files & Transforming them to Rows and Columns

Converting XML files into rows and columns is automatically handled during import with Paxata.     Paxata does a particularly nice job of handling XML, JSON data with regards of converting the xml structure to rows and columns with virtually no additional effort from the end user.  The transpose is all handled during import.

 

Try out these steps to see how it’s done.
Step 1:  In the Library, click the + Import icon    
Step 2:  Select a data source (or local file) that contains an xml or json file*
Step 3:  The parser will automatically detect that this is an xml file and transpose the structure to rows and columns
 
Step 4:  In the lower left quadrant of the parsing screen, adjust the settings as desired.  XML Path and Column Naming are the two most common settings to adjust.
     a) The parser will suggest an appropriate Path.
 
Override this path by either manually typing/deleting or use the Override button to get a more graphical representation of the xml structure.
 
If you want to bring in all elements in the file, change the path to “/”
   b) The Column Naming can be set to either Simple or Full. Choosing Full will name each column the full xml path to that element.  This can help disambiguate elements of similar names.
Using Simple Name:
The same data & settings using Full Name:.

Now that your XML data has been imported (and transformed to rows and columns along the way), you are ready to clean up that data and publish it where it needs to be.

 

*n.b.  Paxata supports globbing multiple xml files together as a single dataset.  All the techniques discussed in this tip apply whether you are importing a single xml file or globbing multiple xml files together.

Show Buttons
Hide Buttons