XML to Excel

rohit782192

Hi Team,

How to Convert XML generated in Alteryx of the workflow to Excel Format.

Can anyone can help.?

Parse

Accepted answers

ArnaldoSandoval

Hi @rohit782192

Certainly it is harder to assist or help you when you share so little information about your issue, let's see if I GUESSED what you want, here we go.

I haven't seen any Alteryx Documentation detailing the structure of workflow files, e.g. yxmd, all what we know is they are written as XML files, and that we can open with a text editor, but nothing else.
YXMD files are version based workflow files, so whatever we write to read them should be updated each time Alteryx release a new version, as new structures could be included, creating strong dependencies with whatever tool we use to read them.

I am writing these notes for member of the community that happen to visit this thread-topic. I am basing these notes on the following workflow, it volutered for this investiagation 😀

It is a simple workflow with 4 tools, and their tool's ID is shown above; Alteryx save the XML of these tools to the yxmd, organizing each one of them as nodes!

The yxmd's Nodes tree:

The yxmd file contains a Nodes tree, each tool has an entry in this tree, each tool with its own set of XML attributes defining how it was configured by the user at the designer, all these XML attributes are subject to change.
The screenshot shows the ToolID 1, which is the Input Data tool, inside the XML file its node is defined by a lot of attributes, like GuiSetting, Properties, Configuration, Annotation and much more, depending on each tool scope.
We also notice that ToolID 3 follows, as it appears in the screenshot, Tool 3 is the second tool in the workflow, a select with its own set of properties.
By default a workflow reading a yxmd file returns all the nodes, e.g. all the tools contained in the workflow, nothing else.

The Input Data tool reading the yxmd was configured to handle a file format XML and the Return Child Values and Return Outer XML options are enabled.
With this configuation the Input Data tool reads all the <Node ToolID nodes from the yxmd file, although it does not include all the details of each tool configuration, we may see we get the top level details for each tool.

The workflow processing this very simple workflow, looks like:

It is obvious that each Node contains specific Alteryx tools with particular parameters, e.g. the complexity of the workflow increase exponentially.

The Connection Tree

The yxmd's connection tree is not returned by the Input Data tool used above, we need an Input Data with a different configuration.

This is the data returned by the Data Input above, it includes the Connections_OuterXML allowing us to parse the connection.

Now the solution node contains sort of a connection matrix for all the tools on the canvas, as shown:

The Connections matrix likely contains N-1 entries, depending on the complexity of the workflow.

The attached workflow process the yxmd's XML for the file defined at the Input Data, the workflow is far from complete to process complex workflows with many tools, node at its very top-level is easier, detailing each node properties is way to complex.

Procesing the Connection in the workflow highlights how to retrieve this information, perhaps a macro with this concept could process complex workflows.

Attaching:

Sample workflow: Xml_to_Excel.yxmd.
Get_Workflow_Out_2_Excel.yxmd - this process the XML_to_Excel Alteryx workflow, it is a very raw version.

hth

Arnaldo

Xml_to_Excel.yxmd

Get_Workflow_Out_2_Excel.yxmd

All comments

FrederikE

Hey @rohit782192,

There is no universal approach to this. This is an individual transformation job. If you can share the XML we might be able to help.

If you understand the XML-Format well, the XML Parse Tool should help, otherwise Text To Columns and RegEx are the go-to Tools to start with the parsing.

There is also a weekly XML Parsing Challenge where you can look at the results which might be helpful: https://community.alteryx.com/t5/Weekly-Challenge/Challenge-37-Parsing-a-Raw-XML-File/td-p/36577.

rohit782192

if you have alteryx you can see xml in your Alteryx Designer. It is generated in XML View of the workflow configuration Tab.

FrederikE

hey @rohit782192,

You mean parsing a yxmd-file?

You can do this by renaming the file and thereby changing its extention to .xml

e.g. New Workflow2.xml

This file can be read by an other Alteryx process.

However, transforming this into an excel file should be done dependend on a certain use case. Transforming semi-structured documents into tabluar format is often very bad for performance and analysis afterwards as they are more dynamic.

See the attached WF for reading such a file as a .xml (e.g. for finding what tools are used)

OR reading a .csv that can be used to tranform further.

New Workflow2.yxzp

jdminton

If you are wanting to get the xml into Excel with each line of code on a separate row, you can use the top method in the workflow. If you'd like to see all of the code in a single cell, you can use the second output in the workflow. This, however, will only work with small xml files due to Excel's character limit in a cell. Some additional context around why you are bringing it to Excel might provide some alternate solutions for you.

