Convert .csv files to json using Smooks mediator

Image courtesy: https://folksy.com/

If you are using a ESB version before WSO2 ESB 5.0 you need to use Smooks Mediator to convert csv files to Json since older versions of(before ESB 5.0) does not contain Data mapper mediator.

In this example we are using WSO2 ESB 4.8.1.

Prerequisites

• Download milyn-smooks-all-1.4.jar and place it in [ESB_HOME]/respository/components/lib folder.
• In order to enable VFS transport enable following in <ESB_HOME>/repository/conf/axis2/axis2.xml.

<transportreceiver name="vfs" class="org.apache.synapse.transport.vfs.VFSTransportListener"/>
<transportSender name="vfs" class="org.apache.synapse.transport.vfs.VFSTransportSender"/>

• Start WSO2 ESB 4.8.1

<ESB_HOME/bin>./wso2server.sh

Steps

1. Login to Management Console of ESB and create a folder called temp and add smooks_conf.xml to the configuration registry. In the string list we specify what are the xml elements names to be taken.

<?xml version="1.0" encoding="UTF-8"?>
  <smooks-resource-list xmlns="http://www.milyn.org/xsd/smooks-1.0.xsd">
    <!--Configure the CSVParser to parse the message into a stream of SAX events. -->
    <resource-config selector="org.xml.sax.driver">
      <resource>org.milyn.csv.CSVReader</resource>
      <param name="fields" type="string-list">employeeId,employeeName,Gender,Country</param>
      <param name="ident" type="boolean">true</param>
    </resource-config>
  </smooks-resource-list>

2. Place the Employee.csv file in folder specified FileURI in the below proxy.

Employee.csv

001,Isuru Uyanage,F,Sri Lanka
002,Supun Silva,M,Sri Lanka
003,Mark Boucher,M,South Africa
004,Glain McGrath,M,Australia

3. Create the following proxy.

<?xml version="1.0" encoding="UTF-8"?>
<proxy xmlns="http://ws.apache.org/ns/synapse"
       name="Proxy1"
       transports="vfs"
       statistics="disable"
       trace="disable"
       startOnLoad="true">
   <target>
      <inSequence>
         <log level="full"/>
         <smooks config-key="conf:/repository/temp/smooks_conf1.xml">
            <input type="text"/>
            <output type="xml"/>
         </smooks>
         <log level="full"/>
         <property name="messageType"
                   value="application/json"
                   scope="axis2"
                   type="STRING"/>
         <log level="custom">
            <property name="Employee1 = ID"
                      expression="json-eval($.csv-set.csv-record[0].employeeId)"/>
            <property name="Employee1 = Name"
                      expression="json-eval($.csv-set.csv-record[0].employeeName)"/>
            <property name="Employee2 = ID"
                      expression="json-eval($.csv-set.csv-record[1].employeeId)"/>
            <property name="Employee2 = Name"
                      expression="json-eval($.csv-set.csv-record[1].employeeName)"/>
         </log>
      </inSequence>
   </target>
   <parameter name="transport.vfs.ActionAfterProcess">MOVE</parameter>
   <parameter name="transport.PollInterval">5</parameter>
   <parameter name="transport.vfs.MoveAfterProcess">file://Users/IsuruUyanage/Desktop/Support/Rotation3</parameter>
   <parameter name="transport.vfs.FileURI">vfs:file://Users/IsuruUyanage/Desktop/Support/Rotation3/JLRCAPGEMINIPROD-97</parameter>
   <parameter name="transport.vfs.MoveAfterFailure">file://Users/IsuruUyanage/Desktop/Support/Rotation3</parameter>
   <parameter name="transport.vfs.FileNamePattern">.*.csv</parameter>
   <parameter name="transport.vfs.ContentType">text/plain</parameter>
   <description/>
</proxy>

When the vfs automatically triggers the file, it would move to the other specified location(MoveAfterFailure/MoveAfterProcess) in the above proxy.

The content of the .csv file would converted to a xml payload as below.

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
 <soapenv:Body>
  <csv-set>
   <csv-record number="1">
    <employeeId>001</employeeId>
    <employeeName>Isuru Uyanage</employeeName>
    <Gender>F</Gender>
    <Country>Sri Lanka</Country>
   </csv-record>
   <csv-record number="2">
    <employeeId>002</employeeId>
    <employeeName>Supun Silva</employeeName>
    <Gender>M</Gender>
    <Country>Sri Lanka</Country>
   </csv-record>
   <csv-record number="3">
    <employeeId>003</employeeId>
    <employeeName>Mark Boucher</employeeName>
    <Gender>M</Gender>
    <Country>South Africa</Country>
   </csv-record>
   <csv-record number="4">
    <employeeId>004</employeeId>
    <employeeName>Glain McGrath</employeeName>
    <Gender>M</Gender>
    <Country>Australia</Country>
   </csv-record>
  </csv-set>
 </soapenv:Body>
</soapenv:Envelope>

Then it will be converted to a json by following property.

<property name="messageType" value="application/json" scope="axis2"/>

They can be accessed as below.

<property name="Employee1 = ID"
                      expression="json-eval($.csv-set.csv-record[0].employeeId)"/>
            <property name="Employee1 = Name"
                      expression="json-eval($.csv-set.csv-record[0].employeeName)"/>
            <property name="Employee2 = ID"
                      expression="json-eval($.csv-set.csv-record[1].employeeId)"/>
            <property name="Employee2 = Name"
                      expression="json-eval($.csv-set.csv-record[1].employeeName)"/>

For further reading please refer,

WSO2 Integration Studio

WSO2 Enterprise Integrator