2

I need to extract CSVData for each transaction and send the extracted csv files as multiple attachments in a single email using SMTP connector.

Could you please let me know, how Can I extract CSV payload dynamically based on Transaction array and access them separately to send attachment in the below format.

I am using DW 2.0/Mule 4

{

"data1.csv":vars.csvExtract1,

"data2.csv":vars.csvExtract2

}

Input payload is as below.

{
   "Header": {
      "From": {
         "@description": "Networks",
         "#text": "Networks"
      },
      "To": {
         "@description": "Retail",
         "#text": "Retail"
      },
      "MessageID": "735-MSG",
      "MessageDate": "2005-01-01T12:00:00+10:00",
   },
   "Transactions": [
      {
         "@transactionID": "46735",
         "@transactionDate": "2002-01-01T12:00:00+10:00",
         "WorkNotification": {
            "@version": "r9",
            "CSVMainsServiceRenewal": {
               "RecordCount": "3",
                  "CSVData": "NMI,NMI_Checksum,Planned_Outage_Commencement_Date,Planned_Outage_Commencement_Time,Duration_of_Outage\n5767656543,7,2015-09-26,15:09:00,11\n5240667197,0,2012-11-02,12:13:00,2\n5240494248,7,2013-01-01,12:13:00,2"
            }
         }
      },
      {
         "@transactionID": "46739",
         "@transactionDate": "2002-01-01T12:00:00+10:00",
         "WorkNotification": {
            "@version": "r9",
            "CSVMainsServiceRenewal": {
               "RecordCount": "3",
                  "CSVData": "NMI,NMI_Checksum,Planned_Outage_Commencement_Date,Planned_Outage_Commencement_Time,Duration_of_Outage\n5767656543,7,2015-09-26,15:09:00,11\n5240667197,0,2012-11-02,12:13:00,2\n5240494248,7,2013-01-01,12:13:00,2"
            }
         }
      }
   ]
}

Thanks,

Deeps
  • 47
  • 9

3 Answers3

1

Try with this. This will assign the value for the key CSVData, for each of the transactions to a key named data$$.csv (which increments on each iteration of the map). Is that what you are after?

%dw 2.0
output application/json
---
payload.Transactions map {
    'data$$.csv': $.WorkNotification.CSVMainsServiceRenewal.CSVData
} reduce ((item, acc = {}) -> acc ++ item)
Salim Khan
  • 4,233
  • 11
  • 15
  • Thanks a lot salim, yes this is what I was looking for. – Deeps Oct 08 '20 at 04:53
  • However I need to derive the CSV files name based on the input parameter. I need to derive csv names as "Networks_Retail_20201008_01.csv" here Network and Retail should be derived from header.from and header.to attributes. I have created a variables to fetch these values but unable to derive the key dynamically. – Deeps Oct 08 '20 at 05:00
  • 1
    Sure.. I updated Lokesh's DW script below and that should get you what you were looking for in the exact shape. – Salim Khan Oct 09 '20 at 05:52
  • 1
    Thanks Salim, yes the CSV file is generating with the expected name, but I could see that the CSV file is having a blank line as header. is there a way we can set header=false while extracting so that the content can start from first line itself. – Deeps Oct 10 '20 at 02:11
  • That should be a separate question altogether. As well depends on how you are trying to generate the CSV from the string that you get at the end of the transformation described in this question. – Salim Khan Oct 11 '20 at 05:38
1

Can you try the below dwl

%dw 2.0
output application/json
var payload= {
"Header": {
"From": {
 "@description": "Networks",
 "#text": "Networks"
},
"To": {
 "@description": "Retail",
 "#text": "Retail"
},
"MessageID": "735-MSG",
"MessageDate": "2005-01-01T12:00:00+10:00",
},
"Transactions": [
{
 "@transactionID": "46735",
 "@transactionDate": "2002-01-01T12:00:00+10:00",
 "WorkNotification": {
    "@version": "r9",
    "CSVMainsServiceRenewal": {
       "RecordCount": "3","CSVData":"NMI,NMI_Checksum,Planned_Outage_Commencement_Date,Planned_Outage_Commencement_Time,Duration_of_Outage\n5767656543,7,2015-09-26,15:09:00,11\n5240667197,0,2012-11-02,12:13:00,2\n5240494248,7,2013-01-01,12:13:00,2"
   }
  }
 },
{
  "@transactionID": "46739",
 "@transactionDate": "2002-01-01T12:00:00+10:00",
 "WorkNotification": {
    "@version": "r9",
    "CSVMainsServiceRenewal": {
       "RecordCount": "3",
          "CSVData": "NMI,NMI_Checksum,Planned_Outage_Commencement_Date,Planned_Outage_Commencement_Time,Duration_of_Outage\n5767656543,7,2015-09-26,15:09:00,11\n5240667197,0,2012-11-02,12:13:00,2\n5240494248,7,2013-01-01,12:13:00,2"
    }
  }
  }
 ]
}
---
{(payload.Transactions map {
"$(payload.Header.From.'#text' ++ '_' ++
    payload.Header.To.'#text' ++ '_' ++
    now() as String {format : 'yyyyMMdd'} ++ (if ($$ as Number < 9) '_0' else '_')
    ++ '$$' as Number + 1).csv" : 
$.WorkNotification.CSVMainsServiceRenewal.CSVData
})}

Output is

