Applescript to Export/Copy selected notes in Attribute Browser View to Clipboard to use in Excel?

[TomD (Tom Diaz)]

I am not sure if this exists but does anyone know of an applescript to export a selected group of notes to the clipboard in the Attribute Browser as a tsv for import into Excel?

Use case, AB is SUCH a useful view in tinderbox (my favorite by the way) it would be useful to be to interchange this columnar view with Excel. I know that copying to clipboard in AB has been requested in the past (hence now looking for an applescript method) and export Text-> csv remains an option for the entire file but thought I would post to get other ideas.

Thanks
Tom

[sumnerg (Sumner Gerard)]

AppleScript can easily grab attribute values from just the notes you have selected. You can select them in AB if that is more convenient. Or you can select them in other views. AppleScript won’t know specifically how you have configured AB, so this may not be exactly what you are looking for.

Here are a couple of scripts you might try. With each you first select the notes you want and then run. The output is character-separated-values on the clipboard for pasting into Excel, or Numbers or wherever.

Script 1

This is a bare-bones script that has the virtue of simplicity.

tell application "Tinderbox 9"
	tell front document
		tell selections
			set theNames to name
			set theTexts to attribute "Text"'s value
			set theURLs to attribute "URL"'s value
			set theTags to attribute "Tags"'s value
		end tell
	end tell
end tell

set exportStr to "Name" & tab & "Text" & tab & "URL" & tab & "Tags" & return

repeat with n from 1 to length of theNames
	set exportStr to exportStr & item n of theNames & tab & "\"" & item n of theTexts & "\"" & tab & item n of theURLs & tab & item n of theTags & return
end repeat

set the clipboard to exportStr
return exportStr -- to view in "Result" pane

Grabbing just the attributes you want and arranging them in columns in the order you want requires manual adjustments to the code. That entails about the same level of bother and difficulty as setting up and revising export templates in Tinderbox. As with export templates, you have to fiddle each time your requirements change.

Script 2

The following is a more ambitious script that attempts to make it easy to reconfigure the output without adjusting the AppleScript code. You specify which columns of attributes you want, in what order, and with what delimiter, by simply “filling in a box” within a prompt.

# Select Tinderbox notes (non-contiguous, multiple levels ok) and click run.
# Places quoted character-separated values (CSV) on clipboard for pasting wherever.
# v. 1.2 -- includes simple date conversion and "edit the box" dialog

property entryString : "Name Text t"

set dialogText to "Export selected notes from Tinderbox to the system clipboard in a csv (character-separated value) format for pasting into spreadsheets or other applications." & return & return & "1. Select notes in Tinderbox" & return & return & "2. Edit the box below to select Tinderbox attributes and order them as you want them to appear in the spreadsheet. Use the exact spelling. Add/delete/reorder in the box as needed. Use one or more spaces between names." & return & return & "3. After the last attribute name enter one or more spaces followed by the delimiter to be used to separate items in the exported csv." & return & return & "4. Click 'Continue' to export to the clipboard for pasting. " & return & return & "                  ------------------------" & return & return & "For the delimiter enter a single character:" & return & return & "   t  for tab" & return & "   ,  for comma" & return & "   ;  for semicolon" & return & "   |  for pipe" & return & "           and so on..." & return & return & "Examples of possible entries in the box:" & return & return & "   Name MyString Path Text ," & return & "   Name MyString MyDate MySet Text ;" & return & "   Name MySet MyString MyDate Text   ;" & return & "   Name   Text   MyString   MySet    t" & return

set theResponse to display dialog dialogText default answer entryString buttons {"Cancel", "Continue"} default button "Continue" with title "Tindebox Export to Spreadsheet"

set entryString to text returned of theResponse
tell entryString
	if character -2 is not " " then error "Can't find a delimiter at end"
	set myDelimiter to last character
	if myDelimiter is "t" then set myDelimiter to tab
	set grabAttribs to words of text 1 thru -2 --if "t" present ignore as last "word"
end tell

--  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 9"
	tell front document's selections -- (note use of plural)
		repeat with i from 1 to numCols
			set attrName to grabAttribs's item i
			tell attribute attrName
				set attribVals's item i to value
				if item 1's type is "date" then copy my convertDates(attribVals's item i) to attribVals's item i
			end tell
			# 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

display notification "Tinderbox data ready for pasting."

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


-- handlers (subroutines)

