Tinderbox Forum

Basic How-To Q about exporting from TB to spreadsheet


(Tom Diaz) #23

Hi James,

I just tried it, exporting as AB worked as advertised. Did you select the note(s) in AB view you wanted to export? If I try exporting the AB view without any notes selected, my File/Export/as Attribute Browser is greyed out as well, however, if I select some notes and try exporting “as Attribute Browser” all works well.

Tom


(eastgate) #24

I think the issue here is that the view pane wants to have the focus before exporting


(James Fallows) #25

And, have finally tried the script @sumnerg provided above. Very handy! Still gets a little confused with long, punctuation-dense $Text entries, but that’s an edge case, and by trial and error I can figure out what is confusing the script.
Appreciate it.


(RobTrew) #26

It turns out to be quite fast, though a bit clumsy, to perform a triage of attributes based on whether or not they are members of the set of known system attributes.

Searching lists which are properties is, as you know, faster than searching through local variable lists, and an experiment with binary search turned out to add more code than significant speed :slight_smile:

I think it might be sensible to ask Eastgate support to expose the groups of which attributes are members through the API, so that they can be easily grouped/selected for controls, but in the meanwhile, a basic partition of User vs System attributes in the active document might look something like:

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

-- Rough sketch
-- Rob Trew 2019

property pAttrs : {"AIM", "Abstract", "AccentColor", "AccessDate", "Address", "AdornmentCount", "AdornmentFont", "AgentAction", "AgentCaseSensitive", "AgentPriority", "AgentQuery", "Aliases", "ArticleTitle", "Associates", "Author2", "Author3", "Author4", "Authors", "AutoFetch", "AutoFetchCommand", "Badge", "BadgeMonochrome", "BadgeSize", "Base", "BeforeVisit", "Bend", "BookTitle", "Border", "BorderBevel", "BorderColor", "BorderDash", "CallNumber", "Caption", "CaptionAlignment", "CaptionColor", "CaptionFont", "CaptionOpacity", "CaptionSize", "Checked", "ChildCount", "ChosenWord", "City", "CleanupAction", "ClusterTerms", "Color", "Color2", "Container", "Country", "Created", "Creator", "DEVONthinkGroup", "DEVONthinkLabel", "DOI", "Deck", "DescendantCount", "Direction", "DisplayExpression", "DisplayExpressionDisabled", "DisplayName", "District", "DueDate", "Edict", "EdictDisabled", "Edition", "Email", "EndDate", "EvernoteNotebook", "File", "Fill", "FillOpacity", "Flags", "FormattedAddress", "FullName", "GeocodedAddress", "GridColor", "GridColumns", "GridLabelFont", "GridLabelSize", "GridLabels", "GridOpacity", "GridRows", "HTMLBoldEnd", "HTMLBoldStart", "HTMLCloud1End", "HTMLCloud1Start", "HTMLCloud2End", "HTMLCloud2Start", "HTMLCloud3End", "HTMLCloud3Start", "HTMLCloud4End", "HTMLCloud4Start", "HTMLCloud5End", "HTMLCloud5Start", "HTMLDontExport", "HTMLEntities", "HTMLExportAfter", "HTMLExportBefore", "HTMLExportChildren", "HTMLExportCommand", "HTMLExportExtension", "HTMLExportFileName", "HTMLExportFileNameSpacer", "HTMLExportPath", "HTMLExportTemplate", "HTMLFileNameLowerCase", "HTMLFileNameMaxLength", "HTMLFirstParagraphEnd", "HTMLFirstParagraphStart", "HTMLFont", "HTMLFontSize", "HTMLImageEnd", "HTMLImageStart", "HTMLIndentedParagraphEnd", "HTMLIndentedParagraphStart", "HTMLItalicEnd", "HTMLItalicStart", "HTMLLinkExtension", "HTMLListEnd", "HTMLListItemEnd", "HTMLListItemStart", "HTMLListStart", "HTMLMarkDown", "HTMLMarkupText", "HTMLOrderedListEnd", "HTMLOrderedListItemStart", "HTMLOrderedListStart", "HTMLOrderedsListItemEnd", "HTMLOverwriteImages", "HTMLParagraphEnd", "HTMLParagraphStart", "HTMLPreviewCommand", "HTMLQuoteHTML", "HTMLStrikeEnd", "HTMLStrikeStart", "HTMLUnderlineEnd", "HTMLUnderlineStart", "Height", "HideKeyAttributes", "HoverExpression", "HoverFont", "HoverImage", "HoverOpacity", "ID", "ISBN", "ImageCount", "InboundLinkCount", "InteriorScale", "IrisAngle", "IrisRadius", "IsAdornment", "IsAlias", "IsComposite", "IsMultiple", "IsPrototype", "IsTemplate", "Issue", "Journal", "KeyAttributeDateFormat", "KeyAttributeFont", "KeyAttributeFontSize", "KeyAttributes", "LastFetched", "Latitude", "LeafBase", "LeafBend", "LeafDirection", "LeafTip", "LeftMargin", "LineSpacing", "Lock", "Longitude", "MapBackgroundAccentColor", "MapBackgroundColor", "MapBackgroundColor2", "MapBackgroundFill", "MapBackgroundFillOpacity", "MapBackgroundPattern", "MapBackgroundShadow", "MapBodyTextColor", "MapBodyTextSize", "MapScrollX", "MapScrollY", "MapTextSize", "Modified", "MyBoolean", "MyColor", "MyDate", "MyInterval", "MyList", "MyNumber", "MySet", "MyString", "NLNames", "NLOrganizations", "NLPlaces", "Name", "NameAlignment", "NameBold", "NameColor", "NameFont", "NameLeading", "NameStrike", "NeverComposite", "NoSpelling", "NoteURL", "NotesFolder", "NotesID", "NotesModified", "OnAdd", "OnJoin", "OnRemove", "OnVisit", "Opacity", "Organization", "OutboundLinkCount", "OutlineBackgroundColor", "OutlineColorSwatch", "OutlineDepth", "OutlineOrder", "OutlineTextSize", "Pages", "ParagraphSpacing", "Path", "Pattern", "PlainLinkCount", "PlotBackgroundColor", "PlotBackgroundOpacity", "PlotColor", "PlotColorList", "PostalCode", "Private", "Prototype", "PrototypeBequeathsChildren", "PrototypeHighlightColor", "PublicationCity", "PublicationYear", "Publisher", "RSSChannelTemplate", "RSSItemLimit", "RSSItemTemplate", "RawData", "ReadCount", "ReadOnly", "RefFormat", "RefKeywords", "RefType", "ReferenceRIS", "ReferenceTitle", "ReferenceURL", "Requirements", "ResetAction", "RightMargin", "Role", "Rule", "RuleDisabled", "ScrivenerID", "ScrivenerKeywords", "ScrivenerLabel", "ScrivenerLabelID", "ScrivenerNote", "ScrivenerStatus", "ScrivenerStatusID", "ScrivenerType", "Searchable", "SelectionCount", "Separator", "Shadow", "ShadowBlur", "ShadowColor", "ShadowDistance", "Shape", "ShowTitle", "SiblingOrder", "SimplenoteKey", "SimplenoteModified", "SimplenoteSync", "SimplenoteTags", "SimplenoteVersion", "SmartQuotes", "Sort", "SortAlso", "SortAlsoTransform", "SortBackward", "SortBackwardAlso", "SortTransform", "SourceCreated", "SourceModified", "SourceURL", "StartDate", "State", "Sticky", "Subtitle", "SubtitleColor", "SubtitleOpacity", "SubtitleSize", "TableExpression", "TableHeading", "Tabs", "Tags", "Telephone", "Text", "TextAlign", "TextBackgroundColor", "TextColor", "TextExportTemplate", "TextFont", "TextFontSize", "TextLength", "TextLinkCount", "TextPaneRatio", "TextPaneWidth", "TextSidebar", "TextWindowHeight", "TextWindowWidth", "TimelineAliases", "TimelineBand", "TimelineBandLabelColor", "TimelineBandLabelOpacity", "TimelineBandLabels", "TimelineColor", "TimelineDescendants", "TimelineEnd", "TimelineEndAttribute", "TimelineGridColor", "TimelineMarker", "TimelineScaleColor", "TimelineScaleColor2", "TimelineStart", "TimelineStartAttribute", "Tip", "TitleBackgroundColor", "TitleFont", "TitleForegroundColor", "TitleHeight", "TitleOpacity", "Twitter", "URL", "UUID", "User", "ViewInBrowser", "Visits", "Volume", "WatchFolder", "WebLinkCount", "WeblogPostID", "Width", "WordCount", "Xpos", "Ypos", "mt_allow_comments", "mt_allow_pings", "mt_convert_breaks", "mt_keywords"}

