How to map CSV fields to a JSON list?

Question

I am using Mule 3.8.1 and have a csv file which sends in store opening times as

Group Name,Group ID,Store ID,Store Name,Mon opening time,Tues opening time,Wed opening time,Thurs opening time,Fri opening time,Sat opening time,Sun opening time,Mon closing time,Tues closing time,Wed closing time,Thurs closing time,Fri closing time,Sat closing time,Sun closing time
DBLTD,DB1,STORE1,Main Store,9:00,9:00,9:00,9:00,9:00,9:00,9:00,20:00,20:00,20:00,20:00,20:00,20:00,20:00
DBLTD,DB1,STORE2,NYC Store,9:00,9:00,9:00,9:00,9:00,9:00,9:00,20:00,20:00,20:00,20:00,20:00,20:00,20:00

I need to map them to a JSON list in the following format using Dataweave:

{
   "groupId": "DB1",
   "groupName": "DBLTD",
   "storeId": "STORE1"
   "storeName": "Main Store",  
   "openingTimes":
   [
      {
         "day": "mon",
         "openingTime": "9:00",
         "closingTime": "20:00"
      },
      {
         "day": "tues",
         "openingTime": "9:00",
         "closingTime": "20:00"
      }
      ...etc
   ]
}

The XML flow I am using to test this working:

<?xml version="1.0" encoding="UTF-8"?>

<mule xmlns:file="http://www.mulesoft.org/schema/mule/file" xmlns:dw="http://www.mulesoft.org/schema/mule/ee/dw" xmlns:metadata="http://www.mulesoft.org/schema/mule/metadata" xmlns="http://www.mulesoft.org/schema/mule/core" xmlns:doc="http://www.mulesoft.org/schema/mule/documentation"
    xmlns:spring="http://www.springframework.org/schema/beans" 
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-current.xsd
http://www.mulesoft.org/schema/mule/core http://www.mulesoft.org/schema/mule/core/current/mule.xsd
http://www.mulesoft.org/schema/mule/file http://www.mulesoft.org/schema/mule/file/current/mule-file.xsd
http://www.mulesoft.org/schema/mule/ee/dw http://www.mulesoft.org/schema/mule/ee/dw/current/dw.xsd">
    <flow name="testFlow">
        <file:inbound-endpoint path="src\main\resources\input" moveToDirectory="src\main\resources\output" responseTimeout="10000" doc:name="File"/>
        <dw:transform-message metadata:id="e77b30f7-f9a2-4b97-82a9-23c186dc03cb" doc:name="Transform Message">
            <dw:input-payload mimeType="application/csv"/>
            <dw:set-payload><![CDATA[%dw 1.0
%output application/json
---
{
    GroupID: payload."Group ID",
    GroupName: payload."Group Name",
    StoreId: payload."Store ID",
    StoreName: payload."Store Name"
}]]></dw:set-payload>
        </dw:transform-message>
    </flow>
</mule>

Does anyone know how I could do this?

Thanks

0. create an object with `openingTimes` of array type as a property. 1. read the csv file as two arrays. 2. loop through one array. For each loop, set the `day` attribute of an temporary object to a value from the first array, and `time` attribute of the same object to a value from the second array; then append the temporary object to the openingTimes array. — Alic, Dec 16 '16 at 17:35
Is all of this done in dataweave? Sorry - my first venture into dataweave.. — user3165854, Dec 16 '16 at 18:20
Sorry, I am not familiar with the Mule platform. But that's how a programmer would do it in code. — Alic, Dec 16 '16 at 18:44

score 1 · Accepted Answer · edited Dec 19 '16 at 04:54

Try the below code...

%dw 1.0
%output application/json
---
payload map {
    GroupID: $."Group ID",
    GroupName: $."Group Name",
    StoreId: $."Store ID",
    StoreName: $."Store Name",
    openingTimes: [
        {
            'day':'mon',
            'openingTime':$."Mon opening time",
            'closingTime': $."Mon closing time"
        },
        {
            'day':'Tues',
            'openingTime':$."Tues opening time",
            'closingTime': $."Tues closing time"
        },
        {
            'day':'Wed',
            'openingTime':$."Wed opening time",
            'closingTime': $."Wed closing time"
        },
        {
            'day':'Thurs',
            'openingTime':$."Thurs opening time",
            'closingTime': $."Thurs closing time"
        },
        {
            'day':'Fri',
            'openingTime':$."Fri opening time",
            'closingTime': $."Fri closing time"
        },
        {
            'day':'Sat',
            'openingTime':$."Sat opening time",
            'closingTime': $."Sat closing time"
        },
        {
            'day':'Sun',
            'openingTime':$."Sun opening time",
            'closingTime': $."Sun closing time"
        }

    ]

}

Thanks. That worked. Looks like I was over thinking the solution. — user3165854, Dec 19 '16 at 12:07

How to map CSV fields to a JSON list?

1 Answers1