Sets can store several values delimited by semicolons. When exporting them from Tinderbox, in the resulting csv file one gets 1 Tags column containing 2 values separated by a semicolon. The same for the Theme column:
NoteA,tag1;tag2,theme1;theme2
To place just 1 value in 1 column, the note should be repeated for as many times as the number of values in each of its attributes of set type. The above would become:
Does this approach to view data from TB in mysql or a spreadsheet make sense? Are there instructions on how to iterate the needed tasks to write the csv file?
[This specific question is rescued from a more general previous post.]
Now select the agent "CSV export. Select the text/HTML pane and look at specimen output.
Use File ā Export selected note and look at the exported file.
You can then look at the templates used in the TBX and see how this works
Anyway, give it a try out - and then ask about any bits you donāt understand. The action code isnāt exactly starter stuff and uses list iterations (lists.each) and the action code var used for local variables.
I could have hard coded the attributes to iterate, but this method allows you to set the attribute to be exported per-note via the agentās $ExportAttributes (a user attribute Iāve added). By altering the csv-item template code that list could be stored elsewhere, e.g. so lots of different exports could all use the same list.
It certainly helps, itās very kind of you, and I donāt understand the output.
With the following output of the demo I cannot see how to import the csv into the right fields in mysql. For the single note example, I think the predicted output I wrote above, if it is possible to get from Tinderbox, would also allow for a matrix in mysql. Am I missing something here?
Sorry, I donāt know SQL. I was just producing a CSV document, which I though was the request. Iād be very surprised if a SQL database canāt import CSV.
The thought occurs, given the latter, that the issue might be the lack of a header row. My demo didnāt include one as your requested specimen output lacked one; the omission seemed egregious enough in a CSV to be a deliberate choice. If you need a header row, amend the ācsv-wrapperā template to:
Otherwise, Iād suggest asking an SQL-related forum as to how that app imports CSV data. Iām sure is will as CSV is essentially the lowest common denominator form of data exchange formats.
Previously unstated assumption: it seemed unlikely your listing tag-like attributes would be using complex strings. But, if youāre using quotes or semi-colons in your actual individual attribute values, that may complicate matters. Insufficient info to comment further in this context.
Thank you for your helpful demo, and sorry for my delay to follow it up.
Mysql easily imports csv. There is no problem there. The problem is that the csv exported with the demo doesnāt dedicate each column to a Tinderbox attribute; ie, 2nd column for $Tags, 3rd column for $Themes, as in:
I sort of follow. It would be useful if we could work with a more real example. Iād assumed each note had $Tags data and $Theme data. But if using a Set, they donāt always retain the stored order of the items. But you examples seem to imply that for Note A, the first $Tags value is associated with the first $Theme value. This is not a safe assumption long term (i.e. it will be true until one day it isnāt). Lists do hold value order - though also accept duplicate values (sets de-dupe).
Also unclear is if it is a fixed assumption that a notes $Tags and $Themes will always have the same number of values. What if note as has 3 tags and two themes. Itās not clear what happens.
Taking into account your objections, Mark, I realise now that your demo approach is way better than the one I thought of. Thank you for pointing them out.
As commas are frequent in note names, I have modified the demo to enclose the values in between double quotes after reading aTbRefās page on single and double quotes. However, I have not been able to find the way to quote the unmatched double quote needed before the first and after the last attribute in csv-item:
^action($ExportString=;
var X;
X = $ExportAttributes("CSV export");
X.each(Y){
eval("$"+Y).each(Z){
$ExportString=$ExportString+$ID+'","'+$Name+'","'+Y+'","'+Z+"\n";
};
};
)^^value($ExportString)^^action($ExportString=;)^
Quotes in csv-wrapper work as entered below, as long as some value is entered in ExportAttributes, as it will.
Does that help ? Note the change - an added single quote before the final \n. The latter is simply a line break character, to the next record starts on a new line.
Thatās great, and I confirm that it now works flawlessly with a large number of real TB notes.
I guess @eastgate will take note of the mis-parsing of \n. I find format substitutions complex at times. Trying to learn them, and to use the agent solution for all CSV exports from TB, I modified your demo. The goal is that each note uses a line, with a single column for all tags, etc. Funny enough this time I got the formatting output right whatever the number of attributes addedā¦ but I canāt understand why they are not filled with their values.
This is the content of csv-item:
^action($MyString=;
var X;
X = $ExportAttributes("CSV Export Agent").format('','","$','','');
$MyString=$MyString+'"'+$ID+'","'+$Name +X+'"' +"\n";
};
)^^value($MyString)^^action($MyString=;)^
An this is the output:
āIDā,āNameā,āTagsā,āThemeā,āContextā
ā1532104244ā,āJournalism that matters ā,ā$Tagsā,ā$Themeā,ā$Contextā
ā1532104237ā,āWhy I Needed to Pull Back From Twitterā,ā$Tagsā,ā$Themeā,ā$Contextā
The solution to the above is not as important, as it can be achieved with other means, so please look into it only if it is easy to correct.
But perhaps someone could explain briefly the use of prototypes for TB exports. I view it as a problem: if I change the prototype to two notes with differente prototypes (letās say, quoation and question), I canāt think of a way to get the notes back to their original prototype after exporting them. This is why in the demo above I change the selection of notes by the agent based on a prototype to a new attribute ExportCSV. The good thing about using an agent for exports is that it can also apply the change of HTMLExportTemplate and HTMLExportExtension.
I know, the last string is is in double rather than single quotes, but that is the secret to getting the line break to export correctly. Investigation as to why is still under debate elsewhere.
You code is simply making a variable called X which isnāt being iterated. Please read up on list.each() and eval(). So your use of .format() is moot as it doesnāt work towards the necessary solution. It is also important to understand the difference between an attribute name Name and a reference to that attributeās value $Name. I do understand that the forum convention of marking a textual reference to an attribute name by using a $-prefix is unintentionally confusing.
Prototypes? I read insufficient info to answer the question. Are you trying to export the same note via two different templates, for different purposes, in the same document? If so there are a number of methods to achieve this. Can you clarify the ambiguities in your question?
