Convert CSV to JSON using a Data Mapper

Prerequisites

WSO2 ESB or EI and Developer Studio (EI Tooling)

Note

The Data Mapper mediator is a data mapping solution that can be integrated into a mediation sequence. It converts and transforms one data format to another, or changes the structure of the data in a message.

Create deployable artifacts using developer studio

• In WSO2 EI Tooling, I have added an API, adding a Data Mapper mediator in the InSequence of the API resource as below to convert CSV to JSON.

• Double click the Data Mapper mediator icon and provide a name (Configuration Name) for the data mapping configuration file that will be created.

• Click OK. You view the data mapping editor.

Sample content of CSV used to create mapping config file

date,id,product_version
2018-01-01,01,1.0.0
2018-02-21,01,1.2.0
2018-03-01,03,2.0.0

Sample content of JSON used to create mapping config file

{
    "data": [{
        "date":"2018-01-01",        
        "id": "01",                
        "product_version":"1.0.0"
    }]
}

• Load the CSV file as the input file and the load the JSON file as the output file as below.

• We can see the mapping configs as below

• We need to map CSV to JSON as below and save the mapped configurations.

• Then right-click on data mapper mediator and click on Show Properties View. Then we can see the default configs as below.

• We need to change the Input Type as CSV and Output Type as JSON from the properties as follows.

• When checking on the source view, related synapse configs as follows.

<api context="/convert" name="ConvertCsvToJson" xmlns="http://ws.apache.org/ns/synapse">
    <resource methods="POST">
        <inSequence>
            <log level="full"/>
            <datamapper config="gov:datamapper/MappingConfig.dmc" inputSchema="gov:datamapper/MappingConfig_inputSchema.json" inputType="CSV" outputSchema="gov:datamapper/MappingConfig_outputSchema.json" outputType="JSON"/>
            <log level="full"/>
            <respond/>
        </inSequence>
        <outSequence/>
        <faultSequence/>
    </resource>
</api>

• We can create a composite application project and created a carbon application. Then we can deploy the created carbon application in EI or ESB server.
• To invoke the deployed API we can use the following curl command.

curl --data-binary @test.csv -H 'Content-type:text/plain' http://localhost:8280/convert

• Following is the response of the curl command.

• Hence there were log mediators, we can see the CSV request payload and JSON output from the EI server logs as below.

[2018-09-30 20:18:36,304] [EI-Core]  INFO - LogMediator To: /convert, MessageID: urn:uuid:e9a1a6ee-7c4a-4b78-bb9f-da3d59c5b8e8, Direction: request, Envelope: <?xml version='1.0' encoding='utf-8'?><soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"><soapenv:Body><text xmlns="http://ws.apache.org/commons/ns/payload">2018-01-01,01,1.0.0
2018-02-21,01,1.2.0
2018-03-01,03,2.0.0
</text></soapenv:Body></soapenv:Envelope>
[2018-09-30 20:18:36,344] [EI-Core]  INFO - LogMediator To: /convert, MessageID: urn:uuid:e9a1a6ee-7c4a-4b78-bb9f-da3d59c5b8e8, Direction: request, Payload: {"data":[{"date":"2018-01-01","id":"01","product_version":"1.0.0"},{"date":"2018-02-21","id":"01","product_version":"1.2.0"},{"date":"2018-03-01","id":"03","product_version":"2.0.0"}]}