Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
Bedrock Mission!

Learn more

View all

Sign in to view all badges

The 4th edition of the Campaign Community Lens newsletter is out now!
SOLVED

Exporting javascript a list of variables to excel file through a workflow

Avatar

Level 4

HI Team, 

 

Scenario : in javascript i have an array of deliveries -  for say - "birthday1", "birthday2" and i need to save the statistics of these two deliveries like for : sucessfull sents(based on broadlog table), opt-out(based on tracking table) to a variable of javascript and push them into excel file. 

 

i did like this in javascript : 

 

vars.iteration = getOption("iteration") // initially to 0

 

var array = ["Birthday_Dynata%Mar%2020" , "Birthday_Dynata%Jun%2020"];

 

vars.templateName = array[vars.iteration]; //"Birthday_Dynata_MO_AU_Mar 2020";
vars.fromDate = '03/01/2020';
vars.toDate = '03/31/2020';

 

// through sql i am getting some counts based on template name and dates


vars.success = "select count(*) from NmsBroadLogRcp B1 JOIN NmsDelivery D2 ON (D2.iDeliveryId = B1.iDeliveryId) and "+
"D2.sLabel like '"+ vars.templateName +"' and (B1.tsEvent>='"+ vars.fromDate +"' and B1.tsEvent<='"+ vars.toDate +"')";

vars.succSent = sqlGetInt(vars.success) // success count of Mar emails for 1st iteration 

 

vars.optouts = ".... same as above ...."

vars.optcounts = sqlGetInt(vars.optouts) // opt out counts of Mar emails for 1st iteration 

 

now i want to store these two variables into excel file.. 

for this i did an enrichment activty : 

Ramaswami_0-1591377262838.png

enrichment : (For example, lets take only one variable here say : success )

Ramaswami_1-1591377299510.png

 

Ramaswami_2-1591377361712.png

This is the test condition : 

 

Ramaswami_3-1591377448165.png

 

so the problem is if i don't iterate two time i can export the data like below : 

 

Ramaswami_4-1591377514909.png

 

but when i iterate it twice my 'test' is always failing. Seems like i can't able to get my instance variable 'iteration' to validate in test node : 

i tried : vars.iteration, instance.vars.iteration also but i can't able to pass this test condition - 

Ramaswami_5-1591377593611.png

 

Even if test condition is passed i am not sure if i get the second row in excel sheet 

1 Accepted Solution

Avatar

Correct answer by
Level 1

Hello @Ramaswami, 

 

I do not see where you increment vars.iteration, you can do it after your 'data extraction file' with a javascript.

By doing with this workflow, you assume to have x files with just one line. x is the number of iteration.

 

May be, if you want just one file with x lines, I recommand to create a file and add lines for each sql request you need.

 

var array = ["Birthday_Dynata%Mar%2020" , "Birthday_Dynata%Jun%2020"];
... // your variables
var f = new File("c:/file.csv")
f.open("a")

for each (item in array)
{
vars.success = "select count(*) from NmsBroadLogRcp B1 JOIN NmsDelivery D2 ON (D2.iDeliveryId = B1.iDeliveryId) and "+
"D2.sLabel like '"+ vars.templateName +"' and (B1.tsEvent>='"+ vars.fromDate +"' and B1.tsEvent<='"+ vars.toDate +"')";

vars.succSent = sqlGetInt(vars.success) // success count of Mar emails for 1st iteration
f.writeln("dataTxt;" + vars.succSent + ";" + vars.fromDate )

}
f.close()

 

Best regards, 

Alexandre Planchot

View solution in original post

3 Replies

Avatar

Employee

Hi Ramaswami, I believe your query has already been answered through this discussion : https://experienceleaguecommunities.adobe.com/t5/adobe-campaign-classic-questions/exporting-variable....

Is this a different query or similar to the previous one ?

Regards,

Sushma

Avatar

Level 4
hi @Sushma, Its related to my previous discussion it's working fine for 1st row but i have 2-10 rows to be populated into the excel sheet through javascript variables ( it's like a for loop and inserting each iteration values into a row of excel sheet ) it worked fine if it has only 1 teration how about the 2nd iteration.

Avatar

Correct answer by
Level 1

Hello @Ramaswami, 

 

I do not see where you increment vars.iteration, you can do it after your 'data extraction file' with a javascript.

By doing with this workflow, you assume to have x files with just one line. x is the number of iteration.

 

May be, if you want just one file with x lines, I recommand to create a file and add lines for each sql request you need.

 

var array = ["Birthday_Dynata%Mar%2020" , "Birthday_Dynata%Jun%2020"];
... // your variables
var f = new File("c:/file.csv")
f.open("a")

for each (item in array)
{
vars.success = "select count(*) from NmsBroadLogRcp B1 JOIN NmsDelivery D2 ON (D2.iDeliveryId = B1.iDeliveryId) and "+
"D2.sLabel like '"+ vars.templateName +"' and (B1.tsEvent>='"+ vars.fromDate +"' and B1.tsEvent<='"+ vars.toDate +"')";

vars.succSent = sqlGetInt(vars.success) // success count of Mar emails for 1st iteration
f.writeln("dataTxt;" + vars.succSent + ";" + vars.fromDate )

}
f.close()

 

Best regards, 

Alexandre Planchot