to convertDates(listOfIsoDates)
	-- convert list of Tinderbox ISO 8601 dates to spaced date-time string for spreadsheets
	set convertedDates to {}
	repeat with i from 1 to listOfIsoDates's length
		tell listOfIsoDates's item i
			if it is "never" then
				set convertedDates's end to ""
			else
				set convertedDates's end to its text 1 thru 10 & " " & text 12 thru 19
			end if
		end tell
	end repeat
	return convertedDates
end convertDates

This code in this second one is clearly is more “involved” (be sure to scroll down to grab the whole thing). But in theory you won’t need to modify it even when your needs change. You can put it in the script menu where it becomes a simple menu pick. For me this script is easier to use than the first script above or export templates within Tinderbox, when exporting to Excel or Numbers (or other apps that accept character-separated values).

[TomD (Tom Diaz)]

Wow. Great script! Brilliant!

Works wonderfully in AB view! Ready to copy and paste into Excel.
I too am using Script 2.Made my day!

Many thanks Sumner
Tom

[satikusala (Michael Becker)]

Hi all, BTW, there are two alteartive approaches for this.

1. In the outline review, add the columns you want to see. Copy the notes and then paste them in excel.
2. Create an export template the produces a table. Look at it in preview. Copy the preview, paste it in excel.

[TomD (Tom Diaz)]

Thanks Michael,

Great suggestions and alternative approaches as well. I did not think of either.

Tom

[sumnerg (Sumner Gerard)]

@satikusala

two alteartive approaches for this

Both involve quite a bit more work than using Script 2. Suggest giving it a try.

Also, can see an advantage to making the selection in AB view. The notes can be quickly grouped for selection, without coding.

[sumnerg (Sumner Gerard)]

@TomD

Works wonderfully in AB view! Ready to copy and paste into Excel.

Thanks for the nice feedback! Tinderbox has a good implementation of AppleScript. Documentation for AppleScript, though, is not always consistent or clear. It’s especially “unorthodox” for Tinderbox. That can make it a challenge to write scripts, even though I’ve found debugging is easier than for TB action code.

AppleScripts, once written, can be easy to use.

That second script above has saved me a lot of time that otherwise would have been spent on unproductive fiddling with templates and such. Plus it’s reusable on other Tinderbox documents. Unlike export templates or columns, it doesn’t have to be set up in each new document. Just “fill in the box.”

[TomD (Tom Diaz)]

Wonderful script to export selected notes with configurable attributes.
This has “saved my bacon” many times.

BTW, this code also works in Table view if you need it (bonus)

Feature Request…
Problem: sometimes Text has code and other nasty characters like " ’ tabs, semicolons and commas. which destroy importing into a spreadsheet.

Request: Is there a way in Applescript to “clean” $Text before import?

Tom

[sumnerg (Sumner Gerard)]

@TomD

Is there a way in Applescript to “clean” $Text before import

Yes, if you can specify which characters you want to remove then I can suggest a way that may work for you.

For most cases copy-pasting from the new table view now is easier. But if you need to replace characters then a script is probably still the way to go.

[TomD (Tom Diaz)]

Thanks Sumner,
Awesome script by the way!!!

Here is what I am thinking

1. Remove: all empty lines and preceding and trailing whitespaces to start
2. Begin and end the $Text string with a single quote instead of a double quote

I think that should get us most of the way there
Other thoughts welcomed

Tom

[sumnerg (Sumner Gerard)]

Can you give a specific example of troublesome text?

Do you need to “clean” whitespace from each line, or from the beginning and end of the entire string from $Text.

Are you trying to get this into Excel? Or some other app?

[TomD (Tom Diaz)]

1. Yes, I will attach some sample text that caused this question. It actually came form aTBRef. I was trying to compare/summarize the different methods for Dictionaries, Lists/Sets etc in a Numbers spreadsheet. I initially tried to use tables but I needed to capture just 3 attributes only: $Name, $Text and $NoteURL into the spreadsheet of selected notes. Your script gave me this flexibility.

2. Yes, I would like to “clean” / “trim” any leading whitespace from the beginning and end of the text. In addition, much of the $Text in question has double lines and normal quotes within the text. Yes, ideally I would like to condense and remove all emply lines from the body of the text as well if possible. Lastly, I think, since the $Text has quotation marks within the text, it would be best to start the $Text value with a single quotation and end with a single quotation to define the boundaries.

3. I was experimenting with Numbers, I had not tried Excel. Many times, I find import, export from Numbers is easier but if it works in Excel, then fine as well.

Basically, what I wanted were 3 columns using your custom attibute script:
| Name | Text | NoteURL |

---- $Text in question from 4 notes -----

—$Text from Note 1 ----

Dictionary.add(itemDict)

This reads a Dictionary-type argument itemDict from which a key and a value are parsed.

