Mulesoft s3 csv to json-CodePudding

I am trying to take a csv file that was SFTP to s3 bucket and then I use getObject to get data. It is in the payload in form of string and I call the below but it gives me an error. I am not clear if there is another way to convert to json format. If I step through and just see what is in the payload the data looks fine. If I do a simple change to json doing following:

%dw 2.0
output application/json
---
payload

I get a bunch of \n \r characters in the results and it does not work.

My logic in the transform message:

%dw 2.0
import * from dw::core::Strings
output application/json

---

payload filter (substring($.FT_OPPORTUNITY_ID,0,3) == "OPP") map ( payload01 , indexOfPayload01 ) -> {
    STAGE: payload01.STAGE,
    OPPORTUNITY_NAME: payload01.OPPORTUNITY_NAME,
    TOTAL_AUM_VALUE: payload01.TOTAL_AUM_VALUE,
    MA_AUM: payload01.MA_AUM,
    PARTICIPANTS_WITH_A_BALANCE: payload01.PARTICIPANTS_WITH_A_BALANCE,
    REFERRING_PARTNER: payload01.REFERRING_PARTNER,
    MANAGED_ACCOUNT_PROVIDER: payload01.MANAGED_ACCOUNT_PROVIDER,
    QDIA_SELECTION: payload01.QDIA_SELECTION,
    NO_OF_PROPRIETARY_FUNDS: payload01.NO_OF_PROPRIETARY_FUNDS,
    ADVISOR_CONTACT_NAME: payload01.ADVISOR_CONTACT_NAME,
    ADVISOR_FIRM_NAME: payload01.ADVISOR_FIRM_NAME,
    ADVISOR_EMAIL: payload01.ADVISOR_EMAIL,
    PROBABILITY: payload01.PROBABILITY,
    ADVISORY_FIRM: payload01.ADVISORY_FIRM,
    CREATED_DATE: payload01.CREATED_DATE,
    CLOSE_DATE: payload01.CLOSE_DATE,
    TOTAL_VESTWELL_ARR: payload01.TOTAL_VESTWELL_ARR,
    OPPORTUNTIY_OWNER: payload01.OPPORTUNTIY_OWNER,
    OPPORTUNITY_ID: payload01.OPPORTUNITY_ID,
    FT_SALES_TERRITORY: payload01.FT_SALES_TERRITORY,
    CRD: payload01.CRD,
    AS_OF_DATE: payload01.AS_OF_DATE,
    SOURCE_ID: payload01.SOURCE_ID,
    FT_Stage: payload01.FT_Stage,
    FT_Probability: payload01.FT_Probability,
    FT_OPPORTUNITY_ID: payload01.FT_OPPORTUNITY_ID,
    PLAN_ID: payload01.PLAN_ID,
    STATE: payload01.STATE
}

error message:

""You called the function 'Value Selector' with these arguments: 
  1: String ("S")
  2: Name ("FT_OPPORTUNITY_ID")

But it expects one of these combinations:
  (Array, Name)
  (Array, String)
  (Date, Name)
  (DateTime, Name)
  (LocalDateTime, Name)
  (LocalTime, Name)
  (Object, Name)
  (Object, String)
  (Period, Name)
  (Time, Name)

7| payload filter (substring($.FT_OPPORTUNITY_ID,0,3) == "OPP") map ( payload01 , indexOfPayload01 ) -> {
                             ^^^^^^^^^^^^^^^^^^^
Trace:
  at anonymous::criteria (line: 7, column: 27)
  at dw::Core::reduce (line: 2352, column: 9)
  at dw::Core::filter (line: 2351, column: 11)
  at anonymous::filter (line: 7, column: 9)
  at anonymous::map (line: 7, column: 9)
  at anonymous::main (line: 7, column: 62)" evaluating expression: "%dw 2.0
import * from dw::core::Strings
output application/json

---

payload filter (substring($.FT_OPPORTUNITY_ID,0,3) == "OPP") map ( payload01 , indexOfPayload01 ) -> {
    STAGE: payload01.STAGE,
    OPPORTUNITY_NAME: payload01.OPPORTUNITY_NAME,
    TOTAL_AUM_VALUE: payload01.TOTAL_AUM_VALUE,
    MA_AUM: payload01.MA_AUM,
    PARTICIPANTS_WITH_A_BALANCE: payload01.PARTICIPANTS_WITH_A_BALANCE,
    REFERRING_PARTNER: payload01.REFERRING_PARTNER,
    MANAGED_ACCOUNT_PROVIDER: payload01.MANAGED_ACCOUNT_PROVIDER,
    QDIA_SELECTION: payload01.QDIA_SELECTION,
    NO_OF_PROPRIETARY_FUNDS: payload01.NO_OF_PROPRIETARY_FUNDS,
    ADVISOR_CONTACT_NAME: payload01.ADVISOR_CONTACT_NAME,
    ADVISOR_FIRM_NAME: payload01.ADVISOR_FIRM_NAME,
    ADVISOR_EMAIL: payload01.ADVISOR_EMAIL,
    PROBABILITY: payload01.PROBABILITY,
    ADVISORY_FIRM: payload01.ADVISORY_FIRM,
    CREATED_DATE: payload01.CREATED_DATE,
    CLOSE_DATE: payload01.CLOSE_DATE,
    TOTAL_VESTWELL_ARR: payload01.TOTAL_VESTWELL_ARR,
    OPPORTUNTIY_OWNER: payload01.OPPORTUNTIY_OWNER,
    OPPORTUNITY_ID: payload01.OPPORTUNITY_ID,
    FT_SALES_TERRITORY: payload01.FT_SALES_TERRITORY,
    CRD: payload01.CRD,
    AS_OF_DATE: payload01.AS_OF_DATE,
    SOURCE_ID: payload01.SOURCE_ID,
    FT_Stage: payload01.FT_Stage,
    FT_Probability: payload01.FT_Probability,
    FT_OPPORTUNITY_ID: payload01.FT_OPPORTUNITY_ID,
    PLAN_ID: payload01.PLAN_ID,
    STATE: payload01.STATE
}"."

CodePudding user response：

The payload seems to have an incorrect media type so it is considered a String. You can probably set it as a CSV at the S3 operation by using the attribute outputMimeType="application/csv" or in the transformation parse it with read(): read(payload,"application/csv")

CodePudding user response：

I have resolved based on @aled solution.

%dw 2.0
output application/json
---
read(payload,"application/csv")