Need to Process XML Spreadsheet

[Monica_K69 (Monica K)]

I have an xml Spreadsheet, I need to read and process it but couldn’t able to read and process. How can i do it?

[Lak_Ui (Lakshmi)]

1. use read text file activity
2. use deserialize xml activity to process the file

[Monica_K69 (Monica K)]

I have already tried but couldn’t able to process. any alternate solution?

[supermanPunch (Arpan)]

Hi @Monica_K69 ,

We would like to receive a Sample of your Xml Spreadsheet data if possible. So that we could understand what maybe the different ways to convert it into Excel. Is it possible to provide a Sample Input Data ?

[Monica_K69 (Monica K)]

SettleMaster125128.xml (29.7 KB)
I cannot read this file as this extension is not supported. Please do let me know if we have any other alternate solution.

[ppr (Peter Preuss)]

Quick Prototype result:

Variables and Namespace Setup:

We are tricking the datatable conversion with the help of LINQ and JSON

arrJObjects =

(From row In xDoc.Root.Descendants(xnsDefault + "Table").First().Descendants(xnsDefault+"Row")
Where row.Elements().Count > 5
Let JPrps = row.Elements(xnsDefault + "Cell").Select(Function (x,i) New JProperty("Col" & i.toString, x.Value)).toArray
Select jo = New JObject(JPrps)).toArray

We filter for rows having more then 5 cells
then we are creating JProperties from the cell and its value
we convert it into a JObject
And return it as an array of JObjects

dtResult =
JSonConvert.DeserializeObject(Of DataTable)(JsonConvert.SerializeObject(arrJObjects))

Serializing arrJObject to JSON and desrializing it to datatable will create us the datatable

Next Things you can do: Handling the first row and assign it as column headers

Also have a look here:
Handling XML Namespaces:

XML Extraction - Handling Namespaces (Tableau, Soap, OASIS types)

Going ahead Serializing the XML - output: xDoc | DataType: XDocument: [grafik] Checking if we have to handle XML namespaces [grafik] We do have: [grafik] Checking for some children: [grafik] Defining Namespace Variable [grafik] In some cases, we can also do: Assign Activity - xnsDefault = xDoc.Root.Name.Namespace Checkin the retrieval [grafik] xDoc.Descendants(xnsDefault + "productVersion") GetDescendants { [<productVersion build="456" xmlns="http://tableau.com/api">123</…

LINQ Starter

[HowTo] - First Start with LINQ (VB.Net)

LINQ is part of the .NET framework and offers capabilities for interacting with data, objects or other sources within a particular syntax. This HowTo introduces on starting with LINQ (Language-Integrated Query). Current Version of this topic is DRAFT and will be finalized / enhanced soon Introduction Typical tasks for implementing parts of an automation project are interacting with e.g. with data in a datatable or items from collections. LINQ can help to implement it into a compact and standa…

Find starter help here:
ppr_XMLBox_SpreadSheetXML.xaml (8.3 KB)

[Monica_K69 (Monica K)]

Thank you, It has worked for me.