XML2CSV

Post Reply
Krzysztof Mycek
Member
Posts: 30
Joined: Tue Mar 03, 2020 11:28 am

XML2CSV

Post 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?
jan_suhr
Advanced member
Posts: 586
Joined: Fri Nov 04, 2011 1:12 pm
Location: Nyköping, Sweden

Re: XML2CSV

Post by jan_suhr »

Jan Suhr
Color Consult AB
Sweden
=============
Check out my apps
Krzysztof Mycek
Member
Posts: 30
Joined: Tue Mar 03, 2020 11:28 am

Re: XML2CSV

Post 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.
Krzysztof Mycek
Member
Posts: 30
Joined: Tue Mar 03, 2020 11:28 am

Re: XML2CSV

Post by Krzysztof Mycek »

But ... ok I see. I will try it.
jan_suhr
Advanced member
Posts: 586
Joined: Fri Nov 04, 2011 1:12 pm
Location: Nyköping, Sweden

Re: XML2CSV

Post by jan_suhr »

You can always use XSLT with the Saxonica configurator. It will fix it
Jan Suhr
Color Consult AB
Sweden
=============
Check out my apps
Krzysztof Mycek
Member
Posts: 30
Joined: Tue Mar 03, 2020 11:28 am

Re: XML2CSV

Post 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);
	
		
}}
loicaigon
Advanced member
Posts: 357
Joined: Wed Jul 10, 2013 10:22 am

Re: XML2CSV

Post 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() &lt; last()">
<xsl:text>
</xsl:text>
                </xsl:if>
            </xsl:for-each>
        </xsl:if>
    </xsl:template>
</xsl:stylesheet>
Image
But you can certainly create your own script.
Krzysztof Mycek
Member
Posts: 30
Joined: Tue Mar 03, 2020 11:28 am

Re: XML2CSV

Post 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
Post Reply