turning contents list of documents into a spreadsheet?

DEVONthink Automation
1

I’m not really sure how to ask this question, so I’ll describe my specific scenario:

I have a DTP2 database of almost 7000 PDFs of musical compositions from the Renaissance to the early 20th century. There is no searchable text in these, but they’re in folders according to composer or genre (solo, duo, etc.) as originals and replicants. I can get a nice list of them and their metadata by looking in the smart folder “All PDF documents.”

I’d like to be able to take this list and put it into a spreadsheet, or database program, and add some additional fields (Era: Renaissance, Baroque, etc.; composer dates, and more) so I can better classify and get a sense of this large library.

I can’t figure out how to do this. I don’t want to copy the documents, only a list of them. I tried selecting and copying everything in the list-view window (if that’s what it’s called in DTP2), but wasn’t sure if DT was copying only the list, or trying to copy all the documents. In any case, Numbers crashed when I tried to paste the info into it, and TextEdit just hung.

I’d be happy if I can just get the document names and Spotlight Comments into another program to work with.

Has anyone done this? Any thoughts?

best,

Christopher

EDIT: I forgot to add that I know I can use the Scripts>Export>Listing script, but that only returns the enclosing folders and their contents, not the metadata.

2

This script will get you started. It is based on the Scripts > Export>Listing script. This script will export any selection of documents (say, in your smart group) to a .tab delimited file (import to Numbers, Excel, etc.) It exports record name and Spotlight comments. You may modify this to add whatever other fields you consider metadata for your purposes.

-- Create listing of the selected items instead of all items in a database
-- based on
-- Create Listing script in Export Folder of DTPO scripts
-- Created by Christian Grunenberg on Sun Jul 24 2005.
-- Copyright (c) 2005-2009. All rights reserved.

tell application id "com.devon-technologies.thinkpro2"
	try
		if not (exists current database) then error "No database is open."
		set theDatabase to the current database
		set theSelection to the selection
		if theSelection is {} then error "Please select some items."
		set theFile to choose file name default name ((name of theDatabase) as string) & ".tab"
		
		show progress indicator "Creating Listing..."
		
		--set theListing to my createListing(children of root of theDatabase, "")
		set theListing to my createListing(theSelection)
		set thePath to POSIX path of theFile
		if thePath does not end with ".tab" then set thePath to thePath & ".tab"
		set writeFile to open for access (thePath as POSIX file) with write permission
		set eof writeFile to 0
		
		write theListing to writeFile
		
		close access writeFile
		
		hide progress indicator
	on error error_message number error_number
		hide progress indicator
		if the error_number is not -128 then display alert "DEVONthink Pro" message error_message as warning
	end try
end tell

on createListing(theseRecords)
	tell application id "com.devon-technologies.thinkpro2"
		set tabChar to ASCII character 9
		set this_listing to "Name" & tabChar & "Comments" & return
		repeat with this_record in theseRecords
			set this_name to (name of this_record as string)
			set these_comments to (comment of this_record as string)
			set this_listing to this_listing & this_name & tabChar & these_comments & return
		end repeat
	end tell
	return this_listing
end createListing
3

korm,

This is fantastic! It works perfectly and is exactly what I was needing. I can’t thank you enough!

Christopher

4

I need to do this too, but I don’t know how to use scripts. Is there a DTP tutorial on this topic?

5

This worked great for me also…not sure how to add fields though; is there a tutorial on scripting, can anyone point me in the right direction?

6

DEVONthink has one of the most extensive scripting libraries available on the Mac platform.

Start with DEVONthink Help – the section on Scripting provides good starting information for learning about scripting DEVONthink as well as links and references to other places to learn about AppleScript in general.

As with any programming language, the best way to learn how to write or modify AppleScript is to read scripts written by others. Delve into the Scripting forum here – locate scripts that are advertised to do something you think is interesting and parse the code to see why that code does what the author says it does. There are lots of scripting styles – the way I write scripts is different than what Rob Trew (Houthakker) does, or Justin Lancey, Charles Turner, or any of the dozens of excellent scripters who have posted here in the past.

To get started, beside learning about AppleScript generally, you’ll want to delve into the DEVONthink dictionary that can be accessed through AppleScript Editor – a program included on your Mac. The Help section I mentioned above explains how to locate and use the dictionary.

Good luck. And, remember the first principal of scripting – share your work on the forum. We all benefit.