CSV to XML converter using Groovy
csv, groovy, xml
Solution
So, assuming the example CSV you put in the question is in a file called `"mycsv.csv"`, you can do this:
@Grab('com.xlson.groovycsv:groovycsv:1.0')
import static com.xlson.groovycsv.CsvParser.parseCsv
import groovy.xml.*
println XmlUtil.serialize( new File( 'mycsv.csv' ).withReader { r ->
def csvParser = parseCsv( r, separator:'\t' )
new StreamingMarkupBuilder().bind {
XMLCreators {
csvParser.each { line ->
row {
job( line[ 'Job' ] )
type( line[ 'Type' ] )
cntr( line[ 'Cntr Number' ] )
}
}
}
}
} )
Which prints out:
<?xml version="1.0" encoding="UTF-8"?><XMLCreators>
<row>
<job>JOB0001</job>
<type>Circle</type>
<cntr>12</cntr>
</row>
<row>
<job>JOB0002</job>
<type>Square</type>
<cntr>13</cntr>
</row>
<row>
<job>JOB0003</job>
<type>Cube</type>
<cntr>15</cntr>
</row>
</XMLCreators>
If you can't do the `@Grab` and you just need the jars to add to your classpath, then the main groovycsv jar (and its dependencies) are listed here
Edit, formatting in the class structure you detail above:
package com.navis.extension.tee;
import com.navis.argo.business.api.ArgoUtils
import com.navis.argo.business.api.GroovyApi
import static com.xlson.groovycsv.CsvParser.parseCsv
import groovy.xml.*
/**
* Converting CSV to XML.
*
* * Date: 24/01/14 : 11:50
* Called from: Groovy Job.
*/
public class CSVtoXML extends GroovyApi {
public void execute(Map param) {
new File( 'C:/convert/CRR_output.xml' ).withWriter { w ->
new File( 'C:/convert/CRR.csv' ).withReader { r ->
def csvParser = parseCsv( r , separator: ',')
String xml = new StreamingMarkupBuilder().bind {
XMLCreators {
csvParser.each { line ->
println line
if( line.size() ) {
row {
job( line[ 'Job' ] )
type( line[ 'Type' ] )
cntr( line[ 'Cntr Number' ] )
date( line[ 'Date' ] )
bref( line[ 'Booking Ref' ] )
comm( line[ 'Commodity' ] )
weig( line[ 'Weight' ] )
spci( line[ 'Special Instructions' ] )
}
}
}
}
}
w.writeLine( xml )
}
}
}
static main( args ) {
new CSVtoXML().execute( [:] )
}
}
Problem
I am at the end of my ability with what i am trying to achieve here, i need to create a converter using groovy to convert CSV to XML. My ability is very limited with groovy so the following code may be a pile of rubbish but it was put together using other pieces of code i have found. ``` package com.ns.extension.tee; import com.ns.argo.business.api.ArgoUtils import com.ns.argo.business.api.GroovyApi import org.apache.log4j.Logger import org.jdom.Document import org.jdom.Element 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 /** * Converting CSV to XML. * * * Date: 24/01/14 : 11:50 * Called from: Groovy Job. */ public class CSVtoXML extends GroovyApi { /** * Triggered from a Groovy Job. * @param param */ public void execute(Map param) { LOGGER.warn(String.format("At start of %s at %s", this.getClass().getName(), ArgoUtils.timeNow())); convertCSVToXML(); LOGGER.warn(String.format("At end of %s at %s", this.getClass().getName(), ArgoUtils.timeNow())); } /** * Loads and converts a file from CSV to XML. */ private void convertCSVToXML() { List<String> headers = new ArrayList<String>(5); String path = "C:\\convert\\"; path = path.replaceAll("\\", "/"); File file = new File(path, "CRR.csv"); BufferedReader reader = null; try { DocumentBuilderFactory domFactory = DocumentBuilderFactory.newInstance(); DocumentBuilder domBuilder = domFactory.newDocumentBuilder(); Document newDoc = domBuilder.newDocument() as Document; // Root element Element rootElement = newDoc.createElement("XMLCreators"); newDoc.appendChild(rootElement); reader = new BufferedReader(new FileReader(file)); int line = 0; String text = null; while ((text = reader.readLine()) != null) { StringTokenizer st = new StringTokenizer(text, " ", false); String[] rowValues = new String[st.countTokens()]; int index = 0; while (st.hasMoreTokens()) { String next = st.nextToken(); rowValues[index++] = next; } //String[] rowValues = text.split(","); if (line == 0) { // Header row for (String col : rowValues) { headers.add(col); } } else { // Data row Element rowElement = newDoc.createElement("row"); rootElement.appendChild(rowElement); for (int col = 0; col < headers.size(); col++) { String header = headers.get(col); String value = null; if (col < rowValues.length) { value = rowValues[col]; } else { // ?? Default value value = ""; } Element curElement = newDoc.createElement(header); curElement.appendChild(newDoc.createTextNode(value)); rowElement.appendChild(curElement); } } line++; } ByteArrayOutputStream baos = null; OutputStreamWriter osw = null; try { baos = new ByteArrayOutputStream(); osw = new OutputStreamWriter(baos); TransformerFactory tranFactory = TransformerFactory.newInstance(); Transformer aTransformer = tranFactory.newTransformer(); aTransformer.setOutputProperty(OutputKeys.INDENT, "yes"); aTransformer.setOutputProperty(OutputKeys.METHOD, "xml"); aTransformer.setOutputProperty("{http://xml.apache.org/xslt}indent-amount", "4"); Source src = new DOMSource(newDoc); Result result = new StreamResult(osw); aTransformer.transform(src, result); osw.flush(); System.out.println(new String(baos.toByteArray())); } catch (Exception exp) { exp.printStackTrace(); } finally { try { osw.close(); } catch (Exception e) { } try { baos.close(); } catch (Exception e) { } } } catch (Exception e) { e.printStackTrace(); } } private final Logger LOGGER = Logger.getLogger(CSVtoXML.class); } ``` I would like it output in the follwoing format, all be it with different values this is just an example. ``` <?xml version="1.0" encoding="UTF-8"?> <XMLCreators> <row> <Name>chi</Name> <Age>23</Age> <sex></sex> </row> <row> <Name>kay</Name> <Age>19</Age> <sex>male</sex> </row> <row> <Name>john</Name> <Age></Age> <sex>male</sex> </row> </XMLCreators> ``` Example CSV: ``` Job Type Cntr Number Date Booking Ref Commodity Weight Special Instructions JOB0001 Circle 12 31/09/2013 Book0001 1 100.00 Carry JOB0002 Square 13 31/11/2013 Book0001 2 200.00 None JOB0003 Cube 15 31-Dec-13 Book0001 3 300.00 Hide ``` -------------------------------------------------------------------------------------------- 03/02/2014 Current code ``` package com.ns.extension.tee; import com.ns.argo.business.api.ArgoUtils import com.ns.argo.business.api.GroovyApi import static com.xlson.groovycsv.CsvParser.parseCsv import groovy.xml.* import org.apache.xalan.* /** * Converting CSV to XML. * Date: 24/01/14 : 11:50 * Called from: Groovy Job. */ public class CSVtoXML extends GroovyApi { public void execute(Map param) { new File( 'C:/convert/CRR_output.xml' ).withWriter { w -> new File( 'C:/convert/CRR.csv' ).withReader { r -> def csvParser = parseCsv( r , speerator: ',') w.println new StreamingMarkupBuilder().bind { XMLCreators { csvParser.each { line -> println line if( line ['Job'] ){ row { job( line[ 'Job' ] ) type( line[ 'Type' ] ) cntr( line[ 'Cntr Number' ] ) date( line[ 'Date' ] ) bref( line[ 'Booking Ref' ] ) comm( line[ 'Commodity' ] ) weig( line[ 'Weight' ] ) spci( line[ 'Special Instructions' ] ) } }} } } } } } static main( args ) { new CSVtoXML().execute( [:] ) } } ```