$MyDictionary = $MyDictionary.add({apple:green});

Note that quotes are not needed around the key and value.

If key does not exist, that key is created with a value of value.

If key exists, key is given value value. This replaces any/all existing values for this key.

Assume $MyDictionary has no ‘apple’ key. Example:

$MyDictionary = $MyDictionary.add({apple:fruit});

The key ‘apple’ is added and now has value ‘fruit’

$MyDictionary = $MyDictionary.add({apple:green});

The key ‘apple’ now has a new value ‘green’. Now assume the ‘apple’ key has multiple values of ‘fruit;green;red’:

$MyDictionary = $MyDictionary.add({apple:pie});

Now the value is just ‘pie’ because an .add() operator replaces all existing value(s).

This operator is also equivalent to:

Dictionary[“key”] = “value”;

To add an additional value(s) to existing value(s), see Dictionary.extend().

To remove a key—and any value(s) it has—from the Dictionary, there is no operator but instead the key string is deleted using a minus operator: see ‘Deleting key:value pairs’ here.

As from v9.5.2, the .add() operator accepts quoted strings. The following expressions are equivalent:

$MyDictionary.add({1:able}}

$MyDictionary.add(“{1:able}”}

But do not use either of the following example syntax:

$MyDictionary.add({“1:able”}} WRONG!

$MyDictionary.add({“1”:“able”}} WRONG!

Using offset addresses within itemDict

Either the keyStr or the valueStr may need to be calculated variable, for instance the valueStr might need to be the value of $MyString(“Some note”) or in a loop, $MyString(loopVar). These cannot be resolved within the .add() operator but itemDict can be a variable. Thus, in a loop, rather than:

vDict = vDict.add({$Name(aState)+“:”+$Color(aState)}); WRONG!

use:

 vList.each(aState){

// Dict.add() can't resolve attribute offset value

// so store the complete string in a string

// variable and pass a single var argument to .add()

var:string vPair = $Name(aState)+":"+$Color(aState);

vDict = vDict.add({vPair});

};

Legacy form (pre-v9.5.0)

Dictionary.add(keyStr, valueStr)

This sets a keyStr to the valueStr.

If keyStr does not exist, that key is created with a value of valueStr.

If keyStr exists, keyStr is given value valueStr. This replaces any/all existing values for this key.

Assume $MyDictionary has no ‘apple’ keyStr. Example:

$MyDictionary = $MyDictionary.add(“apple”,“fruit”);

The keyStr ‘apple’ is added and now has valueStr ‘fruit’

$MyDictionary = $MyDictionary.add(“apple”,“green”);

The keyStr ‘apple’ now has a new value ‘green’. Now assume the ‘apple’ key has multiple values of ‘fruit;green;red’:

$MyDictionary = $MyDictionary.add(“apple”,“pie”);

Now the value is just ‘pie’ because an .add() operator replaces all existing valueStr(s).

—$Text from Note 2 —

Dictionary.contains(keyStr)

The expression:

$MyDictionary.contains(key)

is true if the dictionary contains the designated key.

Note that keyStr is literal string and not a regex pattern (unlike the operator’s use chained with some other data types).

Dictionaries are case-sensitive, but Dictionary.icontains(key) is available for case-insensitive matching.

—$Text from Note 3 —

Dictionary.count()

Dictionary.count

A Number-type property. Returns the number of keys in the dictionary (by their nature all keys are discrete so there is not potential of duplication in the count). If:

$MyDictionary = {cat:animal; dog:animal; rock: mineral};

$MyNumber = $MyDictionary.count;

MyNumber is now 3.

Dictionary.count and Dictionary.size are interchangeable. Use which ever seems more intuitive.

—Bonus — Text from Note 4 ----

Dictionary.extend(itemDict)

This reads a Dictionary-type argument itemDict from which a key and a value are parsed. It adds the value string to the value(s) of a key.

If key does not exist, that key is created with a value of value.

If key exists, value is appended to key’s existing value(s).

Assume $MyDictionary has no ‘pear’ key. Example:

$MyDictionary = $MyDictionary.extend({pear:fruit});

The key ‘pear’ is added and now has value ‘fruit’. Next:

$MyDictionary = $MyDictionary.extend({pear:green});

The key ‘pear’ now has an additional new value ‘green’, but the overall value is now a list and the key:value pair is pear:fruit;green.

To set the new valueStr so it replaces all existing value(s) see Dictionary.add().

Note that quotes are not used around either/both the keyStr and valueStr. As from v9.5.2, the .extend() operator accepts quoted strings. The following expressions are equivalent:

$MyDictionary.extend({1:able}}

