fb
WSO2 Tutorial 4 minutes

WSO2: How to process a CSV file using the Smooks Mediator

joost-hofman.jpg
Joost Hofman
WSO2 Expert
29 jan no 1
Scroll

29_jan_no_1.png

In this blog we will set up a simple example that picks up a csv file and converts it to a proper xml structure. Make sure you enabled the VFS transport in your axis2.xml configuration file before starting this example.

We will first create our project structure in the Developer studio:

1. Create a Maven Multi Module Project

2. Create an ESB Configuration project

3. Create a Composite Application Project

We will create two files in our WSO2 ESB project; a Proxy service to process the CSV file, A Smooks configuration file to configure our Smooks mediator.

Smooks is an extensible framework for building applications for processing XML and non XML data (CSV, EDI, Java etc) using Java.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
First we create our Proxy service
<?xml version="1.0" encoding="UTF-8"?>
<proxy http://ws.apache.org/ns/synapse">http://ws.apache.org/ns/synapse"
name="YenloCSVProcessor"
    transports="vfs" startOnLoad="true" trace="disable">
    <target>
        <inSequence>
            <log level="full" />
            <smooks config-key="smooks-config">
                <input type="text" />
                <output type="xml" />
            </smooks>
            <log level="full" />
        </inSequence>
        <outSequence />
        <faultSequence />
    </target>
    <parameter name="transport.PollInterval">5</parameter>
    <parameter name="transport.vfs.ActionAfterProcess">
MOVE</parameter>
    <parameter name="transport.vfs.ActionAfterFailure">
MOVE</parameter>
    <parameter name="transport.vfs.FileURI">file://D:tmp
yenloexamples
csvin
    </parameter>
    <parameter name="transport.vfs.MoveAfterProcess"
>file://D:tmpyenlo
examplescsvout
    </parameter>
    <parameter name="transport.vfs.MoveAfterFailure">file://D:tmp
yenloexamplescsvfail
    </parameter>
    <parameter name="transport.vfs.FileNamePattern">.*.csv</parameter>
    <parameter name="transport.vfs.ContentType">text/plain</parameter>
</proxy>

We set the transport to VFS and set the required parameters for this transport.

Make sure you change the file paths to your needs.

For more information about the VFS transport please visit the WSO2 Docs: https://docs.wso2.com/display/ESB481/VFS+Transport

In our insequence we have 3 mediators: Log the raw csv, Smooks our csv, Log the xml message.

To be able to use the smooks mediator we need to create a smooks configuration file.

For more information about the smooks mediator: https://docs.wso2.com/display/ESB481/Smooks+Mediator

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
Create a new local entry in the ESB with the name you speficied with the key attribute of the smooks mediator and set the following content:
<?xml version="1.0" encoding="UTF-8"?>
<localEntry http://ws.apache.org/ns/synapse">http://ws.apache.org/ns/synapse" key="smooks-config">
    <smooks-resource-list http://www.milyn.org/xsd/smooks-1.1.xsd">http://www.milyn.org/xsd/smooks-1.1.xsd"
        xmlns:csv="http://www.milyn.org/xsd/smooks/csv-1.2.xsd">
        <resource-config selector="org.xml.sax.driver">
            <resource>org.milyn.csv.CSVReader</resource>
            <param name="fields">firstname,lastname,$ignore$,age</param>
            <param name="rootElementName">yenlo</param>
            <param name="recordElementName">csvRecord</param>
        </resource-config>
    </smooks-resource-list>
</localEntry>

With the fields parameter you can specify the name of the xml element of each position in the csv. With the tag $ignore$ you can ignore positions within the csv.

With the rootElementName you can specify the top element after running the smooks mediator.

The recordElementName is the name of the element for each CSV record that is converted.

Your ESB project should look like this now:

29_jan_no_4.png

Now add both files to your earlier created YenloCSVCAR project:

29_jan_no_5.png

Deploy the CAR file to your ESB.

Running our CSV processor proxy

Place the example csv file in the “in” directory we specified in the proxy parameters. Within a couple seconds the file will be picked up and processed by our proxy service.

My CSV example looks like this:

1
2
3
Joost,Hofman,ignore me,31
Piet,Klaas,also ignore me,47

In our first Log statement we will see:

1
2
3
<?xml version="1.0" encoding="utf-8"?><soapenv:Envelope xmlns_soapenv="http://schemas.xmlsoap.org/soap/envelope/"><soapenv:Body><text http://ws.apache.org/commons/ns/payload">http://ws.apache.org/commons/ns/payload">Joost,Hofman
,ignore me,31
Piet,Klaas,also ignore me,47</text></soapenv:Body></soapenv:Envelope>

The raw content from the csv file is set in the “text” element.

Now the smooks mediator will parse the raw content to a xml structure.

1
2
3
4
<?xml version="1.0" encoding="utf-8"?><soapenv:Envelope xmlns_soapenv="http://schemas.xmlsoap.org/soap/envelope/"><soapenv:Body><yenlo><csvRecord number="1"><firstname>Joost</firstname><lastname>Hofman</lastname><age>31</age></csvRecord><csvRecord number="2"><firstname>Piet</firstname><lastname>Klaas</lastname><age>47</age></csvRecord></yenlo></soapenv:Body></soapenv:Envelope>
As we can see in our 2nd log statement, the raw content is parsed to the structure we provided in the smooks-config.xml file.