-- TEST ---------------------------------------------------
on run
    -- List of user (vs system) attributes in front document:
    
    tell application "Tinderbox 8"
        set ks to (name of attributes of front document)
    end tell
    
    script isUserAttr
        on |λ|(x)
            pAttrs does not contain x
        end |λ|
    end script
    
    -- set userAttribs to filter(isUserAttr, ks)
    
    set ab to partition(isUserAttr, ks)
    
    -- User attributes
    item 1 of ab
    
    -- Remaining (i.e. system) attributes
    -- item 2 of ab 
end run


-- GENERIC FUNCTIONS --------------------------------------
-- https://github.com/RobTrew/prelude-applescript

-- filter :: (a -> Bool) -> [a] -> [a]
on filter(f, xs)
    tell mReturn(f)
        set lst to {}
        set lng to length of xs
        repeat with i from 1 to lng
            set v to item i of xs
            if |λ|(v, i, xs) then set end of lst to v
        end repeat
        return lst
    end tell
end filter


-- mReturn :: First-class m => (a -> b) -> m (a -> b)
on mReturn(f)
    -- 2nd class handler function lifted into 1st class script wrapper. 
    if script is class of f then
        f
    else
        script
            property |λ| : f
        end script
    end if
end mReturn

-- partition :: predicate -> List -> (Matches, nonMatches)
-- partition :: (a -> Bool) -> [a] -> ([a], [a])
on partition(f, xs)
    tell mReturn(f)
        set ys to {}
        set zs to {}
        repeat with x in xs
            set v to contents of x
            if |λ|(v) then
                set end of ys to v
            else
                set end of zs to v
            end if
        end repeat
    end tell
    {ys, zs}
