Choose Language Hide Translation Bar
phil_kay
Staff
Importing and merging multiple XML files

I wanted to use all the data from my exercise activity tracking app as part of an analysis for a blog post. So I downloaded all the data from the last six years.

When I unzipped this there were 660 files -- one for each logged activity:

Picture1.png

And they were all XML file format. I had never worked with XML before. For my analysis, I would need to get each file into JMP data table format and then stack them all into a single JMP table.

So it is great that there is a new XML Import Wizard in JMP 15. Even better, you can copy the settings from the wizard and use them to import all the files in a folder into a single JMP table in one step. Let me show you how...

First of all, your files will all need to have the *.xml file extension. My files had a different extension. There are tools available outside of JMP for changing the file extension for multiple files, if you have this same problem.

Next, in JMP, File > Open and navigate to the folder where you have your XML files. Change the file type to XML Data Files (*.xml):

Picture2.png

Now, click on a single file to select it. Make sure you have Open as Data (Using Preview) selected:

Picture3.png

Click Open.

The XML Import Wizard will launch. Having never worked with XML before I didn't know what to do here at first. But it didn't take me long to figure it out by trying a few clicks. The wizard can make some guesses at the structure of the data. And you can see whether the settings are right with the Import Preview. You can also change the Column Names that you will have in the resulting JMP table.

Picture4.png

(I have obscured some of the details in the data here because you don't need to know exactly where I was on the 4th April 2014!) 

Now, click OK and you should have a nicely formatted JMP data table, which is great if you just want to import a single XML file. But I didn't want to repeat this manual process for all 660 files. This is the really nice bit...

Right-click on the Source script in the data table and Edit. Now, copy the XML settings from the script:

Picture5.png

In your clipboard, you now have the instructions for importing any of the XML files in your folder (assuming they have the same structure).

Now, File > Import Multiple Files. Click on the |...| button to select your folder of XML files:

Picture6.png

You could also use the "Select files by..." options to filter out certain files, if required.

Click Settings and paste the XML settings into the Script box in the XML tab:

Picture7.png

At this point, I kept Stack Similar Files option checked to bring all the files together into a single JMP data table.

All you need to do now is click Import, and you will have your data in JMP ready to explore!