Extracting metadata from an excel

Post Reply
Gilmore Buckets
Newbie
Posts: 6
Joined: Wed Feb 21, 2018 4:16 pm

Extracting metadata from an excel

Post by Gilmore Buckets »

I converted an excel to an xml and trying to build a metadata path xml or xpath to extract information using all the rows associated with a column. The issue I'm having is I need some kind of variable or expression to use for the different rows, while the column stays constant at 3.
Attachments
Build Metadata Location.png
Build Metadata Location.png (65.91 KiB) Viewed 8000 times
loicaigon
Advanced member
Posts: 363
Joined: Wed Jul 10, 2013 10:22 am

Re: Extracting metadata from an excel

Post by loicaigon »

What is the XML structure exactly. What's best with XPath is not to use index but filters that will spot the interesting elements like:
//*[@id=123]
This would return any element WHERE id attribute equals 123 whatever its location inside the structure.
Could this be applied in your case ?
Loic
Zoranj
Member
Posts: 107
Joined: Tue Sep 20, 2016 7:37 pm
Location: Canada

Re: Extracting metadata from an excel

Post by Zoranj »

Not to hijack the thread, but might be helpful for GIlmore as well, Loic how would you pick with Xpath all "Impressions" from this XML:
----
<?xml version="1.0" encoding="UTF-8"?>
<Log>
<DataSource>lmerp</DataSource>
<SQLStatement>SELECT * FROM ProductionJobInfo WHERE SwitchReadStatus = 1 And ERPJobID = 10173</SQLStatement>
<SQLStatementResult>Success</SQLStatementResult>
<SQLStatementValue>
<Row>
<Column Name="ID" DataType="BINARY">0k/8injVxUyc7A2p3qZrGw==</Column>
<Column Name="ERPJobID" DataType="INTEGER">10173</Column>
<Column Name="JobDescription" DataType="STRING">DRC - Brochures 3x Different</Column>
<Column Name="ERPJobComponent" DataType="STRING">3 Panel Brochure Spanish 6p - Digital Print F/B 4x4</Column>
<Column Name="Impressions" DataType="REAL">956</Column>
<Column Name="Customer" DataType="STRING">Storyart Communications</Column>
<Column Name="CSR" DataType="STRING">undefined</Column>
<Column Name="SalesAgent" DataType="STRING">Doug Osborne</Column>
<Column Name="DeptID" DataType="INTEGER">118</Column>
<Column Name="ShipBy" DataType="DATETIME">2017-12-08T13:40:00Z</Column>
<Column Name="Source" DataType="STRING">LM_CDPService</Column>
<Column Name="SwitchReadStatus" DataType="INTEGER">1</Column>
<Column Name="LastUpdated" DataType="DATETIME">2018-02-27T14:16:00Z</Column>
</Row>
<Row>
<Column Name="ID" DataType="BINARY">gcbt+nJfqkOa1oQqDlq5/Q==</Column>
<Column Name="ERPJobID" DataType="INTEGER">10173</Column>
<Column Name="JobDescription" DataType="STRING">DRC - Brochures 3x Different</Column>
<Column Name="ERPJobComponent" DataType="STRING">3 Panel Brochure Eng 6p - Digital Print F/B 4x4</Column>
<Column Name="Impressions" DataType="REAL">956</Column>
<Column Name="Customer" DataType="STRING">Storyart Communications</Column>
<Column Name="CSR" DataType="STRING">undefined</Column>
<Column Name="SalesAgent" DataType="STRING">Doug Osborne</Column>
<Column Name="DeptID" DataType="INTEGER">118</Column>
<Column Name="ShipBy" DataType="DATETIME">2017-12-08T13:40:00Z</Column>
<Column Name="Source" DataType="STRING">LM_CDPService</Column>
<Column Name="SwitchReadStatus" DataType="INTEGER">1</Column>
<Column Name="LastUpdated" DataType="DATETIME">2018-02-27T14:16:26Z</Column>
</Row>
<Row>
<Column Name="ID" DataType="BINARY">tUEugKrfj0u7rd4hVvfdrQ==</Column>
<Column Name="ERPJobID" DataType="INTEGER">10173</Column>
<Column Name="JobDescription" DataType="STRING">DRC - Brochures 3x Different</Column>
<Column Name="ERPJobComponent" DataType="STRING">3 Panel Brochure Fre 6p - Digital Print F/B 4x4</Column>
<Column Name="Impressions" DataType="REAL">956</Column>
<Column Name="Customer" DataType="STRING">Storyart Communications</Column>
<Column Name="CSR" DataType="STRING">undefined</Column>
<Column Name="SalesAgent" DataType="STRING">Doug Osborne</Column>
<Column Name="DeptID" DataType="INTEGER">118</Column>
<Column Name="ShipBy" DataType="DATETIME">2017-12-08T13:40:00Z</Column>
<Column Name="Source" DataType="STRING">LM_CDPService</Column>
<Column Name="SwitchReadStatus" DataType="INTEGER">1</Column>
<Column Name="LastUpdated" DataType="DATETIME">2018-02-27T14:15:59Z</Column>
</Row>
</SQLStatementValue>
</Log>
------
allsystems
Newbie
Posts: 6
Joined: Fri Mar 16, 2018 12:33 pm

Re: Extracting metadata from an excel

Post by allsystems »

@zoranj
The value of "Impressions in a specific Node
/Log/SQLStatementValue/Row[3]/Column[5][@Name="Impressions"]

A list of all of the values
[Metadata.TextIndexed:Path="/Log/SQLStatementValue/Row/Column[@Name='Impressions']",Dataset="Xml",Model="XML"]

A sum of the values
[Metadata.Integer:Path="sum(/Log/SQLStatementValue/Row/Column[@Name='Impressions'])",Dataset="Xml",Model="XML"]

Hopefully this helps GiIlmore too. ;-)
Zoranj
Member
Posts: 107
Joined: Tue Sep 20, 2016 7:37 pm
Location: Canada

Re: Extracting metadata from an excel

Post by Zoranj »

Thanks, this is great help.
Of course I asked for Gilmore's benefit ;) :lol:
Arthur
Member
Posts: 113
Joined: Sat Sep 09, 2017 11:58 pm
Location: Yateley, UK

Re: Extracting metadata from an excel

Post by Arthur »

allsystems wrote:@zoranj
The value of "Impressions in a specific Node
/Log/SQLStatementValue/Row[3]/Column[5][@Name="Impressions"]

A list of all of the values
[Metadata.TextIndexed:Path="/Log/SQLStatementValue/Row/Column[@Name='Impressions']",Dataset="Xml",Model="XML"]

A sum of the values
[Metadata.Integer:Path="sum(/Log/SQLStatementValue/Row/Column[@Name='Impressions'])",Dataset="Xml",Model="XML"]

Hopefully this helps GiIlmore too. ;-)
THANK YOU, THANK YOU, THANK YOU.
I finally understand where I kept making a mistake defining XPath and it never worked, so I had to go with index as Switch automaticaly sugests :roll:
All the flows now amended to look for the XPath value predicates /col[@name='blablabla'] :):):)
Sometimes the easiest and most straightforward things hapen to be the most difficult...
Post Reply