Expand my Community achievements bar.

SOLVED

Convert JSON file to CSV using Java script activity

Avatar

Level 2

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

Akila4_2-1710832009119.png

 

Akila4_0-1710831898694.png

 

Any help is much appreciated. Thanks

1 Accepted Solution

Avatar

Correct answer by
Community Advisor

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

View solution in original post

5 Replies

Avatar

Level 3

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,

Avatar

Correct answer by
Community Advisor

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

Avatar

Level 4

@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

Avatar

Level 3

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

Avatar

Level 3

Hi @Akila4 ,

 

Let this be your JSON data,

Sujith_02kumar_0-1712578342211.png

Flow structure:

Query > Js Code

Sujith_02kumar_1-1712578386421.png

 

Query Activity:

Condition: primary key is not empty

Sujith_02kumar_2-1712578413802.png

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:

Sujith_02kumar_3-1712578575690.png

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

 

I hope this helps:

 

Regards,

Sujith Kumar.