Convert an XML file to CSV file using java (with multiple element values)

Question

I am working on parsing an XML to a CSV. Ran into an article @ Convert an XML file to CSV file using java and was able to verify the solution using the data.xml and style.xsl

Building on the xml data.xml, was wondering what changes would be needed on the style.xsl if mutiple values were present for some of the elements.

For example, if multiple OSs were present sometimes in the data.xml

<?xml version="1.0"?>
<Sites>
<Site id="101" name="NY-01" location="New York">
<Hosts>
   <Host id="1001">
   <Host_Name>srv001001</Host_Name>
   <IP_address>10.1.2.3</IP_address>
   <OS>Windows</OS>
   <Load_avg_1min>1.3</Load_avg_1min>
   <Load_avg_5min>2.5</Load_avg_5min>
   <Load_avg_15min>1.2</Load_avg_15min>
</Host>
<Host id="1002">
   <Host_Name>srv001002</Host_Name>
   <IP_address>10.1.2.4</IP_address>
   <OS>Linux, Windows</OS>
   <Load_avg_1min>1.4</Load_avg_1min>
   <Load_avg_5min>2.5</Load_avg_5min>
   <Load_avg_15min>1.2</Load_avg_15min>
</Host>
<Host id="1003">
   <Host_Name>srv001003</Host_Name>
   <IP_address>10.1.2.5</IP_address>
   <OS>Linux</OS>
   <Load_avg_1min>3.3</Load_avg_1min>
   <Load_avg_5min>1.6</Load_avg_5min>
   <Load_avg_15min>1.8</Load_avg_15min>
</Host>
<Host id="1004">
   <Host_Name>srv001004</Host_Name>
   <IP_address>10.1.2.6</IP_address>
   <OS>Linux, NetBSD</OS>
   <Load_avg_1min>2.3</Load_avg_1min>
   <Load_avg_5min>4.5</Load_avg_5min>
   <Load_avg_15min>4.2</Load_avg_15min>
</Host>

What do you mean by multiple values? What kind of output do you expect? — helderdarocha, May 09 '14 at 14:33
Following link seems to satisfy your requirements. http://stackoverflow.com/questions/3293371/convert-xml-file-to-csv-in-java — the-petrolhead, May 09 '14 at 14:34
Ah, you mean multiple comma-separated text values. You could replace the comma for some other separator using the `translate` function. — helderdarocha, May 09 '14 at 14:45

michael.hor257k · Accepted Answer · 2014-05-11T23:20:19.727

Try it this way?

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" 
xmlns:xsl="http://www.w3.org/1999/XSL/Transform" >
<xsl:output method="text" encoding="utf-8"/>

<xsl:template match="/">
    <xsl:text>Host_Name,IP_address,OS,Load_avg_1min,Load_avg_5min,Load_avg_15min&#10;</xsl:text>
    <xsl:for-each select="Sites/Site/Hosts/Host">
        <xsl:value-of select="concat(Host_Name,',',IP_address,',&quot;',OS,'&quot;,',Load_avg_1min,',',Load_avg_5min,',',Load_avg_15min,'&#10;')"/>
    </xsl:for-each>
</xsl:template>

</xsl:stylesheet>

Or, if you prefer:

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" 
xmlns:xsl="http://www.w3.org/1999/XSL/Transform" >
<xsl:output method="text" encoding="utf-8"/>

<xsl:template match="/">
    <xsl:text>Host_Name,IP_address,OS,Load_avg_1min,Load_avg_5min,Load_avg_15min&#10;</xsl:text>
    <xsl:for-each select="Sites/Site/Hosts/Host">
        <xsl:for-each select="*">
            <xsl:text>"</xsl:text>
            <xsl:value-of select="."/>
            <xsl:text>"</xsl:text>
            <xsl:if test="position()!=last()">
                <xsl:text>,</xsl:text>
            </xsl:if>
        </xsl:for-each>
        <xsl:if test="position()!=last()">
            <xsl:text>&#10;</xsl:text>
        </xsl:if>
    </xsl:for-each>
</xsl:template>

</xsl:stylesheet>

Note that both assume none of your "fields" contains a " quotation mark.

score 0 · Answer 2 · answered May 09 '14 at 14:47

0

If you have comma-separated values in an element, you can replace it when transforming using translate(node, character-to-be-replaced, replacement-character):

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">

    <xsl:output method="text"/>
    <xsl:strip-space elements="*"/>

    <xsl:template match="Host">
        <xsl:value-of select="ancestor::Site/@id"/><xsl:text>, </xsl:text>
        ...

        <xsl:value-of select="@id"/><xsl:text>, </xsl:text>
        <xsl:value-of select="Host_Name"/><xsl:text>, </xsl:text>
        <xsl:value-of select="IP_address"/><xsl:text>, </xsl:text>
        <xsl:value-of select="translate(OS, ',', ';')"/><xsl:text>, </xsl:text>
        ...
    </xsl:template>

</xsl:stylesheet>

You can use it for all elements that might have commas, since if the comma is not found, the text will be copied without any changes.

answered May 09 '14 at 14:47

helderdarocha

23,209
4
50
65

Thanks, will try it over the weekend and get back. – eugthom May 09 '14 at 16:16
1

It's not necessary to replace the commas with another delimiter. CSV is perfectly capable of carrying payload that contains commas - you just need to enclose the value in double quotes. Since any value *may* be quoted, you can just make that your standard. – michael.hor257k May 09 '14 at 18:36
I'm a noob (read pathetic) when it comes to XSL. Tried to implement your XSL logic, but failing miserably. Would you mind providing a working XSL ? – eugthom May 09 '14 at 21:42
michael.hor257k, I would like to tinker as less as possible to not at all with the data.xml – eugthom May 09 '14 at 21:43
@eugthom Please edit your question and add (1) an example of **your own XML source** (2) the **exact** result you expect to get and (3) your current XSLT stylesheet. -- P.S. You need to use the @ character when addressing your comments - otherwise the recipient does not get notified. – michael.hor257k May 10 '14 at 16:47
@michael.hor257k, you can find the above files @ [link](https://www.dropbox.com/sh/drmoeqbgy0oq2d8/AACYP3CW-Nsjl2RIuUzu7Kija) – eugthom May 11 '14 at 22:27
@helderdarocha, forgot to add you to the comment thread. I've attached some sample files for reference, if it helps... – eugthom May 11 '14 at 22:34
@eugthom You should **edit** your question (click the *edit* link under it, and use the editor to update your question, pasting the code and formatting it with the editor tools). – helderdarocha May 11 '14 at 22:42

Convert an XML file to CSV file using java (with multiple element values)

2 Answers2