Creating a csv format request body from JSON body

[Sheetal1]

@dino -at-google

I want to from a csv request body using a JSON array as an input. And that csv format will be sent as a request body to a service callout.

I have for a desired csv request body, but whenever I’m setting that body to a variable using context.getVariable() the format is changing to string. We know that csv requires a new line character but because its getting converted to string, no new line is visible.

What can i do.

[chrismca73]

Hi,

You can correct context.getVariable to setVariable and add the required content type(text/csv) in the Service callout. Take a try like this, It will help you

JS Script

var body = context.getVariable("message.content");
var bodyObj = JSON.parse(body);

var someObj = bodyObj.SomeArray;

//CSV with Headers
var csvString = "Username,UserID\n";

for(var i=0;i<someObj.length;i++){
var indJson = someObj[i];
csvString += indJson.UserName+","+indJson.UserID+"\n"
}

**context.setVariable("csvString",csvString);**

ServiceCallout

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<ServiceCallout async="false" continueOnError="false" enabled="true" name="SC-SomeEndpoint">
    <DisplayName>SC-SomeEndpoint</DisplayName>
    <Properties/>
    <Request clearPayload="true" variable="myRequest">
         <IgnoreUnresolvedVariables>false</IgnoreUnresolvedVariables>
         <Set>
 **<Payload contentType="text/csv" variablePrefix="$" variableSuffix="#">$csvString#</Payload>**
         </Set>
    </Request>
    <Response>calloutResponse</Response>
    <HTTPTargetConnection>
         <Properties/>
        <URL>https://xxxx.yyyyy.com/v1/postCSV</URL>
    </HTTPTargetConnection>
</ServiceCallout>

You can send request to the desired endpoint with CSV payload.

This is my testing payload,

{
    "SomeArray": [
        {
            "UserName":"ABC",
            "UserID":"123"
        },
        {
            "UserName":"DEF",
            "UserID":"456"
        },
        {
            "UserName":"GHI",
            "UserID":"789"
        }
    ]
}

[Sheetal1]

Hi @chrismca73 ,

Thanks for the response, but still facing the error regarding CRLF.
Each line of csv file should have this CRLF/LF in it. We can see if these character is present of not using either by View non-printable unicode characters (soscisurvey.de) or we can see it inside notepad++.

So these CRLF characters are being converted to period (.)

The requirement was to add double qoutes around all the not null values.
(RecordType value is hardcoded).
Code i have written :

var getPersonModifiedSinceResponse = JSON.parse(context.getVariable(‘response.content’));

var csvString = ‘“Name”,“DW_MiddleInitial__c”,“DW_BirthDate__c”,“RecordTypeId”\r\n’;

for(var i=0; i < getPersonModifiedSinceResponse.length; i++)
{
firstName[i]= getPersonModifiedSinceResponse[i].FirstName === null ? null : ‘"’+ getPersonModifiedSinceResponse[i].FirstName + ‘"’;
middleInitials[i] = getPersonModifiedSinceResponse[i].MiddleInitials=== null ? null : ‘"’+getPersonModifiedSinceResponse[i].MiddleInitials + ‘"’;

birthdate[i] = getPersonModifiedSinceResponse[i].BirthDate === null ? null : ‘"’ + getPersonModifiedSinceResponse[i].BirthDate.slice(0,10) + ‘"’;

csvString += firstName[i] +“,” +middleInitials[i] +“,”+birthdate[i]+‘,"’+“0124C0000006742”+‘"’+“\r\n”;
}

context.setVariable(“csvString”,csvString);

Response I am getting(check inside the above mentioned site) :

“Name”,“DW_MiddleInitial__c”,“DW_BirthDate__c”,“RecordTypeId”.“batch”,null,“update”,null,“0124C0000006742”.“batch”,null,“update”,null,“0124C0000006742”.“batch”,null,“update”,null,“0124C0000006742”

[dchiesa1]

How are you determining that the CRLF is being translated to a “dot” ? I understand you are using that website
(htt​​ps://ww​w.soscisurvey.de). But how do you get the data into that website? Presumably you paste it? And you copied the content (ctrl-C) from where?

When I tried invoking an API that does JSON-to-CSV conversion, via curl from my terminal, I get what appears to be correctly formatted text:

That looks like newlines to me. When I use the -o option with curl, which tells curl to emit the output into a file, then open the file in a hex editor, I can see the CRLF sequences.

When I open a debugsession (trace), and copy the string out of the debug window, then paste it into soscisurvey.de , I see dots!

The dots are just how THAT WEBSITE represents an actual CRLF sequence. That’s not Apigee inserting dots. It’s the website you chose to use.

So, please double-check your assumptions. Maybe what you think is happening, is not really happening.

For reference, this is the JS policy configuration I used

<Javascript name='JS-Convert-JSON-to-CSV'>
  <Properties>
    <Property name='source'>request.content</Property>
    <Property name='destination'>response.content</Property>
    <Property name='fixedvalue'>RecordType,0124C0000006742</Property>
    <Property name='fieldlist'>FirstName,MiddleInitials,BirthDate</Property>
  </Properties>
  <ResourceURL>jsc://convert-JSON-to-CSV.js</ResourceURL>
</Javascript>

And this is the JS code I used

// the input JSON needs to be an array of objects
var jsonlist = JSON.parse(context.getVariable(properties.source));

var fieldlist = properties.fieldlist.split(",");
var fixedvalue = properties.fixedvalue.split(",");

if (fixedvalue) {
  fieldlist.push(fixedvalue[0]);
}
// build the headerline from the names of the fields
var headerline = fieldlist
  .map(function (f) {
    return '"' + f + '"';
  })
  .join(",");

// build the list of lines, one for each item in the json list
var lines = jsonlist.map(function (listitem) {
  // for this line, get the list of values, maybe surrounded by double quotes
  var fieldvalues = fieldlist.map(function (fieldname) {
    var value = listitem[fieldname];
    return value == null ? "null" : '"' + value + '"';
  });

  // append the fixed value if it is present
  if (fixedvalue) {
    fieldvalues.push('"' + fixedvalue[1] + '"');
  }
  // join the list of values with comma
  return fieldvalues.join(","); // this is one line
});

// join the header line, with all the lines, and concat a trailing CRLF
var csvString = headerline + "\r\n" + lines.join("\r\n") + "\r\n";
context.setVariable(properties.destination, csvString);

[chrismca73]

@dchiesa1 Precisely

@Sheetal1 There is no issue where you formatting the request to CSV. The issue is at the place where you copied that string. You might copied from the flow variable csvString, Its minified.

Try also assigning the same csvString values to request.content and see it in body. The same string you’ll see it in CSV format.

context.setVariable("request.content",csvString);

In Variables

Same value in the body

Good Luck!