I need help understanding the steps involved in converting an XML file into a CSV file using java. Here is an example of an XML file
<?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</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</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>
</Hosts>
</Site>
</Sites>
and here is the resulting CSV file.
site_id, site_name, site_location, host_id, host_name, ip_address, operative_system, load_avg_1min, load_avg_5min, load_avg_15min
101, NY-01, New York, 1001, srv001001, 10.1.2.3, Windows, 1.3, 2.5, 1.2
101, NY-01, New York, 1002, srv001002, 10.1.2.4, Linux, 1.4, 2.5, 1.2
101, NY-01, New York, 1003, srv001003, 10.1.2.5, Linux, 3.3, 1.6, 1.8
101, NY-01, New York, 1004, srv001004, 10.1.2.6, Linux, 2.3, 4.5, 4.2
I was thinking of using a DOM parser to read the xml file. The problem I have with that is I would need to specify specific elements in to code by name, but I want it to be able to parse it without doing that.
Are there any tools or libraries in java that would be able to help me achieve this.
If I have a XML file of this format below and want to add the value of the InitgPty in the same row with MSgId (Pls note :InitgPty is in the next tag level, so it prints the value in the next row)
<?xml version="1.0"?>
<CstmrCdtTrfInitn>
<GrpHdr>
<MsgId>XYZ07/ABC</MsgId>
<NbOfTxs>100000</NbOfTxs>
<InitgPty>
<Nm>XYZ</Nm>
</InitgPty>
here's a working example, data.xml has your data:
import java.io.File;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.transform.Result;
import javax.xml.transform.Source;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;
import javax.xml.transform.stream.StreamSource;
import org.w3c.dom.Document;
class Xml2Csv {
public static void main(String args[]) throws Exception {
File stylesheet = new File("src/main/resources/style.xsl");
File xmlSource = new File("src/main/resources/data.xml");
DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
DocumentBuilder builder = factory.newDocumentBuilder();
Document document = builder.parse(xmlSource);
StreamSource stylesource = new StreamSource(stylesheet);
Transformer transformer = TransformerFactory.newInstance()
.newTransformer(stylesource);
Source source = new DOMSource(document);
Result outputTarget = new StreamResult(new File("/tmp/x.csv"));
transformer.transform(source, outputTarget);
}
}
style.xsl
<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:fo="http://www.w3.org/1999/XSL/Format" >
<xsl:output method="text" omit-xml-declaration="yes" indent="no"/>
<xsl:template match="/">
Host_Name,IP_address,OS,Load_avg_1min,Load_avg_5min,Load_avg_15min
<xsl:for-each select="//Host">
<xsl:value-of select="concat(Host_Name,',',IP_address,',',OS,Load_avg_1min,',',Load_avg_5min,',',Load_avg_15min,'
')"/>
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>
output:
Host_Name,IP_address,OS,Load_avg_1min,Load_avg_5min,Load_avg_15min
srv001001,10.1.2.3,Windows1.3,2.5,1.2
srv001002,10.1.2.4,Linux1.4,2.5,1.2
srv001003,10.1.2.5,Linux3.3,1.6,1.8
srv001004,10.1.2.6,Linux2.3,4.5,4.2
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments