Basic How-To Q about exporting from TB to spreadsheet

Tinderbox Tasks
1

I think what I’m about to ask is a known and straightforward procedure. But it’s not known (or recalled) by me at the moment. Thus this asking-in-public question.

Goal: I want to export selected fields from a TB file, to a spreadsheet-compatible file, to share it with some colleagues who usually work with spreadsheets.

Specs:

  • In the relevant TB file, each note has about 12 Key Attributes.

  • I want to export (a) each note’s $Name, which will be the first column of each derived spreadsheet item; (b) five or six of the KA’s, one of which is Boolean and the rest of which are Set-type ($CityName; $SourceName; etc), so that these form columns in the spreadsheet; and then ( c) each item’s $Text, which can range in length from a few words to several paragraphs. This will also constitute a field in the spreadsheet, presumably the right-most column on the screen, though some long $Text entries may be truncated.

  • I can think of some clumsy workarounds for the exporting I want. (For instance, I could create an attribute $ExportText, and could have a rule setting that to each note’s $Name + (all the relevant KA's) + $Text, with appropriate dividers among each attribute.) Or, I could create a duplicate of the file; could remove all the attributes that I don’t want to export; and work from there.

  • But I think there must be a more straightforward way to export a group of notes, with the relevant KAs and $Text, in a form appropriate to import, drag-drop, or copy/paste into a spread sheet.

Is there such a way? Could someone explain it? Thanks!

3

Thanks! Will put all these to use. Appreciate it.

1 Like
4

CSV, or tab-delimited (same idea, different column value separator), is the way to do.

Now we have AppleScript/OSAscript support one could probably do something that outputs a formatted spreadsheet, but it’s likely more effort that the above (unless/until some script export writes such a script :wink:).

6

Agree. :grin:

Thanks for the mention of semi-colon delimiters, a variant I’d hitherto missed.

7

I’ve been using this little script for a while:

Click to show script 
# Select Tinderbox notes (non-contiguous, multiple levels ok) and click run.
# Places quoted character-separated values (CSV) on clipboard for pasting wherever.

# 1. List the attributes in line below, each between quotes.  Add/remove/reorder as needed
set grabAttribs to {"Name", "CityName", "SourceName", "Themes", "MyBoolean", "NoteURL", "Path", "Text"}

# 2. And set a delimiter here. Try tab or "," or "|" or ";" or whatever the receiving app expects
set myDelimiter to ","

--  NO USER TINKERING NEEDED BELOW THIS LINE, at least in theory;)

set text item delimiters to quote & myDelimiter & quote

# create separate list to hold lists of attribute values (a "list of lists")
copy grabAttribs to attribVals

set numCols to grabAttribs's length

tell application "Tinderbox 8"
	tell front document's selections -- (note use of plural)
		repeat with i from 1 to numCols
			set attrName to grabAttribs's item i
			set attribVals's item i to attribute attrName's value
			# adds list of this attribute's values to the list of lists
		end repeat
	end tell
end tell

set numRows to attribVals's item 1's length

set outStr to quote & (grabAttribs as string) & quote & return # header row

repeat with i from 1 to numRows
	set rowItems to {}
	repeat with j from 1 to numCols
		set rowItems's end to attribVals's item j's item i
	end repeat
	set outStr to outStr & quote & (rowItems as string) & quote & return # body row
end repeat

set the clipboard to outStr

--return outStr -- uncomment this line to view output in Script Editor 'Result' pane

Copy-paste into Script Editor (in Applications > Utilities) or into your other script editor of choice.

Make sure Script Editor and Tinderbox are listed and checked at System Preferences > Security & Privacy > Privacy > Accessibility.

In the first non-commented line just list the names of the attributes you want (between quotes as shown) in the order that you want them in the spreadsheet.

In the second non-commented line set the delimiter you want to try (tab or , or ; all tend work well with Numbers and Excel but you could use others as well).

Select the Tinderbox notes you want to export, and click ‘run’ in Script Editor.

The results will be placed on the system clipboard. You can then paste them into Numbers or Excel or wherever.

From start-to-finish (from copy-pasting the script and editing attribute names to pasting the data) the process takes about a minute or two.

Got the order wrong? Forgot something? Didn’t need something? The chosen delimiter didn’t quite do the job? Just edit either or both of the first two uncommented lines in the script and click again. This can’t do anything that traditional Tinderbox export can’t do but (for me) it is quicker to adjust.

I’ve found this useful for taking with me a rough approximation of the Attribute Browser on the iPad. Once the data is in Numbers I can easily group it by attribute value by using the Smart Categories feature. (Doesn’t group by individual members of sets, though).

For cases where the csv (character-separated value) approach gets choked by unpaired embedded " (straight quotes) or embedded tabs or whatever, it would not be hard (though the script would noticeably slower) to have the script generate a table in Numbers and set values in its cells rather than paste them in.

8

Thanks all for these incredibly useful answers.

One final (rudimentary) question. Suppose I have five attributes that I want to export, in a semicolon-delimited file. I understand the way to use ^value($Attribute1)^ to get those values, and the result is something like:

NoteName; CityName; StateName; PersonName; Text;

Here is my remaining question: How do I indicate (for the semicolon-delimited file) that we’ve reached the end of the series of attributes, and are starting with a new NoteName? Is there a different end-of-note delimiter that is needed, different from the semicolons that separate one attribute from another?

Thanks

9

I’m pretty sure that a new line (i.e. a hard return) indicates a new row is needed in the spreadsheet.

10

Thanks! And, sorry to be dense, but I actually don’t see how to put that hard return in the export template. If I use any of the formulations below, they all render as literal characters in the exported text, as opposed to a hard line break:

  • (\n)
  • “(\n)”
  • ("\n")
  • \n
  • (\r)
  • “(\r)”
  • etc

Again I assume there is a simple answer, but it’s not one that is obvious to me right now. Thanks!

11

In the template itself, i.e. outside any ^export^ tags just use a normal line break (Return key). Within a string use \n (Unix style new line character), i.e. “Two\nlines”.

So let’s say you want to export two attributes with a line break between the two in the exported data, do like so:

^value($MySetA)^
^value($MySetB)^

Note the line break in the code between the two ^value()^ calls. This might give us exported text like

ant;bee;cow
frogs;dogs;logs
2 Likes
12

Ah, thanks! It’s so easy and obvious, once it’s pointed out!

Thanks all.

1 Like
13

Export is the best way because it’s most flexible.

But another approach might be to set up a view with columns for each of the attributes of interest. Select all and Edit ▸ Copy will but a tab-separated-value table on the clipboard.

2 Likes
14

Ahah! Have been using this program for a dozen years, and always new areas of surprise. Glad to know about this one.

15

Tinderbox export via templates is endlessly flexible but the details are easily forgotten. The workarounds are also easily forgotten.:grinning: For example, you and I first learned about the view-in-columns-then copy-paste approach in this thread:

The view-in-columns-then copy-paste approach unfortunately does not bring over column headers. And in some cases where I have quotes in the Text of notes it produces scrambled output, whereas the script I posted above works well. Have you tried it?

Click to reveal script 
# Select Tinderbox notes (non-contiguous, multiple levels ok) and click run.
# Places quoted character-separated values (CSV) on clipboard for pasting wherever.

# 1. List the attributes in line below, each between quotes.  Add/remove/reorder as needed
set grabAttribs to {"Name", "CityName", "SourceName", "Themes", "MyBoolean", "NoteURL", "Path", "Text"}

# 2. And set a delimiter here. Try tab or "," or "|" or ";" or whatever the receiving app expects
set myDelimiter to ","

--  NO USER TINKERING NEEDED BELOW THIS LINE, at least in theory;)