end partition


(RobTrew) #27

And if we really need to shave off a few milliseconds (very unlikely to be worth it, I think) we can use an NSSet in lieu of an AppleScript list:

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

-- Rough sketch of NSSet version

-- NB All NSSet references **must** be set to **missing value** before the end of the script.

-- Rob Trew 2019

property pAttrs : {"AIM", "Abstract", "AccentColor", "AccessDate", "Address", "AdornmentCount", "AdornmentFont", "AgentAction", "AgentCaseSensitive", "AgentPriority", "AgentQuery", "Aliases", "ArticleTitle", "Associates", "Author2", "Author3", "Author4", "Authors", "AutoFetch", "AutoFetchCommand", "Badge", "BadgeMonochrome", "BadgeSize", "Base", "BeforeVisit", "Bend", "BookTitle", "Border", "BorderBevel", "BorderColor", "BorderDash", "CallNumber", "Caption", "CaptionAlignment", "CaptionColor", "CaptionFont", "CaptionOpacity", "CaptionSize", "Checked", "ChildCount", "ChosenWord", "City", "CleanupAction", "ClusterTerms", "Color", "Color2", "Container", "Country", "Created", "Creator", "DEVONthinkGroup", "DEVONthinkLabel", "DOI", "Deck", "DescendantCount", "Direction", "DisplayExpression", "DisplayExpressionDisabled", "DisplayName", "District", "DueDate", "Edict", "EdictDisabled", "Edition", "Email", "EndDate", "EvernoteNotebook", "File", "Fill", "FillOpacity", "Flags", "FormattedAddress", "FullName", "GeocodedAddress", "GridColor", "GridColumns", "GridLabelFont", "GridLabelSize", "GridLabels", "GridOpacity", "GridRows", "HTMLBoldEnd", "HTMLBoldStart", "HTMLCloud1End", "HTMLCloud1Start", "HTMLCloud2End", "HTMLCloud2Start", "HTMLCloud3End", "HTMLCloud3Start", "HTMLCloud4End", "HTMLCloud4Start", "HTMLCloud5End", "HTMLCloud5Start", "HTMLDontExport", "HTMLEntities", "HTMLExportAfter", "HTMLExportBefore", "HTMLExportChildren", "HTMLExportCommand", "HTMLExportExtension", "HTMLExportFileName", "HTMLExportFileNameSpacer", "HTMLExportPath", "HTMLExportTemplate", "HTMLFileNameLowerCase", "HTMLFileNameMaxLength", "HTMLFirstParagraphEnd", "HTMLFirstParagraphStart", "HTMLFont", "HTMLFontSize", "HTMLImageEnd", "HTMLImageStart", "HTMLIndentedParagraphEnd", "HTMLIndentedParagraphStart", "HTMLItalicEnd", "HTMLItalicStart", "HTMLLinkExtension", "HTMLListEnd", "HTMLListItemEnd", "HTMLListItemStart", "HTMLListStart", "HTMLMarkDown", "HTMLMarkupText", "HTMLOrderedListEnd", "HTMLOrderedListItemStart", "HTMLOrderedListStart", "HTMLOrderedsListItemEnd", "HTMLOverwriteImages", "HTMLParagraphEnd", "HTMLParagraphStart", "HTMLPreviewCommand", "HTMLQuoteHTML", "HTMLStrikeEnd", "HTMLStrikeStart", "HTMLUnderlineEnd", "HTMLUnderlineStart", "Height", "HideKeyAttributes", "HoverExpression", "HoverFont", "HoverImage", "HoverOpacity", "ID", "ISBN", "ImageCount", "InboundLinkCount", "InteriorScale", "IrisAngle", "IrisRadius", "IsAdornment", "IsAlias", "IsComposite", "IsMultiple", "IsPrototype", "IsTemplate", "Issue", "Journal", "KeyAttributeDateFormat", "KeyAttributeFont", "KeyAttributeFontSize", "KeyAttributes", "LastFetched", "Latitude", "LeafBase", "LeafBend", "LeafDirection", "LeafTip", "LeftMargin", "LineSpacing", "Lock", "Longitude", "MapBackgroundAccentColor", "MapBackgroundColor", "MapBackgroundColor2", "MapBackgroundFill", "MapBackgroundFillOpacity", "MapBackgroundPattern", "MapBackgroundShadow", "MapBodyTextColor", "MapBodyTextSize", "MapScrollX", "MapScrollY", "MapTextSize", "Modified", "MyBoolean", "MyColor", "MyDate", "MyInterval", "MyList", "MyNumber", "MySet", "MyString", "NLNames", "NLOrganizations", "NLPlaces", "Name", "NameAlignment", "NameBold", "NameColor", "NameFont", "NameLeading", "NameStrike", "NeverComposite", "NoSpelling", "NoteURL", "NotesFolder", "NotesID", "NotesModified", "OnAdd", "OnJoin", "OnRemove", "OnVisit", "Opacity", "Organization", "OutboundLinkCount", "OutlineBackgroundColor", "OutlineColorSwatch", "OutlineDepth", "OutlineOrder", "OutlineTextSize", "Pages", "ParagraphSpacing", "Path", "Pattern", "PlainLinkCount", "PlotBackgroundColor", "PlotBackgroundOpacity", "PlotColor", "PlotColorList", "PostalCode", "Private", "Prototype", "PrototypeBequeathsChildren", "PrototypeHighlightColor", "PublicationCity", "PublicationYear", "Publisher", "RSSChannelTemplate", "RSSItemLimit", "RSSItemTemplate", "RawData", "ReadCount", "ReadOnly", "RefFormat", "RefKeywords", "RefType", "ReferenceRIS", "ReferenceTitle", "ReferenceURL", "Requirements", "ResetAction", "RightMargin", "Role", "Rule", "RuleDisabled", "ScrivenerID", "ScrivenerKeywords", "ScrivenerLabel", "ScrivenerLabelID", "ScrivenerNote", "ScrivenerStatus", "ScrivenerStatusID", "ScrivenerType", "Searchable", "SelectionCount", "Separator", "Shadow", "ShadowBlur", "ShadowColor", "ShadowDistance", "Shape", "ShowTitle", "SiblingOrder", "SimplenoteKey", "SimplenoteModified", "SimplenoteSync", "SimplenoteTags", "SimplenoteVersion", "SmartQuotes", "Sort", "SortAlso", "SortAlsoTransform", "SortBackward", "SortBackwardAlso", "SortTransform", "SourceCreated", "SourceModified", "SourceURL", "StartDate", "State", "Sticky", "Subtitle", "SubtitleColor", "SubtitleOpacity", "SubtitleSize", "TableExpression", "TableHeading", "Tabs", "Tags", "Telephone", "Text", "TextAlign", "TextBackgroundColor", "TextColor", "TextExportTemplate", "TextFont", "TextFontSize", "TextLength", "TextLinkCount", "TextPaneRatio", "TextPaneWidth", "TextSidebar", "TextWindowHeight", "TextWindowWidth", "TimelineAliases", "TimelineBand", "TimelineBandLabelColor", "TimelineBandLabelOpacity", "TimelineBandLabels", "TimelineColor", "TimelineDescendants", "TimelineEnd", "TimelineEndAttribute", "TimelineGridColor", "TimelineMarker", "TimelineScaleColor", "TimelineScaleColor2", "TimelineStart", "TimelineStartAttribute", "Tip", "TitleBackgroundColor", "TitleFont", "TitleForegroundColor", "TitleHeight", "TitleOpacity", "Twitter", "URL", "UUID", "User", "ViewInBrowser", "Visits", "Volume", "WatchFolder", "WebLinkCount", "WeblogPostID", "Width", "WordCount", "Xpos", "Ypos", "mt_allow_comments", "mt_allow_pings", "mt_convert_breaks", "mt_keywords"}

