Convert JSON file to CSV using Java script activity

[Akila4]

Hi,

I have a .JSON file it contains some of customer  details I want to covert this JOSN file to CSV before  feeds into data loading activity using script activity in technical workflow. I am facing this error this is my workflow

Any help is much appreciated. Thanks

[InMo]

Hi Akila,

Seems like you haven't provided proper path of json file in your javascript activity (code).

File path is a parameter for java script code or function you have in javascript activity, please check. 

Regards,

[AkshayAnand · Accepted solution]

Hi @Akila4 

May i know if you are using your local system path in the JavaScript to access the file. If yes, Could you try uploading the file to adobe's SFTP with  proper rights and try accessing the file from that path.

Let me know if this helps.

Regards

Akshay

[DishaSharma]

@Akila4 @AkshayAnand @InMo 

could you please share the code to convert this json file into csv file

i m also working same requirement and your help will be very much appreciated

[InMo]

Hi @Akila4,

As mentioned by @AkshayAnand also above, how you will access the json file, you need to upload this file into SFTP before operating on it.

Once this is done, you can access it from SFTP in the js activity, parse it and save the data in a table. You don't need to load it using data loading activity.

please refer below post for steps

https://experienceleaguecommunities.adobe.com/t5/adobe-campaign-classic-questions/parse-a-json-value...

Regards

[Sujith_02kumar]

Hi @Akila4 ,

Let this be your JSON data,

Flow structure:

Query > Js Code

Query Activity:

Condition: primary key is not empty

Js activity:

var que = xtk.queryDef.create(
<queryDef schema={vars.targetSchema} operation="select">
<select>
<node expr="@address"/>
<node expr="@addressType"/>
<node expr="@addressStatus"/>
<node expr="@quarantineReason"/>
<node expr="@quarantineErrorText"/>
<node expr="@lastErrorDate"/>
<node expr="@createdDate"/>
<node expr="@JsonData"/>
<node expr="@id"/>
</select>
</queryDef>
).ExecuteQuery();

var mySchema = vars.targetSchema.split(":")[1]
for each (var i in que){
var myJson = i.@JsonData
var jsonObj = JSON.parse(myJson);
var address = jsonObj.data.addressQuarantine.address;
var addressType = jsonObj.data.addressQuarantine.addressType;
var email = jsonObj.data.addressQuarantine.email;
var addressStatus = jsonObj.data.addressQuarantine.addressStatus;
var quarantineReason = jsonObj.data.addressQuarantine.quarantineReason;
var quarantineErrorText = jsonObj.data.addressQuarantine.quarantineErrorText.replace(/'/g, "''");

var lastErrorDate = jsonObj.data.addressQuarantine.lastErrorDate;
lastErrorDate = formatDate(lastErrorDate, "%2D/%2M/%2Y %02H:%02N:%02S");
var createdDate = jsonObj.data.addressQuarantine.createdDate;
createdDate = formatDate(createdDate, "%2D/%2M/%2Y %02H:%02N:%02S");
//logInfo(i.@pk);

var myXML = <{mySchema} xtkschema={vars.targetSchema} operation="update"
id = {i.@id}
address = {address}
addressType = {addressType}
addressStatus = {addressStatus}
quarantineReason = {quarantineReason}
lastErrorDate = {lastErrorDate}
createdDate = {createdDate}
/>
xtk.session.Write(myXML)
}

Final output:

Thus we can get the data from inbound transition and pass it into temporary schema.

I hope this helps:

Regards,

Sujith Kumar.