Page 1 of 1
XML2CSV
Posted: Wed Mar 15, 2023 10:35 am
by Krzysztof Mycek
Hi I need to convert some XML data to CSV.
For Example:
<properties>
<property>
<value1 label="W_1_STR_A">BRZOZA1_100x70_spoady_2i5mm</value1>
<value2 label="W_2_STR_A">BRZOZA2_100x70_spoady_2i5mm</value2>
<value3 label="W_3_STR_A">DYWERGENCJA_70x100_spady_2i5mm</value3>
<value4 label="W_4_STR_A">KONWERGENCJA_70x100_spady_2i5mm</value4>
<value5 label="W_5_STR_A">SWIATLOWOD_70x100_spady_2i5mm</value5>
</property>
<property>
<value1 label="WARIANT_1">1</value1>
<value2 label="WARIANT_2">2</value2>
<value3 label="WARIANT_3">3</value3>
<value4 label="WARIANT_4">4</value4>
<value5 label="WARIANT_5">5</value5>
</property>
</properties>
I need CSV like:
W_1_STR_A">BRZOZA1_100x70_spoady_2i5mm</value1;1;
W_2_STR_A">BRZOZA2_100x70_spoady_2i5mm</value2;2;
W_3_STR_A">DYWERGENCJA_70x100_spady_2i5mm;3;
KONWERGENCJA_70x100_spady_2i5mm;4;
SWIATLOWOD_70x100_spady_2i5mm;5;
Maybe someone has a solution on how to do this in Switch?
Re: XML2CSV
Posted: Wed Mar 15, 2023 10:43 am
by jan_suhr
Re: XML2CSV
Posted: Wed Mar 15, 2023 11:45 am
by Krzysztof Mycek
One variables that is not a problem but I never know how many variables I will have it.
I need to read XML, aggregate variables and prepare on the finish CSV file.
Re: XML2CSV
Posted: Wed Mar 15, 2023 11:47 am
by Krzysztof Mycek
But ... ok I see. I will try it.
Re: XML2CSV
Posted: Wed Mar 15, 2023 12:58 pm
by jan_suhr
You can always use XSLT with the Saxonica configurator. It will fix it
Re: XML2CSV
Posted: Wed Mar 15, 2023 1:01 pm
by Krzysztof Mycek
Jan
that is the script expression witch read the data from XML. But how I can send it by dynamic properly in this connector??
I have always Dynamic property 'tagFileContents' has invalid value ''
Code: Select all
{
var dataset = job.getDataset("Xml");
var xml = new Document(dataset.getPath());
var name = job.getNameProper();
var nodeList = xml.evalToNodes("/properties/property[3]/*"); // Sprawdzamy ile mamy nodeów z nazwami plików (pierwszy jest śmieciem)
var nodeList2 = xml.evalToNodes("/properties/property[9]/*"); // Sprawdzamy ile mamy nodeów z ilościami (pierwszy jest śmieciem)
var nodeList3 = xml.evalToNodes("/properties/property[3]//@label"); // Sprawdzamy ile mamy labelek
var wysokosc_XML = xml.evalToString("/properties/property[6]/value[1]"); //odczytanie wysokosci z XML
var szerokosc_XML = xml.evalToString("/properties/property[6]/value[2]"); //odczytanie szerokosci z XML
s.log(1, "Found: " + nodeList.length);
s.log(1, "Found: " + nodeList2.length);
s.log(1, "Found: " + nodeList3.length);
for (var i = 1; i < nodeList.length; i++) {
var node = nodeList.at(i);
var node2 = nodeList2.at(i);
var node3 = nodeList3.at(i);
var code = node.hasChildNodes() ? node.getFirstChild().getValue() : "";
var code2 = node2.hasChildNodes() ? node2.getFirstChild().getValue() : "";
var code3 = node.getAttributeValue("label");
s.log(2, "value " + i + " = " + code);
s.log(3, "value " + i + " = " + code2);
s.log(3, "value " + i + " = " + code3);
s.log(2, "wysokosc " + "=" + wysokosc_XML);
s.log(2, "szerokosc " + "=" + szerokosc_XML);
}}
Re: XML2CSV
Posted: Wed Mar 15, 2023 3:07 pm
by loicaigon
I would second Jan. Point is with XSLT, you wouldn't use script expression but XSLT syntax to output the expected CSV.
For example, based on your input, the following XSLT would do:
Code: Select all
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
exclude-result-prefixes="xs"
version="1.0">
<xsl:output method="text" encoding="UTF-8" indent="no"/>
<xsl:template match="/">
<xsl:apply-templates select="properties"></xsl:apply-templates>
</xsl:template>
<xsl:template match="properties">
<xsl:apply-templates select="property"></xsl:apply-templates>
</xsl:template>
<xsl:template match="property">
<xsl:if test="starts-with(./*[1]/@label, 'W_')">
<xsl:for-each select="./*">
<xsl:variable name="nodeName"><xsl:value-of select="name(.)"></xsl:value-of></xsl:variable>
<xsl:variable name="nodeNameCount"><xsl:value-of select="string-length($nodeName)"/></xsl:variable>
<xsl:variable name="index">
<xsl:value-of select="substring($nodeName, $nodeNameCount)"/>
</xsl:variable>
<xsl:value-of select="."/>;<xsl:value-of select="$index"/>
<xsl:if test="position() < last()">
<xsl:text>
</xsl:text>
</xsl:if>
</xsl:for-each>
</xsl:if>
</xsl:template>
</xsl:stylesheet>
But you can certainly create your own script.
Re: XML2CSV
Posted: Mon Mar 20, 2023 2:15 pm
by Krzysztof Mycek
The solution is javascript in which we take the data from the XML and then send it all to privatedata. Connector create text file only creates a csv file