set text item delimiters to quote & myDelimiter & quote

# create separate list to hold lists of attribute values (a "list of lists")
copy grabAttribs to attribVals

set numCols to grabAttribs's length

tell application "Tinderbox 8"
	tell front document's selections -- (note use of plural)
		repeat with i from 1 to numCols
			set attrName to grabAttribs's item i
			set attribVals's item i to attribute attrName's value
			# adds list of this attribute's values to the list of lists
		end repeat
	end tell
end tell

set numRows to attribVals's item 1's length

set outStr to quote & (grabAttribs as string) & quote & return # header row

repeat with i from 1 to numRows
	set rowItems to {}
	repeat with j from 1 to numCols
		set rowItems's end to attribVals's item j's item i
	end repeat
	set outStr to outStr & quote & (rowItems as string) & quote & return # body row
end repeat

set the clipboard to outStr

--return outStr -- uncomment this line to view output in Script Editor 'Result' pane

For export to spreadsheets it matches the flexibility of templates with greater ease in choosing and reordering attributes, and changing the delimiter. Plus it’s reusable. You don’t need to set it up again for each Tinderbox document. Just list the attributes you want, pick a delimiter, select the notes, click , and paste.

16

Nice. If the task changed regularly, e.g. in terms of attributes needed, one could probably use a chooser dialog to the script (resists temptation to start tinkering…) :grin:

