Exporting sets to csv


(Bruno Moreira) #1

Dear all,
after a while away, I’m returning to TBX. I’m struggling with something maybe simple: imagine I have 2 key attributes (sets):

$set1
$set2

$set1 has the following values “11;12;13”
$set2 has “21;22;”

I’d like to export them to CSV and have something like the following:

variable,value
set1,11
set1,12
set1,13
set2,21
set2,22

i.e. iterate through all values of each set and output them as mentioned (which is a simple CSV file).

I’m familiar with export code and envelope-letter approach; what I am having problems is the iteration process. Help much appreciated. Thank you!


(Mark Anderson) #3

If I’ve understood correctly…

We’ll assume your 2 KAs are $MyThingA and $MyThingB. Now template could write:

^action($MyString=;$MyThingA.each(myVal){$MyString="MyThingA,"+myVal+"\n";})^
^value($MyString)^
^action($MyString=;$MyThingB.each(myVal){$MyString="MyThingB,"+myVal+"\n";})^
^value($MyString)^
^action($MyString=;)^

As there are only 2KAs I’ve not attempted an outer loop for those as I’m not sure how well that is supported (i.e. I don’t recall trying it). Left as an exercise for the reader is adding a header row, if needed, for the output.

Also, as you’re exporting a CSV file consider making the note’s $HTMLExportExtension value “.csv”


(Bruno Moreira) #4

Thank you Paul and Mark (Mark: that’s it, I have a few more KA but thinking on exporting them in batches). Will try!


(Bruno Moreira) #5

I’m trying the code but I get an empty CSV, I think there may be an error somewhere with the nested ( and {, trying to figure it out!


(Paul Walters) #6

Looks like there’s a closing parentheses missing on the 1st and 3rd lines of Mark’s suggested code.

(myVal) should be (myVal))

Also, the 5th line should end with a closing parentheses — not a closing brace.


(Bruno Moreira) #7

Thank you Paul, getting closer. I get an output now, but $MyString is not being correctly evaluated yet. Still trying :slight_smile:


(Bruno Moreira) #8

Done! Not sure if it’s the most efficient way, but it works. Each action creates one string by iterating through all values of the selected key attribute, and then ^value(…)^exports that string.

Not sure if I could / should embed the ^value(…)^ code inside the ^action(…)^ code (or even if it’s possible).

^action($MyString="";$MyThingA.each(myVal){$MyString=$MyString+"MyThingA,"+myVal+"\n"})^^value($MyString)^^action($MyString="")^^action($MyString="";$MyThingB.each(myVal){$MyString=$MyString+"MyThingB,"+myVal+"\n"})^^value($MyString)^^action($MyString="")^


(Mark Anderson) #9

Now I’ve corrected my earlier sample, it and the above do essentially the same thing. However, be aware that

$MyString='';

and

$Mystring=;

Do slightly different things. It doesn’t affect this task but might affect subsequent tasks using $MyString. See here for why this should be


(Bruno Moreira) #10

Thanks Mark for the clarification on key attribute’s inheritance – it’s important to know.
Tried your code, and it works, but for only one “MyThingA” and one “My ThingB”. Problem is that the ^action iterates all values, but ^value only outputs one because it’s outside the action loop (I think!).


(Mark Anderson) #11

For now it looks like Set.each(){} and List.each(){} don’t working inside `^action()^. Investigating…