1

Im trying to convert xml data to CSV. How do I add COMMA as data/text in CSV?
I couldn't use COMMA as separator since CSV is comma delimited file. Please help me to generate data as below: 

rowId   data
1   [xValue,yValue,zValue]
2   [xValue,yValue,zValue]

Some sample data:

<parent>
<c1>
<c2 x=1 y=2 z=3>
</c2>
</c1>
</parent>

My present xsl extracting the xml data is : Adding a COMMA as separator or text is delimiting the data in file. Please suggest the right way. 

            <xsl:for-each select="childlevel1/childlevel2">
                <xsl:value-of select="concat('','[')"/>
                <xsl:value-of select="@x"/>
                <xsl:value-of select="@y"/>
                <xsl:value-of select="@z"/>
                <xsl:value-of select="concat(']','')"/>
            </xsl:for-each>
Shawn
  • 47,241
  • 3
  • 26
  • 60
Sindhu g
  • 107
  • 2
  • 10

1 Answers1

2

If your actual data has a comma in, which you don't want to treat as a delimiter, you need to put quote marks around that field.

Try this XSLT:

<xsl:template match="parent">
    <xsl:text>rowId,data&#10;</xsl:text>
    <xsl:for-each select="childlevel1/childlevel2">
        <xsl:number />
        <xsl:text>,</xsl:text>
        <xsl:value-of select="concat('&quot;[',@x,',',@y,',',@z,']&quot;')"/>
        <xsl:text>&#10;</xsl:text>
    </xsl:for-each>
</xsl:template>

When you apply it to this XML

<parent>
    <childlevel1>
        <childlevel2 x="1" y="2" z="3" />
        <childlevel2 x="11" y="12" z="13" />
    </childlevel1>
</parent>

The following CSV is output

rowId,data
1,"[1,2,3]"
2,"[11,12,13]"

When you open this in your favourite Spreadsheet (like Microsoft Excel, or OpenOffice), it should only have two columns.

Tim C
  • 70,053
  • 14
  • 74
  • 93
  • Thank you for the response @Tim. Your code works adding the comma as data, one last thing I need is, All the ChildLevel2 should go in one row like [[1,2,3],[11,12,13]]. Please help – Sindhu g May 29 '18 at 18:10
  • It's probably best to ask a whole new question about that. Make sure you include a valid sample of your XML and your expected output. Thanks. – Tim C May 29 '18 at 19:24
  • I have added it as new question. Please refer to https://stackoverflow.com/questions/50593998/add-comma-as-delimiter-when-creating-array-of-arrays-as-one-field-in-csv-from-xm – Sindhu g May 29 '18 at 22:42
  • Hi Tim, Can you please help with the above issue. – Sindhu g May 30 '18 at 16:42