Xml to Excel.yxmd

rohit782192

When we create a Workflow in Alteryx Designer a corresponding XML get Generated in XML view which is available in Workflow-Configuration.

That XML i have break down into Excel. I am able to do for Connection. I am not able to do for Node.

I need some highlight on Extracting node.

ArnaldoSandoval

Hi @rohit782192

I had been following your question but even with the additional information added to the thread, I still have difficulties understanding your request; I know you are talking about the XML code visible on each tool configuration pane, below screenshot is this XML code for a Formula Tool, now each tool has its own XML code, but still your last post mention Connection and Node; Would you be able to include screen shots of these tools' XML screens? and even better an screenshot of the expected output,

This is the XML code for a formual tool.

Help us to help you,

Arnaldo

rohit782192

You are close to the Question.

I am pasting the Screen print from where i can see the full workflow XML from the Workflow-Configuration.

Any workflow in the Designer Generate an XML in the Workflow Configuration.

You can refer attached where we can see.

XMLSCREENPRINT.JPG

ArnaldoSandoval

@rohit782192

Bingo 👍 the screenshot below shows the XML for an Alteryx workflow, on the left, the XML shown by Alteryx, on the right the same workflow opened by Notepag++

Now what do you want to do with the whole XML code? you mention Excel, but what do you want to write to Excel?

It is late here, so I will read you answers 7 hours from now,

hth

Arnaldo.

rohit782192

I want Node and Connection in Excel.

Connection is Coming in Excel when we select Child .

But Unable to break the Node

jdminton

@rohit782192 perhaps you can share what you have for your workflow so far. It would be much easier to help if we could see what you've done so far and what the issue is that you're experiencing. Workflows can be attached the same way that you're attaching images.

rohit782192

You can consider any simple workflow by joininig two tables.

That will generate XML and you can pass..

ArnaldoSandoval

Hi @rohit782192

Certainly it is harder to assist or help you when you share so little information about your issue, let's see if I GUESSED what you want, here we go.

I haven't seen any Alteryx Documentation detailing the structure of workflow files, e.g. yxmd, all what we know is they are written as XML files, and that we can open with a text editor, but nothing else.
YXMD files are version based workflow files, so whatever we write to read them should be updated each time Alteryx release a new version, as new structures could be included, creating strong dependencies with whatever tool we use to read them.

I am writing these notes for member of the community that happen to visit this thread-topic. I am basing these notes on the following workflow, it volutered for this investiagation 😀

It is a simple workflow with 4 tools, and their tool's ID is shown above; Alteryx save the XML of these tools to the yxmd, organizing each one of them as nodes!

The yxmd's Nodes tree:

The yxmd file contains a Nodes tree, each tool has an entry in this tree, each tool with its own set of XML attributes defining how it was configured by the user at the designer, all these XML attributes are subject to change.
The screenshot shows the ToolID 1, which is the Input Data tool, inside the XML file its node is defined by a lot of attributes, like GuiSetting, Properties, Configuration, Annotation and much more, depending on each tool scope.
We also notice that ToolID 3 follows, as it appears in the screenshot, Tool 3 is the second tool in the workflow, a select with its own set of properties.
By default a workflow reading a yxmd file returns all the nodes, e.g. all the tools contained in the workflow, nothing else.

The Input Data tool reading the yxmd was configured to handle a file format XML and the Return Child Values and Return Outer XML options are enabled.
With this configuation the Input Data tool reads all the <Node ToolID nodes from the yxmd file, although it does not include all the details of each tool configuration, we may see we get the top level details for each tool.

The workflow processing this very simple workflow, looks like:

It is obvious that each Node contains specific Alteryx tools with particular parameters, e.g. the complexity of the workflow increase exponentially.

The Connection Tree

The yxmd's connection tree is not returned by the Input Data tool used above, we need an Input Data with a different configuration.

This is the data returned by the Data Input above, it includes the Connections_OuterXML allowing us to parse the connection.

Now the solution node contains sort of a connection matrix for all the tools on the canvas, as shown:

The Connections matrix likely contains N-1 entries, depending on the complexity of the workflow.

Procesing the Connection in the workflow highlights how to retrieve this information, perhaps a macro with this concept could process complex workflows.

Attaching:

Sample workflow: Xml_to_Excel.yxmd.
Get_Workflow_Out_2_Excel.yxmd - this process the XML_to_Excel Alteryx workflow, it is a very raw version.

