Using a wildcard for XML input with different fields

[NeilFisk]

I have thousands of XML files that have similar fields, but not all fields are used in all files.  I wanted to input these using a wildcard, but when I do, Alteryx will use the structure of the first file to open the remaining files, and ultimately skip files that don’t have the same fields.  How does one get around this limitation.

Any suggestions would be welcomed.

[AbhilashR]

Hi @NeilFisk, how about doing an xml read within a standard macro to which you pass the location of each file from upstream? This way the macro treats each file as unique and unions the data before it gives one output.  

[NeilFisk]

I'm not that familiar with setting up macros.  With close to 10,000 files, I don't want to be identifying each file name somewhere because that would take a rather long time to do and I could potentially miss a file.  Is there a way to read the names of the files in a folder and then set up the macro you are talking about?

Thanks for the help.

[AbhilashR · Accepted solution]

Hi @NeilFisk, I have created a mock solution (attached) to help you get started. In this I use the:

Directory tool - to bring us a list of XML files in the folder (point it to your folder which has the xml files)

Select tool - to only use FilePath field

Custom Macro - right click and hit Open Macro option on this tool to look at how it was built

In my example I passed two xml files to the macro and the output shows it brought in data from two xml files into Alteryx. I hope this helps!

[NeilFisk · Accepted solution]

Abhilash,

This worked flawlessly.  Thank you so much for your guidance.

Regards,
Neil

[jholden18]

Abhilash - thanks for making this tool, it is very helpful. Is there a way to add to this macro / workflow to use the data cleansing tool to eliminate leading/trailing white space in the files? What I am trying to solve for is if you have a column named "Column 3" and one named " Column 3" or "Column 3 " then the columns will not match the data because the names are different.

Thanks!