Converting multiple XML files to CSV files

IHAC which came up with a requirement that required all DVM files be changed to CSV format for their internal documentation. Although, it was a surprising requirement but indeed it had to be done.

This is how we can do it:--

1> Change file extension of *.dvm to *.xml

Use MS-DOS to change rename all the files in the current directory that end with .dvm to .xml.

ren *.dvm *.xml

2> Java program to change multiple .xml files to .csv files

package client;
import java.io.File;
import org.apache.xalan.xslt.Process;
public class xmltocsv {
           static int count=0;
   public static void main(String[] args) {

       String basedir="C:\\Documents and Settings\\TeamFusion\\Desktop\\dvm\\";
       String outdir="C:\\Documents and Settings\\TeamFusion\\Desktop\\csv\\";
       String input=null;
       String output=null;

       File file=new File("C:\\Documents and Settings\\TeamFusion\\Desktop\\dvm");
       String[] files=file.list();
       for(String filename: files) {
           input=basedir+filename;
           output=outdir+filename.replaceFirst("xml", "csv");
           convert(input,output);
           }
       System.out.println("Total number of files converted to CSV " +String.valueOf(count));
   }

public static void convert(String input,String output)
       {
       String[] abc={"-in",input,
                     "-xsl","C:\\Documents and Settings\\TeamFusion\\Desktop\\testdvmcsv.xsl",
                     "-out",output};
       try{
       Process.main(abc);
       }
       catch(Exception e) {
           System.out.println("Conversion failed " + input);
       }

           count++;
       }}

To run this java program one need to do following pre-requisites:--

1> Testdvmcsv.xsl needs to be placed in file-path accordingly.

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

<xsl:output method="text" encoding="utf-8" />

<xsl:param name="delim" select="','" />

<xsl:param name="quote" select="'"'" />

<xsl:param name="break" select="'
'" />

<xsl:template match="/">

<xsl:apply-templates select="ns:dvm/ns:columns" />

<xsl:apply-templates select="ns:dvm/ns:rows/ns:row" />

</xsl:template>

<xsl:template match="ns:columns">

<xsl:for-each select="ns:column">

<xsl:value-of select="@name"/>

<xsl:if test="position() != last()">

<xsl:value-of select="$delim"/>

</xsl:if>

</xsl:for-each>

<xsl:value-of select="$break" />

</xsl:template>

<xsl:template match="ns:row">

<xsl:apply-templates />

<xsl:if test="following-sibling::*">

<xsl:value-of select="$break" />

</xsl:if>

</xsl:template>

<xsl:template match="*">

<xsl:value-of select="concat($quote, normalize-space(), $quote)" />

<xsl:if test="following-sibling::*">

<xsl:value-of select="$delim" />

</xsl:if>

</xsl:template>

<xsl:template match="text()" />

</xsl:stylesheet>

2 2> You need to include the full path to .jar files in your CLASSPATH environment variable.

· xalan.jar

· xml-apis.jar

· xercesImpl.jar

·    xalan27.jar

Download these jars from:--

docbook-xsl-1.76.1.tar.bz2

              xalan-j_2_7_0-bin-2jars.tar.gz

To update your CLASSPATH on Linux, put these lines in your .profile file:

CLASSPATH=$CLASSPATH:/usr/Xalan/xalan.jar:/usr/Xalan/xml-apis.jar:\

/usr/Xalan/xercesImpl.jar:/usr/docbook-xsl/extensions/xalan27.jar

export CLASSPATH

On Windows, use the Control Panel to open the System icon, where you can set environment variables for Windows. Use semicolons instead of colons to separate items in the CLASSPATH.

You can tell the Xalan processor is working by running this command from cmd:

java org.apache.xalan.xslt.EnvironmentCheck

It reports on the Java environment and the version of Xalan. If you get a message about a class not found, then your CLASSPATH is not set up right to use Xalan.

Oracle Fusion Middleware

Main Menu

Thursday, March 8, 2012

Converting multiple XML files to CSV files

1 comments:

Post a Comment

Total Pageviews

Followers

Categories