$MyDictionary.extend(“{1:able}”}

But do not use either of the following example syntax:

$MyDictionary.extend({“1:able”}} WRONG!

$MyDictionary.extend({“1”:“able”}} WRONG!

Dictionary.extend(keyStr, valueStr)

This adds the valueStr string to the value(s) of a keyStr.

If keyStr does not exist, that key is created with a value of valueStr.

If keyStr exists, valueStr is appended to keyStr’s existing value(s).

Assume $MyDictionary has no ‘pear’ keyStr. Example:

$MyDictionary = $MyDictionary.extend(“pear”,“fruit”);

The keyStr ‘pear’ is added and now has valueStr ‘fruit’. Next:

$MyDictionary = $MyDictionary.extend(“pear”,“green”);

The keyStr ‘pear’ now has an additional new valueStr ‘green’, but the overall value is now a list and the key:value air is pear:fruit;green.

To set the new valueStr so it replaces all existing value(s) see Dictionary.add().

Dictionary.extend(dictStr)

From v9.5.0, the Dictionary.extend() operator takes a single argument, a dictionary—using the new {} syntax of key:pair elements which will extend the current elements.

$MyDictionary = $MyDictionary.extend({pear:green});

Note that quotes are not used around either/both the keyStr and valueStr. As from v9.5.2, the .extend() operator accepts quoted strings. The following expressions are equivalent:

$MyDictionary.extend({1:able}}

$MyDictionary.extend(“{1:able}”}

But do not use either of the following example syntax:

$MyDictionary.extend({“1:able”}} WRONG!

$MyDictionary.extend({“1”:“able”}} WRONG!

Using offset addresses within itemDict

Either the keyStr or the valueStr may need to be calculated variable, for instance the valueStr might need to be the value of $MyString(“Some note”) or in a loop, $MyString(loopVar). These cannot be resolved within the .add() operator but itemDict can be a variable. Thus, in a loop, rather than:

vDict = vDict.extend({$Name(aState)+“:”+$Color(aState)}); WRONG!

use:

 vList.each(aState){

// Dict.extend() can't resolve attribute offset value

// so store the complete string in a string

// variable and pass a single var argument to .extend()

var:string vPair = $Name(aState)+":"+$Color(aState);

vDict = vDict.extend({vPair});

};

---

NB: I probably picked the hardest text as a representative case since it has html attributes, quotes and spaces but if it works here, it will work in most other places.

Thanks Sumner

Tom

[mwra (Mark Anderson)]

There are no blank lines in the aTbRef (I’ve checked the source). I think people are mistaking the HTML render (i.e. CSS styling) and misreading line-spacing for blank lines, for example:

---

Aside, I don’t think cross-reading the detail of the action codes for List/Set-types vs Dictionary-type is going to give much insight into the differences of the two, and I assume the why/when of using a given type. Trying to determining the ‘right type’ type to use can be a false errand. Whilst there are occasional wrong choices (e.g. using a single-value type attribute when you need to have multiple values) the ‘right’ choice is more slippery and there are normally several ways to the same end. If this needs discussing further, I suggest starting a new thread on that, to ensure we’re solving the intended problem.

[TomD (Tom Diaz)]

In this sample text, I added blank lines before And after the body of the text to simulate

More correctly, I was just using ATBRef as an example Along with my edited portions

Thanks
Tom

[sumnerg (Sumner Gerard)]

@TomD

I probably picked the hardest text as a representative case

Well, that text is a bit more complex than I was expecting.

If you’re not satisfied with the contents you end up with in a Numbers cell, then you could consider using the REGEX.EXTRACT and SUBSTITUTE functions. They allow you to manipulate and extract text in much the same way you might do in Tinderbox.

It’s probably easier doing the cleanup within Tinderbox or Numbers than cooking up something in AppleScript.

However, here is a generic AppleScript approach to removing blank lines, in case that helps give you any ideas.

set theTestStr to "

a line here
another line I want

"

removeBlankLines(theTestStr)

on removeBlankLines(aString)
	set outStr to ""
	repeat with aLine in paragraphs of aString
		if length of aLine > 0 then
			set outStr to outStr & aLine & return
		end if
	end repeat
	return outStr
end removeBlankLines

[archurhh (Art Currim)]

Side comment - when testing I recommend pasting data tables both into Excel as well as Numbers, each tends to ‘like’ content differently. Google Sheets, as well.

[TomD (Tom Diaz)]

Many thanks to all, Sumner thank you for the code. This is a great start. I will admit, I am an Applescript newbie and it really helps with starter code that I can modify as I learn by example and doing.

Tom