-- TEST ---------------------------------------------------
on run
    -- List of user (vs system) attributes in front document:
    
    set setSysAttrs to setFromList(pAttrs)
    
    tell application "Tinderbox 8"
        set ks to (name of attributes of front document)
    end tell
    
    script isUserAttr
        on |λ|(x)
            not setMember(x, setSysAttrs)
        end |λ|
    end script
    
    -- set userAttribs to filter(isUserAttr, ks)
    
    set ab to partition(isUserAttr, ks)
    
    -- NB any ObjC pointers need to be cleared.
    -- The script can not be saved until this is done. 
    set setSysAttrs to missing value
    
    -- User attributes
    item 1 of ab
    
    -- Remaining (i.e. system) attributes
    -- item 2 of ab 
end run


-- GENERIC FUNCTIONS --------------------------------------
-- https://github.com/RobTrew/prelude-applescript


-- mReturn :: First-class m => (a -> b) -> m (a -> b)
on mReturn(f)
    -- 2nd class handler function lifted into 1st class script wrapper. 
    if script is class of f then
        f
    else
        script
            property |λ| : f
        end script
    end if
end mReturn


-- partition :: predicate -> List -> (Matches, nonMatches)
-- partition :: (a -> Bool) -> [a] -> ([a], [a])
on partition(f, xs)
    tell mReturn(f)
        set ys to {}
        set zs to {}
        repeat with x in xs
            set v to contents of x
            if |λ|(v) then
                set end of ys to v
            else
                set end of zs to v
            end if
        end repeat
    end tell
    {ys, zs}