17

Does Excel immediately recognise (on paste) all of the default Tinderbox osascript output strings for particular types ?

I wonder if TBX Boolean, Date, (and list, set) etc strings might need a little additional post-processing for Excel ?

In the case of dates, for example, the default string passed to osascript has either

  • an ISO 8601 form : “2008-06-30T15:38:43+01:00” or
  • uses the string ‘never’

(not sure that Excel would immediately parse either of these as dates at paste-time)

Similarly, I seem to have a recollection that, on paste, Excel parses the uppercase strings ‘TRUE’ and ‘FALSE’ as booleans, but interprets other variants as string literals.

(One could test the kind property of each attribute and do a little post-processing accordingly)

18

That’s a good idea. That would be tinkering above the line.:grinning:

The trick will be capturing a list of User Attributes in a document to help populate the chooser. It’s easy to grab the name of all attributes. But I haven’t figured out an easy way to grab just User Attributes.

19

Lists and sets are simply exported as ;-delimited strings that are placed in one spreadsheet column. Not as useful in that form in a spreadsheet as they are in Tinderbox, but one can do filtering and searches on them.

In my testing Tinderbox booleans exported via the script are interpreted as TRUE or FALSE in both Numbers and Excel. So that’s good.

Dates, on the other hand, come over as non date-time strings in both Numbers and Excel. So it appears that processing is needed both for passing dates to Tinderbox via script and for exporting them. Nice to know the name for the “ISO 8601” format. Good idea about testing for kind, then processing as needed within script.

Meanwhile, the script in its current humble form should help.

2 Likes
20

Good to hear that Booleans need no work.

Re dates,

If you start with a header like:

use AppleScript version "2.4"
use framework "Foundation"
use scripting additions

then lower down, I think you should be able to write things like:

if "date" = tbxKind then
    tell ((current application's NSISO8601DateFormatter's alloc's init)'s ¬
        dateFromString:(strTbxValue)) as date
        short date string & " " & text 1 thru 5 of (time string)
    end tell
else
    strTbxValue
end if

(Though on second thoughts, you will first need to screen out the “never” case)

2 Likes
21

Thanks @sumnerg and others in this tread. One more discovery: that we’d already had this copy/paste discovery, and not even two-and-a-half years ago! (I have spent the past week in every-day flight training, to get back into both legal and safety-related “currency” as a pilot after not flying a plane for two years. Reminder of related principle: until you have used a particular technique or procedure a number of times, it’s not really “yours” and you’ll have to re-learn it eventually. As in this case…)

Have not given the script a try, but will today. My discovery so far is that the cut-and-paste approach works superbly for “straightforward” attributes and values. It easily gets confused with long $Text entries that contain a lot of their own punctuation. Will try the various workarounds for that: Your script; trying to clean up the $Text fields before transfer; just using Export; pasting that one field manually into spreadsheet; etc. Experimentation is The Tinderbox Way.

1 Like
22

And here is an update-update, from looking through the old thread. It’s full of gems, including this one from @galen, about a way to export Attribute Browser views:

Here is my Q: When I tried this just now, I ran into the following impediment – a grayed-out choice for Export as AB.

ABExport

Any guesses on why that would be?

Still have the Script to try …