hth

Arnaldo

Xml_to_Excel.yxmd

Get_Workflow_Out_2_Excel.yxmd

rohit782192

Not clear how you are taking a .txt file as XML and also how calling yxmd.

ArnaldoSandoval

Hi @rohit782192 Can you add the screenshot? please

Arnaldo

rohit782192

I have check your workflow only. if you open the workflow in can see in input data tool as .txt as input in one workflow and .yxmd file as input in another workflow.

I am confused how you have taken the input for the workflow.

rohit782192

You can consider these as a Simple Example.

<AlteryxDocument yxmdVer="2023.1" RunE2="T">
<Nodes>
<Node ToolID="8">
<GuiSettings Plugin="AlteryxGuiToolkit.ControlContainer.ControlContainer">
<Position x="88" y="53" width="498" height="317" />
</GuiSettings>
<Properties>
<Configuration>
<Caption>Test Example</Caption>
<Style TextColor="#314c4a" FillColor="#ecf2f2" BorderColor="#314c4a" Transparency="25" Margin="25" />
<Disabled value="False" />
<Folded value="False" />
</Configuration>
<Annotation DisplayMode="0">
<Name />
<AnnotationText>Test Example</AnnotationText>
<DefaultAnnotationText />
<Left value="False" />
</Annotation>
</Properties>
<ChildNodes>
<Node ToolID="2">
<GuiSettings Plugin="AlteryxBasePluginsGui.TextInput.TextInput">
<Position x="113" y="126" />
</GuiSettings>
<Properties>
<Configuration>
<NumRows value="4" />
<Fields>
<Field name="COUNTRYNAME" />
</Fields>
<Data>
<r>
<c>INDIA</c>
</r>
<r>
<c>LONDON</c>
</r>
<r>
<c>AMERICA</c>
</r>
<r>
<c>MUMBAI</c>
</r>
</Data>
</Configuration>
<Annotation DisplayMode="0">
<Name>Text Input 1</Name>
<DefaultAnnotationText>Text Input 1</DefaultAnnotationText>
<Left value="False" />
</Annotation>
<MetaInfo connection="Output">
<RecordInfo>
<Field name="COUNTRYNAME" size="2147483647" source="TextInput:" type="V_String" />
</RecordInfo>
</MetaInfo>
</Properties>
<EngineSettings EngineDll="AlteryxBasePluginsEngine.dll" EngineDllEntryPoint="AlteryxTextInput" />
</Node>
<Node ToolID="3">
<GuiSettings Plugin="AlteryxBasePluginsGui.TextInput.TextInput">
<Position x="114" y="246" />
</GuiSettings>
<Properties>
<Configuration>
<NumRows value="4" />
<Fields>
<Field name="COUNTRYNAME" />
</Fields>
<Data>
<r>
<c>INDIA</c>
</r>
<r>
<c>LONDON</c>
</r>
<r>
<c>AMERICA</c>
</r>
<r>
<c>DELHI</c>
</r>
</Data>
</Configuration>
<Annotation DisplayMode="0">
<Name>Text Input 2</Name>
<DefaultAnnotationText>Text Input 2</DefaultAnnotationText>
<Left value="False" />
</Annotation>
<MetaInfo connection="Output">
<RecordInfo>
<Field name="COUNTRYNAME" size="2147483647" source="TextInput:" type="V_String" />
</RecordInfo>
</MetaInfo>
</Properties>
<EngineSettings EngineDll="AlteryxBasePluginsEngine.dll" EngineDllEntryPoint="AlteryxTextInput" />
</Node>
<Node ToolID="4">
<GuiSettings Plugin="AlteryxBasePluginsGui.Join.Join">
<Position x="270" y="174" />
</GuiSettings>
<Properties>
<Configuration joinByRecordPos="False">
<JoinInfo connection="Left">
<Field field="COUNTRYNAME" />
</JoinInfo>
<JoinInfo connection="Right">
<Field field="COUNTRYNAME" />
</JoinInfo>
<SelectConfiguration>
<Configuration outputConnection="Join">
<OrderChanged value="False" />
<CommaDecimal value="False" />
<SelectFields>
<SelectField field="Right_COUNTRYNAME" selected="True" rename="Right_COUNTRYNAME" input="Right_" />
<SelectField field="*Unknown" selected="True" />
</SelectFields>
</Configuration>
</SelectConfiguration>
</Configuration>
<Annotation DisplayMode="0">
<Name>Join</Name>
<DefaultAnnotationText>Join</DefaultAnnotationText>
<Left value="False" />
</Annotation>
<MetaInfo connection="Left">
<RecordInfo>
<Field name="COUNTRYNAME" size="2147483647" source="TextInput:" type="V_String" />
</RecordInfo>
</MetaInfo>
<MetaInfo connection="Join">
<RecordInfo>
<Field name="COUNTRYNAME" size="2147483647" source="TextInput:" type="V_String" />
<Field name="Right_COUNTRYNAME" size="2147483647" source="TextInput:" type="V_String" />
</RecordInfo>
</MetaInfo>
<MetaInfo connection="Right">
<RecordInfo>
<Field name="COUNTRYNAME" size="2147483647" source="TextInput:" type="V_String" />
</RecordInfo>
</MetaInfo>
</Properties>
<EngineSettings EngineDll="AlteryxBasePluginsEngine.dll" EngineDllEntryPoint="AlteryxJoin" />
</Node>
<Node ToolID="5">
<GuiSettings Plugin="AlteryxBasePluginsGui.Union.Union">
<Position x="378" y="102" />
</GuiSettings>
<Properties>
<Configuration>
<ByName_ErrorMode>Warning</ByName_ErrorMode>
<ByName_OutputMode>All</ByName_OutputMode>
<Mode>ByName</Mode>
<SetOutputOrder value="False" />
</Configuration>
<Annotation DisplayMode="0">
<Name>Left Join</Name>
<DefaultAnnotationText>Left Join</DefaultAnnotationText>
<Left value="False" />
</Annotation>
<MetaInfo connection="Output">
<RecordInfo>
<Field name="COUNTRYNAME" size="2147483647" source="TextInput:" type="V_String" />
<Field name="Right_COUNTRYNAME" size="2147483647" source="TextInput:" type="V_String" />
</RecordInfo>
</MetaInfo>
</Properties>
<EngineSettings EngineDll="AlteryxBasePluginsEngine.dll" EngineDllEntryPoint="AlteryxUnion" />
</Node>
<Node ToolID="6">
<GuiSettings Plugin="AlteryxBasePluginsGui.Union.Union">
<Position x="390" y="270" />
</GuiSettings>
<Properties>
<Configuration>
<ByName_ErrorMode>Warning</ByName_ErrorMode>
<ByName_OutputMode>All</ByName_OutputMode>
<Mode>ByName</Mode>
<SetOutputOrder value="False" />
</Configuration>
<Annotation DisplayMode="0">
<Name>Right Join</Name>
<DefaultAnnotationText>Right Join</DefaultAnnotationText>
<Left value="False" />
</Annotation>
<MetaInfo connection="Output">
<RecordInfo>
<Field name="COUNTRYNAME" size="2147483647" source="TextInput:" type="V_String" />
<Field name="Right_COUNTRYNAME" size="2147483647" source="TextInput:" type="V_String" />
</RecordInfo>
</MetaInfo>
</Properties>
<EngineSettings EngineDll="AlteryxBasePluginsEngine.dll" EngineDllEntryPoint="AlteryxUnion" />
</Node>
<Node ToolID="7">
<GuiSettings Plugin="AlteryxBasePluginsGui.Union.Union">
<Position x="474" y="174" />
</GuiSettings>
<Properties>
<Configuration>
<ByName_ErrorMode>Warning</ByName_ErrorMode>
<ByName_OutputMode>All</ByName_OutputMode>
<Mode>ByName</Mode>
<SetOutputOrder value="False" />
</Configuration>
<Annotation DisplayMode="0">
<Name>Full Outer Join</Name>
<DefaultAnnotationText>Full Outer Join</DefaultAnnotationText>
<Left value="False" />
</Annotation>
<MetaInfo connection="Output">
<RecordInfo>
<Field name="COUNTRYNAME" size="2147483647" source="TextInput:" type="V_String" />
<Field name="Right_COUNTRYNAME" size="2147483647" source="TextInput:" type="V_String" />
</RecordInfo>
</MetaInfo>
</Properties>
<EngineSettings EngineDll="AlteryxBasePluginsEngine.dll" EngineDllEntryPoint="AlteryxUnion" />
</Node>
</ChildNodes>
<EngineSettings EngineDll="AlteryxBasePluginsEngine.dll" EngineDllEntryPoint="AlteryxCtrlContainer" />
</Node>
</Nodes>
<Connections>
<Connection>
<Origin ToolID="2" Connection="Output" />
<Destination ToolID="4" Connection="Left" />
</Connection>
<Connection>
<Origin ToolID="3" Connection="Output" />
<Destination ToolID="4" Connection="Right" />
</Connection>
<Connection name="#1">
<Origin ToolID="4" Connection="Left" />
<Destination ToolID="5" Connection="Input" />
</Connection>
<Connection name="#2">
<Origin ToolID="4" Connection="Join" />
<Destination ToolID="5" Connection="Input" />
</Connection>
<Connection name="#1">
<Origin ToolID="4" Connection="Right" />
<Destination ToolID="6" Connection="Input" />
</Connection>
<Connection name="#2">
<Origin ToolID="4" Connection="Join" />
<Destination ToolID="6" Connection="Input" />
</Connection>
<Connection name="#1">
<Origin ToolID="4" Connection="Left" />
<Destination ToolID="7" Connection="Input" />
</Connection>
<Connection name="#2">
<Origin ToolID="4" Connection="Join" />
<Destination ToolID="7" Connection="Input" />
</Connection>
<Connection name="#3">
<Origin ToolID="4" Connection="Right" />
<Destination ToolID="7" Connection="Input" />
</Connection>
</Connections>
<Properties>
<Memory default="True" />
<GlobalRecordLimit value="0" />
<TempFiles default="True" />
<Annotation on="True" includeToolName="False" />
<ConvErrorLimit value="10" />
<ConvErrorLimit_Stop value="False" />
<CancelOnError value="False" />
<DisableBrowse value="False" />
<EnablePerformanceProfiling value="True" />
<RunWithE2 value="True" />
<SortedGrouping value="True" />
<PredictiveToolsCodePage value="1252" />
<DisableAllOutput value="False" />
<ShowAllMacroMessages value="False" />
<ShowConnectionStatusIsOn value="True" />
<ShowConnectionStatusOnlyWhenRunning value="True" />
<ZoomLevel value="0" />
<LayoutType>Horizontal</LayoutType>
<MetaInfo>
<NameIsFileName value="True" />
<Name>JoinTestXML</Name>
<Description />
<RootToolName />
<ToolVersion />
<ToolInDb value="False" />
<CategoryName />
<SearchTags />
<Author />
<Company />
<Copyright />
<DescriptionLink actual="" displayed="" />
<Example>
<Description />
<File />
</Example>
<WorkflowId value="d3d4db0c-ee84-4750-a463-7bbc5bfb3408" />
<Telemetry>
<PreviousWorkflowId value="dc4d1cc9-cd4c-4685-a0b4-a7c4db2592e9" />
<OriginWorkflowId value="dc4d1cc9-cd4c-4685-a0b4-a7c4db2592e9" />
</Telemetry>
<PlatformWorkflowId value="" />
</MetaInfo>
<Events>
<Enabled value="True" />
</Events>
</Properties>
</AlteryxDocument>

