Extracting metadata from an excel
-
- Newbie
- Posts: 6
- Joined: Wed Feb 21, 2018 4:16 pm
Extracting metadata from an excel
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 (65.91 KiB) Viewed 8023 times
Re: Extracting metadata from an excel
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
//*[@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
Re: Extracting metadata from an excel
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>
------
----
<?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>
------
-
- Newbie
- Posts: 6
- Joined: Fri Mar 16, 2018 12:33 pm
Re: Extracting metadata from an excel
@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.
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.
Re: Extracting metadata from an excel
Thanks, this is great help.
Of course I asked for Gilmore's benefit
Of course I asked for Gilmore's benefit
Re: Extracting metadata from an excel
THANK YOU, THANK YOU, THANK YOU.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.
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
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...