{
 "Networks_Retail_20201009_1.csv": "NMI,NMI_Checksum,Planned_Outage_Commencement_Date,Planned_Outage_Commencement_Time,Duration_of_Outage\n5767656543,7,2015-09-26,15:09:00,11\n5240667197,0,2012-11-02,12:13:00,2\n5240494248,7,2013-01-01,12:13:00,2",
 "Networks_Retail_20201009_2.csv": "NMI,NMI_Checksum,Planned_Outage_Commencement_Date,Planned_Outage_Commencement_Time,Duration_of_Outage\n5767656543,7,2015-09-26,15:09:00,11\n5240667197,0,2012-11-02,12:13:00,2\n5240494248,7,2013-01-01,12:13:00,2"
}

Regards, Lokesh

Salim Khan
  • 4,233
  • 11
  • 15
lokesh D
  • 26
  • 5
  • Thanks for your inputs, I am able to fetch CSV Extract from provided logic. However could you please help me derive the CSV name/Key dynamically based on the header fields. CSV Names should be "Networks_Retail_20201008_1.csv" and "Networks_Retail_20201008_2.csv" here Network and Retail should be derived from header.from and header.to attributes. I have created a variables to fetch these values but unable to derive the key dynamically. – Deeps Oct 08 '20 at 05:27
  • 1
    I have edited the answer as expected. Kindly refer. – lokesh D Oct 09 '20 at 04:48
  • Thanks Lokesh, yes the CSV file is generating with the expected name, but I could see that the CSV file is having a blank line as header. is there a way we can set header=false while extracting so that the content can start from first line itself. – Deeps Oct 10 '20 at 02:12
1

You can try with the following approaches:

Header true

{(payload.Transactions map {
"$(payload.Header.From.'#text' ++ '_' ++
    payload.Header.To.'#text' ++ '_' ++
    now() as String {format : 'yyyyMMdd'} ++ (if ($$ as Number < 9) '_0' else '_')
    ++ '$$' as Number + 1).csv" : 
read($.WorkNotification.CSVMainsServiceRenewal.CSVData,"application/csv",{"header": "true"})
})}

Output:

{
  "Networks_Retail_20201011_01.csv": [
    {
      "NMI": "5767656543",
      "NMI_Checksum": "7",
      "Planned_Outage_Commencement_Date": "2015-09-26",
      "Planned_Outage_Commencement_Time": "15:09:00",
      "Duration_of_Outage": "11"
    },
    {
      "NMI": "5240667197",
      "NMI_Checksum": "0",
      "Planned_Outage_Commencement_Date": "2012-11-02",
      "Planned_Outage_Commencement_Time": "12:13:00",
      "Duration_of_Outage": "2"
    },
    {
      "NMI": "5240494248",
      "NMI_Checksum": "7",
      "Planned_Outage_Commencement_Date": "2013-01-01",
      "Planned_Outage_Commencement_Time": "12:13:00",
      "Duration_of_Outage": "2"
    }
  ],
  "Networks_Retail_20201011_02.csv": [
    {
      "NMI": "5767656543",
      "NMI_Checksum": "7",
      "Planned_Outage_Commencement_Date": "2015-09-26",
      "Planned_Outage_Commencement_Time": "15:09:00",
      "Duration_of_Outage": "11"
    },
    {
      "NMI": "5240667197",
      "NMI_Checksum": "0",
      "Planned_Outage_Commencement_Date": "2012-11-02",
      "Planned_Outage_Commencement_Time": "12:13:00",
      "Duration_of_Outage": "2"
    },
    {
      "NMI": "5240494248",
      "NMI_Checksum": "7",
      "Planned_Outage_Commencement_Date": "2013-01-01",
      "Planned_Outage_Commencement_Time": "12:13:00",
      "Duration_of_Outage": "2"
    }
  ]
}

Header false

{(payload.Transactions map {
"$(payload.Header.From.'#text' ++ '_' ++
    payload.Header.To.'#text' ++ '_' ++
    now() as String {format : 'yyyyMMdd'} ++ (if ($$ as Number < 9) '_0' else '_')
    ++ '$$' as Number + 1).csv" : 
read($.WorkNotification.CSVMainsServiceRenewal.CSVData,"application/csv",{"header": "false"})
})}

Output:

{
  "Networks_Retail_20201011_01.csv": [
    {
      "column_0": "NMI",
      "column_1": "NMI_Checksum",
      "column_2": "Planned_Outage_Commencement_Date",
      "column_3": "Planned_Outage_Commencement_Time",
      "column_4": "Duration_of_Outage"
    },
    {
      "column_0": "5767656543",
      "column_1": "7",
      "column_2": "2015-09-26",
      "column_3": "15:09:00",
      "column_4": "11"
    },
    {
      "column_0": "5240667197",
      "column_1": "0",
      "column_2": "2012-11-02",
      "column_3": "12:13:00",
      "column_4": "2"
    },
    {
      "column_0": "5240494248",
      "column_1": "7",
      "column_2": "2013-01-01",
      "column_3": "12:13:00",
      "column_4": "2"
    }
  ],
  "Networks_Retail_20201011_02.csv": [
    {
      "column_0": "NMI",
      "column_1": "NMI_Checksum",
      "column_2": "Planned_Outage_Commencement_Date",
      "column_3": "Planned_Outage_Commencement_Time",
      "column_4": "Duration_of_Outage"
    },
    {
      "column_0": "5767656543",
      "column_1": "7",
      "column_2": "2015-09-26",
      "column_3": "15:09:00",
      "column_4": "11"
    },
    {
      "column_0": "5240667197",
      "column_1": "0",
      "column_2": "2012-11-02",
      "column_3": "12:13:00",
      "column_4": "2"
    },
    {
      "column_0": "5240494248",
      "column_1": "7",
      "column_2": "2013-01-01",
      "column_3": "12:13:00",
      "column_4": "2"
    }
  ]
}
Salim Khan
  • 4,233
  • 11
  • 15