end partition


-- NB All names of NSSets should be set to *missing value*
-- before the script exits.
-- ( scpt files can not be saved if they contain ObjC pointer values )
-- setFromList :: Ord a => [a] -> Set a
on setFromList(xs)
    set ca to current application
    ca's NSSet's ¬
        setWithArray:(ca's NSArray's arrayWithArray:(xs))
end setFromList


-- setMember :: Ord a => a -> Set a -> Bool
on setMember(x, objcSet)
    missing value is not (objcSet's member:(x))
end setMember

(Sumner Gerard) #28

Yes, and when lives are at stake use checklists!

In response to prompts from @ComplexPoint, here’s a version of the “simple” script that converts dates, in case you are exporting those to spreadsheet.

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

# 1. List the attributes in line below, each between quotes.  Add/remove/reorder as needed
set grabAttribs to {"Name", "SentDate", "From", "Attachments", "PeopleMentioned", "Topics", "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
			tell attribute attrName
				set attribVals's item i to value
				if item 1's kind 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

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

Eventually a more complex version that prompts for attributes should be possible. Then the script could live full-time up in the menu as there would less be need to edit the first two lines.

I can see how it would be useful for scripts to be able to differentiate easily between user and non-user attributes. Meanwhile, will try to absorb and implement the partition ideas suggested here. Thanks @ComplexPoint.


(Mark Anderson) #29

I wonder, does Discourse’s markdown support AppleScript syntax. I think the method to do this should be: 3 x backticks + space + applescript. BUt that doesn’t work. Maybe it’s something that can/should be added, noticing the way syntax colouring is working on the last sample above. Whilst that is better than no formatting - so as to stop breakage like quote style auto-conversion, mis-parsed syntax colouring can make it harder to read the code samples in situ.

I ask as this isn’t really my area of expertise and for Tinderbox code the default ‘untyped’ syntax colouring seems to work pretty well.


(RobTrew) #30

Discourse and wiki software generally seems to have a problem with single quotes in code (they are inflexibly read as string quotes, suspending syntax analysis till the next one, and bleeding red everywhere).

The AppleScript ‘genitive’ apostrophe is a perennial victim of this. (In many cases you can side-step it, as it happens, by using the syntactic variant of flipping the keywords and using of in lieu of apostrophe. i.e. replacing object’s property with property of object).

document's name

vs

name of document

(Sumner Gerard) #31

Here is a revised script with a dialog that removes almost all the fiddliness of exporting from Tinderbox to spreadsheet while retaining flexibility.

Click to reveal revised script with 'edit the box' dialog
# 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 NoteURL 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. Non-contiguous multi levels ok." & return & return & "2. Edit the box below to choose Tinderbox attributes in the order that you want them to appear in the spreadsheet. Spell their names exactly. Add/delete/reorder 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 valid entries in the box:" & return & return & "   Name MyString Path Text ," & return & "   Name MyString MyDate MySet Text ;" & return & "   Name  Text MySet MyString MyDate   ;" & return & "   Name   Text   MyString   NoteURL       t" & return

set theResponse to display dialog dialogText default answer entryString buttons {"Cancel", "Continue"} default button "Continue" with title "Tinderbox 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 8"
	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
				# add list of this attribute's values to the list of lists
				set attribVals's item i to value
				# if attribute is date then convert format
				if item 1's kind 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

Just activate the Script Menu and save the script with a name like ‘Export Notes to Spreadsheet’ in the right folder. On my machine that’s Library > Scripts > Tinderbox 8. I first saved the script as usual, giving it a name. Then in Finder I held down the option key and chose Go > Library and navigated to the Script folder. I added a Tinderbox 8 folder there and dragged the script into it. (Or for keyboard shortcut access place the script in a Automator Service, a.k.a. Quick Action) or run it with a favorite third-party launcher, etc.) If placed in the Script Menu the script usually remembers the last entry in the box, making quick edits and adjustments easy.

Be sure to give permission, if asked, for the Script Menu to control Tinderbox. Permissions can be reviewed at System Preferences > Security & Privacy > Privacy > Automation. It may also be necessary to make sure Script Editor and Tinderbox 8 are listed and checked at System Preferences > Security & Privacy > Privacy > Accessibility.

This is the dialog, which also serves as a checklist of sorts when memory fails, though there isn’t much to remember.


(Mark Anderson) #32

Thanks for sharing that, much appreciated.


(RobTrew) #33

Perhaps worth adding a quoted / not quoted option ?
(Or perhaps string types only quoted)

Pasting here, for example, I find that anything "quoted " is read by my copy of Excel as a string (rather than date, boolean, number etc)


(Sumner Gerard) #34

For Excel for the Mac 16.25 I’ve found this works best: Use ; as the delimiter and in ‘Text to Columns’ choose ‘Delimited’ then ‘Semicolon’ and make sure ‘Text qualifier:’ is set to " (straight double quote) . Then boolean and dates are interpreted as such without surrounding quotes. But Excel seems to have trouble with long text with embedded linefeeds and such.

Numbers 6.0, on the other hand, seems to accept the various combinations with ease after a click or two in the ‘Table data was imported. Adjust settings’ popup. I was surprised by this because I remember concluding a few years ago that Excel was much better at handling various kinds of somewhat messy “real-world” csv.

Will be interested in hearing about other problems.