How To Read CSV Files Using WSO2 ESB Smooks Mediator

Jul 16, 2016 · 3 minute read · Comments
How TO WSO2 ESB Smooks

In this post we will show an example on how to read CSV Files using the WSO2 ESB Smooks Mediator.

To run the examples we used the WSO2 ESB 4.9.0.

In this example we are going to read a CSV file like the one below from the file system:

1,John Doe,john@doe.email.com,11111111
1,Jane Doe,jane@doe.email.com,2222222

Basically we are going to create a new Proxy Service, that will use the VFS transport.

In order to enable VFS transport we need to follow the instructions described here:

Edit the /repository/conf/axis2/axis2.xml file and uncomment the VFS listener and the VFS sender as follows:

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

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

The ProxyService Configuration

So, let’s take a look at the proxy service configuration:

It is a simple proxy service that defines vfs as transport.

In the inSequence we defined it as an OUT_ONLY.

Then we added the smooks mediator, it refers to a smooks config file defined in the smooks_conf config-key. We will show it later.

In the smooks mediator config, we defined that the input is text and the output is XML.

In the end of the file we have set the parameters used by the VFS transport:

transport.vfs.FileURI: Specifies which folder/file to look into;
transport.vfs.FileNamePattern: specifies which files to process;
transport.vfs.ActionAfterProcess: specifies which action to take after processing the file;

You can find the list of all parameters in the VFS documentation.

So, basically, the service will look for the .csv files in the D:\wso2\example folder and will run the smooks configuration on them.

And then it will log the result message in the console/log file.

Smooks Config

In the example we defined the smooks config as a local entry, we can see it below:

In the config file we set to use the CSVParser, then we defined the field list, in our example:

id,name,email,phone

This config will parse the CSV and create an XML with those fields for each line.

Running The Example

After creating the components on WSO2 ESB, when we put a .csv file on the folder we defined in the proxy service, it will generate an output containing this envelope:

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
    <soapenv:Body>
        <csv-set>
            <csv-record number="1">
                <id>1</id>
                <name>John Doe</name>
                <email>john@doe.email.com</email>
                <phone>11111111</phone>
            </csv-record>
            <csv-record number="2">
                <id>1</id>
                <name>Jane Doe</name>
                <email>jane@doe.email.com</email>
                <phone>2222222</phone>
            </csv-record>
        </csv-set>
    </soapenv:Body>
</soapenv:Envelope>

As we can see it generate an csv-record entry for each line in the CSV file.

Working with CSV With Different Number Of Columns

Now let’s try one thing, let´s try running our example with the following CSV:

1,John Doe,john@doe.email.com,11111111
1,Mary Doe,mary@doe.email.com
2,Jane Doe,jane@doe.email.com,2222222

We will have the following output:

<?xml version='1.0' encoding='utf-8'?>
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
    <soapenv:Body>
        <csv-set>
            <csv-record number="1">
                <id>1</id>
                <name>John Doe</name>
                <email>john@doe.email.com</email>
                <phone>11111111</phone>
            </csv-record>
            <csv-record number="3">
                <id>2</id>
                <name>Jane Doe</name>
                <email>jane@doe.email.com</email>
                <phone>2222222</phone>
            </csv-record>
        </csv-set>
    </soapenv:Body>
</soapenv:Envelope>

As we can see it processed only the records 1 and 3 because they had the exactly number of columns specified in the fields property.

In order to be able to process all records of the CSV file we need to add a new param in the Smooks config:

<param name="strict" type="boolean">false</param>

With this, our smooks config will be like below:

So, now adding the csv in the folder, we will have the following envelope in the output:

<?xml version='1.0' encoding='utf-8'?>
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
    <soapenv:Body>
        <csv-set>
            <csv-record number="1">
                <id>1</id>
                <name>John Doe</name>
                <email>john@doe.email.com</email>
                <phone>11111111</phone>
            </csv-record>
            <csv-record number="2" truncated="true">
                <id>1</id>
                <name>Mary Doe</name>
                <email>mary@doe.email.com</email>
            </csv-record>
            <csv-record number="3">
                <id>2</id>
                <name>Jane Doe</name>
                <email>jane@doe.email.com</email>
                <phone>2222222</phone>
            </csv-record>
        </csv-set>
    </soapenv:Body>
</soapenv:Envelope>

Now all records were processed and sent to the output.

Quick Tip

In order to run the example we need to have the smooks library added to the WSO2 ESB. The version I used was: milyn-smooks-all-1.4.jar.
We just need to add it in the [ESB_HOME]/respository/components/lib

That’s it for today. That’s it! I hope you enjoyed this post.

Thanks, See you in the next post.

Xico Junior