How can we convert XML file to CSV?

Question

I am having an XML file

<?xml version="1.0" encoding="ISO-8859-1"?>
<Results>
    <Row>
        <COL1></COL1>
        <COL2>25.00</COL2>
        <COL3>2009-07-06 15:49:34.984</COL3>
        <COL4>00001720</COL4>
    </Row>
    <Row>
        <COL1>RJ</COL1>
        <COL2>26.00</COL2>
        <COL3>2009-07-06 16:04:16.156</COL3>
        <COL4>00001729</COL4>
    </Row>
    <Row>
        <COL1>SD</COL1>
        <COL2>28.00</COL2>
        <COL3>2009-07-06 16:05:04.375</COL3>
        <COL4>00001721</COL4>
    </Row>  
</Results>

I have to convert this XML into CSV file. I have heard we can do such thing using XSLT. How can i do this in Java ( with/without XSLT )?

score 5 · Answer 1 · answered Jul 06 '09 at 13:16

5

Using XSLT is often a bad idea. Use Apache Commons Digester. It's fairly easy to use - here's a rough idea::

Digester digester = new Digester();

digester.addObjectCreate("Results/Row", MyRowHolder.class);
digester.addCallMethod("Results/Row/COL1","addCol", 0);
// Similarly for COL2, etc.
digester.parse("mydata.xml");

This will create a MyRowHolder instance (where this is a class you provide). This class would have a addCol() method which would be called for each <COLn> with the contents of that tag.

answered Jul 06 '09 at 13:16

Vinay Sajip

95,872
14
179
191

"Using XSLT is often a bad idea" - May I ask why? :) – Tomalak Jul 06 '09 at 13:17
Plus, the poster asked how to do it in Java WITHOUT XSLT. So I'm not sure why I got down-voted :-( – Vinay Sajip Jul 06 '09 at 13:21
Digester is underused. +1 for this – Brian Agnew Jul 06 '09 at 13:43
@Vinay Sajip: Agreed, XSLT is a bit harder to debug. However, converting the above to CSV is so trivial that the required XSLT would probably not need much debugging anyway. – Tomalak Jul 06 '09 at 13:59
@Vinay Sajip: Also, the question was with OR without XSLT, and generally seems to be in favor of XSLT even. ;-) – Tomalak Jul 06 '09 at 14:01
@Tomalak - You're right, I must've read "with/without" as "without". – Vinay Sajip Jul 06 '09 at 14:12

geowa4 · Accepted Answer · 2009-07-06T13:24:29.130

4

In pseudo code:

loop through the rows:
    loop through all children of `Row`:
        write out the text
        append a comma
    new line

That quick little loop will write a comma at the end of each line, but I'm sure you can figure out how to remove that.

For actually parsing the XML, I suggest using JDOM. It has a pretty intuitive API.

edited Jul 06 '09 at 13:24

answered Jul 06 '09 at 13:07

geowa4

40,390
17
88
107

I think the issue is the understanding of how to parse the XML, not so much the writing of the resultant values – Brian Agnew Jul 06 '09 at 13:22
3

You forgot escaping (what if there is a comma in the data?). – bortzmeyer Jul 06 '09 at 15:19

Tomalak · Answer 3 · 2009-07-06T13:23:42.407

In XSLT 1.0:

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

  <xsl:output method="text" encoding="ISO-8859-1" />

  <xsl:template match="/Results">
    <xsl:apply-templates select="Row" />  
  </xsl:template>

  <xsl:template match="Row">
    <xsl:apply-templates select="*" />  
    <xsl:if test="not(last())">
      <xsl:value-of select="'&#10;'" />  
    </xsl:if>
  </xsl:template>

  <xsl:template match="Row/*">
    <xsl:value-of select="." />
    <xsl:if test="not(last())">
      <xsl:value-of select="','" />  
    </xsl:if>
  </xsl:template>

</xsl:stylesheet>

If your COL* values can contain commas, you could wrap the values in double quotes:

  <xsl:template match="Row/*">
    <xsl:value-of select="concat('"', ., '"')" />
    <!-- ... --->

If they can contain commas and double quotes, things could get a bit more complex due to the required escaping. You know your data, you'll be able to decide how to best format the output. Using a different separator (e.g. TAB or a pipe symbol) is also an option.

P.S.: I'll leave it as an exercise for the reader to find sample that shows how to use XSLT from within Java. It's not hard. :) — Tomalak, Jul 06 '09 at 13:58

Gerco Dries · Answer 4 · 2009-07-06T12:59:39.043

1

With XSLT you can use the JAXP interface to the XSLT processor and then use <xsl:text> in your stylesheet to convert to text output.

<xsl:text>&#10;</xsl:text>

generates a newline. for example.

edited Jul 06 '09 at 12:59

answered Jul 06 '09 at 12:58

Gerco Dries

6,682
1
26
35

score 1 · Answer 5 · answered Jul 06 '09 at 12:58

1

Read the XML file in.

Loop throught each record and add it to a csv file.

answered Jul 06 '09 at 12:58

Derek Organ

8,323
17
56
75

Agreed. Using XSLT in this situation is overkill. No need to learn a new language just to output CSV from a format this simple. – Welbog Jul 06 '09 at 12:59
and how we will do that Derek – Rakesh Juyal Jul 06 '09 at 13:02
3

I don't think this is desperately helpful if you're not familiar with the available XML apis :-( – Brian Agnew Jul 06 '09 at 13:11
You forgot escaping (what if there is a comma in the data?). – bortzmeyer Jul 06 '09 at 15:20

Brian Agnew · Answer 6 · 2009-07-06T13:22:58.227

1

Use the straightforward SAX API via the standard Java JAXP package. This will allow you to write a class that receives events for each XML element your reader encounters.

Briefly:

read your XML in using SAX
record text values via the SAX DefaultHandler characters() method
when you get an end event for a COL, record this string value
when you get the ROW end event, simply write out a comma separated line of previously recorded values

edited Jul 06 '09 at 13:22

answered Jul 06 '09 at 13:08

Brian Agnew

268,207
37
334
440

@Brian: If it is possible, please give the example. – Rakesh Juyal Jul 06 '09 at 13:11
I'd have a look at the tutorials linked, and implement a simple DefaultHandler. When you run it, you'll see (in a debugger, or via print outs) how the event methods are called, and that should make it clear. Sorry - I can't easily post a sample – Brian Agnew Jul 06 '09 at 13:12

How can we convert XML file to CSV?

6 Answers6

Linked