How to export attributes containing several values to mysql


(jmm) #1

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:

NoteA,tag1,theme1
NoteA,tag2,theme1
NoteA,tag1,theme2
NoteA,tag2,theme2

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.]


(Mark Anderson) #2

I believe there is a way to do this, using List.each(). Busy now, will try and get and answer/demo file up tomorrow.


(Mark Anderson) #3

OK, here is the demo. Rather than write a long essay, take a look at this demo: http://www.acrobatfaq.com/tbdemos/CSV-value-export.zip

Notes:

  • Look at the notes under /Data and the KA values.
  • 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.

HTH. :grinning:


(jmm) #4

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?

NoteA,Tags,tag1
NoteA,Tags,tag2
NoteA,Themes,theme1
NoteA,Themes,theme2

(Mark Anderson) #5

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:

Name,^value($ExportAttributes.format(","))^
^children("csv-item")^

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.


(jmm) #6

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:

NoteA,tag1,theme1
NoteA,tag1,theme2
NoteA,tag2,theme1
NoteA,tag2,theme2

Instead, the demo outputs both $Tags and $Themes in the 2nd column; same in the 3rd column:

NoteA,"Tags",tag1
NoteA,"Tags",tag2
NoteA,"Themes",theme1
NoteA,"Themes",theme2

I struggle to get Tinderbox to do what I want, and this is one of the occasions in which my skills are beyond what I want to achieve.


(Mark Anderson) #7

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.


(jmm) #8

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.

"ID","Name","^value($ExportAttributes.format('","'))^"
^children("csv-item")^

This last bit is not as difficult as the demo, yet I will be grateful for help. CSV-value-export-1.tbx (68.1 KB)


(Mark Anderson) #9

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.


(jmm) #10

Thanks, but I’m afraid after modifying that end, the file doesn’t export to csv. As far as I can tell, the same happens at the beginning.


(Mark Anderson) #11

Ok, try this as the item template:

^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=;)^

There seems to be a parsing edge case/bug whereby if the \n is enclosed in single quotes it is mis-parsed. Med. The above seems to work.


(jmm) #12

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.


(Mark Anderson) #13

OK, the fix to my original solution is this:

$ExportString=$ExportString+'"'+$ID+'","'+$Name+'","'+Y+'","'+Z+'"'+"\n";

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?