Importing an OpenXML file (exported from EPIC)

Question

Hello community!

I am not having any success importing a report into Designer that was made in the OpenXML format. The system that is generating the report file is EPIC (Hospital EHR system). It generates an .XML file that I'm unable to bring into Designer. We've been manually opening the report in Excel (where it opens without issue) and then saving as an XLSX file. As the workflow is fully automated and needs to be run from the server, I'm trying to find a way to import the file directly.

I get the following error when dragging and dropping the file:

Checking the "Ignore XML Errors and Continue" gives the following:

This is not useful, but in a different way!

The file, when opened in Excel, has 27 columns and 92658 rows of data. Here's the 'info' sheet contents:

Does anyone have any experience importing an OpenXML file? Bonus points if it is a report out of Epic. We were told that this format is the only one available to export our data. The data we're after isn't patient info, but EPIC account info of when a user's account was created / activated.

Any assistance is greatly appreciated!

gyang3 · Accepted Answer

@JFurda Would you be able to provide a sample XML file or would that not be possible due to PHI? Reading XML files in Alteryx would usually require a combination of Input tool, text to column, and XML Parse tool.

To extract the Epic account info, you may need to check #8 in the Input tool "Return Outer XML". Once you have the outerxml, pull in a XML Parse tool and configure it to return the child values as well as any other outerxml (sample screenshot below). Or if you know that the EPIC account info is located within a specific child name, you can specify the child name in the XML Parse tool instead.

I have included a few articles that may be helpful

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/XML-Parsing-for-Beginners/ta-p/10782

https://community.alteryx.com/t5/Weekly-Challenge/Challenge-116-A-Symphony-of-Parsing-Tools/td-p/162575

Hopefully that helps you get started!