Convert JSON file to CSV using Java script activity | Community
Skip to main content
Level 2
March 19, 2024
Solved

Convert JSON file to CSV using Java script activity

  • March 19, 2024
  • 4 replies
  • 1920 views

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

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by AkshayAnand

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

4 replies

Level 3
March 19, 2024

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
Community Advisor
AkshayAnandCommunity AdvisorAccepted solution
Community Advisor
March 19, 2024

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

Level 4
March 21, 2024

@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

Level 3
March 21, 2024

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-from-string-in-the-workflow-query/m-p/451464

 

Regards

Level 4
April 8, 2024

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.