ArnaldoSandoval

@rohit782192

The workflow shared with you read Alteryx yxmd files, e.g. workflow, you had not provide a clear description of the file type you are reading.

Your sample file's workflow is shown below:

None of the Alteryx's tools on its canvas are supported by the top half of my workflow, Alteryx has over 100 tools, I just coded logic for two or three of them, don't expect us to expend days coding all of them just for you 🤣

When ran using your workflow as data, the DbFileInput and Alteryx Select returned errors on empty data streams, once again, you should code the logic for the new tools.

Now this part processing the Connection tree should be expanded because your workflow includes more tools and obviously it has more connections between them, enhancing this module of the workflow is basically cut and paste. Note: This is not the most efficient way to handle this, a macro will do better, something you could do.

Arnaldo

rohit782192

I tried creating an Iterative macro by transposing it.

But the Workflow of Alteryx Sometimes does not give proper output.

caltang

Great share @ArnaldoSandoval ! I actually had a thought in my mind about this, but your method is way cooler/better. Will use it - thanks man!

rohit782192

Very Well Explained in a manual way.

rohit782192

Can you give some hint for macro.

I have created a Iterative Macro but i am only getting name and value.

ITERATIVE.JPG

jdminton

The name and value come from the transpose tool where you labeled "changes columns to rows". Add a crosstab tool at the end to flip them back if needed.

rohit782192

@jdminton Can you have a check on these.

XMLExtractorMacro.yxmc

TestMacroWorkflow.yxmd

rohit782192

Any luck on this.

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335