Updated Customizable Metadata Export Script (UTF16LE)

sander · April 1, 2015, 1:13pm

Updated the wonderful script by Christian Grunenberg to force UTF16LE encoding during export. This script will allow you to export metadata with special characters, and then import it properly into Excel for Mac (ie no strange characters in the name).

Tested with a small test set (OSX 10.7.5) with Excel for Mac 2011 v 14.4.8
Works with Cyrillic, French, Farsi, Hebrew, Vietnamese
Does not handle Zhongwen, Japanese, Korean

If you try it on different character sets, let me know and I will update this post.

Example:

In database:

Exported text file in preview:

After import into excel (step by step instructions below code):


-- Customizable Metadata Export (UTF16LE)

-- Based on Customizable Metadata Export 
-- Created by Christian Grunenberg on Fri Jun 06 2014.
-- Copyright (c) 2010-2014. All rights reserved.

-- Modifications by Sander Raaymakers on Tue Mar 31, 2015:
-- Added customizable Path & Location export (default true)
-- Added customizable Tag separator setting
-- Added UTF-16LE BOM (Byte Order Mark) to support Cyrillic export to excel
-- Changed default extension to .TXT to ensure user uses 'file > import'

property pSeparator : ";"

-- SR: tag separator, note: cannot be the same as pSeparator.
property pTagSeparator : ","

property pKind : true
property pDateCreated : true
property pURL : true
property pTags : true
property pComments : true
property pAnnotations : true
property pAuthor : true
property pRecipient : true
property pPath : true
property pLocation : true

tell application id "DNtp"
	try
		set theSelection to the selection
		if theSelection is {} then error "Please select some documents."
		set theFile to choose file name default name "Export.txt"
		
		show progress indicator "Exporting..."
		
		set theCSV to my prepareCSV("Name", pSeparator)
		if (pKind) then set theCSV to theCSV & my prepareCSV("Kind", pSeparator)
		if (pDateCreated) then set theCSV to theCSV & my prepareCSV("DateCreated", pSeparator)
		if (pAuthor) then set theCSV to theCSV & my prepareCSV("Author", pSeparator)
		if (pRecipient) then set theCSV to theCSV & my prepareCSV("Recipient", pSeparator)
		if (pURL) then set theCSV to theCSV & my prepareCSV("URL", pSeparator)
		if (pPath) then set theCSV to theCSV & my prepareCSV("Path", pSeparator)
		if (pLocation) then set theCSV to theCSV & my prepareCSV("Location", pSeparator)
		if (pTags) then set theCSV to theCSV & my prepareCSV("Tags", pSeparator)
		if (pComments) then set theCSV to theCSV & my prepareCSV("Comments", pSeparator)
		if (pAnnotations) then set theCSV to theCSV & my prepareCSV("Annotations", return)
		
		set theCSV to theCSV & my createCSV(theSelection)
		set thePath to POSIX path of theFile
		if thePath does not end with ".txt" then set thePath to thePath & ".txt"
		
		-- original: do shell script "echo " & quoted form of theCSV & ">" & quoted form of thePath 
		
		-- SR: create UTF-16LE BOM (Byte Order Mark) for excel; common text editors can handle this too.
		do shell script "xxd -p -r <<< xfffe > " & quoted form of (thePath & ".UTF16LE.txt")
		-- SR: create the CSV file
		do shell script "echo " & quoted form of theCSV & ">" & quoted form of thePath
		-- SR: convert the CSV file encoding to UTF-16LE with iconv, append after BOM
		do shell script "iconv -t UTF-16LE " & quoted form of thePath & " >> " & quoted form of (thePath & ".UTF16LE.txt")
		-- SR: clean up, move temp file to user filename. 
		-- Note: comment out the next line if you want to keep both.
		do shell script "mv -f " & quoted form of (thePath & ".UTF16LE.txt") & " " & quoted form of thePath
		
		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 createCSV(theseRecords)
	local this_record, this_csv, this_name, this_URL, this_PATH, this_LOCATION, this_metadata, this_author, this_recipient, this_annotation, theseTags
	tell application id "DNtp"
		set this_csv to ""
		repeat with this_record in theseRecords
			set this_name to name of this_record as string
			set this_csv to this_csv & my prepareCSV(this_name, pSeparator)
			if (pKind) then set this_csv to this_csv & my prepareCSV(kind of this_record as string, pSeparator)
			
			if (pDateCreated) then set this_csv to this_csv & my prepareCSV(creation date of this_record as string, pSeparator)
			if (pAuthor or pRecipient) then
				set this_metadata to meta data of this_record
				if (pAuthor) then
					try
						set this_author to |kMDItemAuthors| of this_metadata
					on error
						try
							set this_author to |kMDItemAuthorEmailAddresses| of this_metadata
						on error
							set this_author to ""
						end try
					end try
					if exists this_author then
						set this_csv to this_csv & my prepareCSV(this_author, pSeparator)
					else
						set this_csv to this_csv & my prepareCSV("", pSeparator)
					end if
				end if
				
				if (pRecipient) then
					try
						set this_recipient to |kMDItemRecipients| of this_metadata
					on error
						try
							set this_recipient to |kMDItemRecipientEmailAddresses| of this_metadata
						on error
							set this_recipient to ""
						end try
					end try
					if exists this_recipient then
						set this_csv to this_csv & my prepareCSV(this_recipient, pSeparator)
					else
						set this_csv to this_csv & my prepareCSV("", pSeparator)
					end if
				end if
			end if
			
			set this_URL to URL of this_record as string
			if this_URL begins with "x-devonthink-item://" then
				if (pURL) then set this_csv to this_csv & my prepareCSV("", pSeparator)
				set this_URL to (characters 21 thru -1 of this_URL) as string
			else
				if (pURL) then set this_csv to this_csv & my prepareCSV(this_URL, pSeparator)
				set this_URL to ""
			end if
			
			-- SR new field
			if (pPath) then
				set this_PATH to path of this_record as string
				set this_csv to this_csv & my prepareCSV(this_PATH, pSeparator)
			else
				set this_PATH to ""
			end if
			
			-- SR new field
			if (pLocation) then
				set this_LOCATION to location of this_record as string
				set this_csv to this_csv & my prepareCSV(this_LOCATION, pSeparator)
			else
				set this_LOCATION to ""
			end if
			
			-- SR modified, original split tags into multiple lines in exported file, added TagSeparator variable, defaulted to comma
			if (pTags) then
				-- original: set {od, text item delimiters of AppleScript} to {text item delimiters of AppleScript, return}
				set {od, text item delimiters of AppleScript} to {text item delimiters of AppleScript, pTagSeparator}
				set theseTags to (tags of this_record) as string
				set text item delimiters of AppleScript to od
				set this_csv to this_csv & my prepareCSV(theseTags, pSeparator)
			end if
			
			if (pComments) then set this_csv to this_csv & my prepareCSV(comment of this_record as string, pSeparator)
			
			if (pAnnotations) then
				if this_URL is not "" then
					set this_annotation to plain text of (get record with uuid this_URL)
				else
					set this_annotation to ""
				end if
				if exists this_annotation then
					set this_csv to this_csv & my prepareCSV(this_annotation, return)
				else
					set this_csv to this_csv & my prepareCSV("", return)
				end if
			end if
			
			if type of this_record is group or type of this_record is feed then
				step progress indicator this_name
				set this_csv to this_csv & my createCSV(children of this_record)
			end if
		end repeat
	end tell
	return this_csv
end createCSV

on prepareCSV(theString, theSeparator)
	try
		if theString contains "\"" then
			local od
			set {od, text item delimiters of AppleScript} to {text item delimiters of AppleScript, "\""}
			set theString to text items of theString
			set text item delimiters of AppleScript to "\"\""
			set theString to "" & theString
			set text item delimiters of AppleScript to od
		end if
	on error
		set theString to ""
	end try
	return "\"" & theString & "\"" & theSeparator
end prepareCSV

import into excel step by step

open excel
create new worksheet
file > import
select “text file” option
hit “import” button
select exported file (e.g. export.txt)
hit “get data” button
select “delimited”
hit “next”
in delimiters uncheck tab, check semi-colon
hit “next”
(optional: scroll to tags field, set as text)
hit “finish”
select cell to place imported text
Note: save in excel format to retain proper encoding.

sander · April 1, 2015, 6:35pm

NEW, EXPERIMENTAL version of script, not tested exhaustively, but works on same dataset used above.

– Modifications by Sander on Wed Apr 1, 2015:
– Added customizable pDecodeURL (default true) to enforce URL decoding
– Added URL decoding sub-routine
– Added customizable pEncodeUTF16LE (default true) to enforce UTF16-LE encoding when saving

Effect on file:

Effect on import (note difference for URL field vs image above):


-- Customizable Metadata Export (UTF16LE+URLDecoding)

-- Based on Customizable Metadata Export 
-- Created by Christian Grunenberg on Fri Jun 06 2014.
-- Copyright (c) 2010-2014. All rights reserved.

-- Modifications by Sander Raaymakers on Tue Mar 31, 2015:
-- Added customizable pPath & pLocation property export (default true)
-- Added customizable pTagSeparator property
-- Added UTF-16LE BOM (Byte Order Mark) to file to support Cyrillic export to excel
-- Changed default extension to .TXT to ensure user uses 'file > import'

-- Modifications by Sander Raaymakers on Wed Apr 1, 2015:
-- Added customizable pDecodeURL (default true) to enforce URL decoding
-- Added URL decoding sub-routine
-- Added customizable pEncodeUTF16LE (default true) to enforce UTF16-LE encoding when saving

property pSeparator : ";"

-- SR: tag separator, note: cannot be the same as pSeparator.
property pTagSeparator : ","

-- SR: set columns to false if no export is required
property pKind : true
property pDateCreated : true
property pURL : true
property pTags : true
property pComments : true
property pAnnotations : true
property pAuthor : true
property pRecipient : true
property pPath : true
property pLocation : true

-- SR: set properties to false to prevent URL decoding or UTF16LE encoded file export
property pDecodeURL : true
property pEncodeUTF16LE : true

tell application id "DNtp"
	try
		set theSelection to the selection
		if theSelection is {} then error "Please select some documents."
		set theFile to choose file name default name "Export.txt"
		
		show progress indicator "Exporting..."
		
		set theCSV to my prepareCSV("Name", pSeparator)
		if (pKind) then set theCSV to theCSV & my prepareCSV("Kind", pSeparator)
		if (pDateCreated) then set theCSV to theCSV & my prepareCSV("DateCreated", pSeparator)
		if (pAuthor) then set theCSV to theCSV & my prepareCSV("Author", pSeparator)
		if (pRecipient) then set theCSV to theCSV & my prepareCSV("Recipient", pSeparator)
		if (pURL) then set theCSV to theCSV & my prepareCSV("URL", pSeparator)
		if (pPath) then set theCSV to theCSV & my prepareCSV("Path", pSeparator)
		if (pLocation) then set theCSV to theCSV & my prepareCSV("Location", pSeparator)
		if (pTags) then set theCSV to theCSV & my prepareCSV("Tags", pSeparator)
		if (pComments) then set theCSV to theCSV & my prepareCSV("Comments", pSeparator)
		if (pAnnotations) then set theCSV to theCSV & my prepareCSV("Annotations", return)
		
		set theCSV to theCSV & my createCSV(theSelection)
		set thePath to POSIX path of theFile
		if thePath does not end with ".txt" then set thePath to thePath & ".txt"
		
		if (pEncodeUTF16LE) then
			-- SR: create UTF-16LE BOM (Byte Order Mark) for excel; common text editors can handle this too.
			do shell script "xxd -p -r <<< xfffe > " & quoted form of (thePath & ".UTF16LE.txt")
			-- SR: create the CSV file
			do shell script "echo " & quoted form of theCSV & ">" & quoted form of thePath
			-- SR: convert the CSV file encoding to UTF-16LE with iconv, append after BOM
			do shell script "iconv -t UTF-16LE " & quoted form of thePath & " >> " & quoted form of (thePath & ".UTF16LE.txt")
			-- SR: clean up, move temp file to user filename. 
			-- Note: comment out the next line if you want to keep both.
			do shell script "mv -f " & quoted form of (thePath & ".UTF16LE.txt") & " " & quoted form of thePath
		else
			-- no encoding
			do shell script "echo " & quoted form of theCSV & ">" & quoted form of thePath
		end if
		
		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 createCSV(theseRecords)
	local this_record, this_csv, this_name, this_URL, this_PATH, this_LOCATION, this_metadata, this_author, this_recipient, this_annotation, theseTags
	tell application id "DNtp"
		set this_csv to ""
		repeat with this_record in theseRecords
			set this_name to name of this_record as string
			set this_csv to this_csv & my prepareCSV(this_name, pSeparator)
			if (pKind) then set this_csv to this_csv & my prepareCSV(kind of this_record as string, pSeparator)
			
			if (pDateCreated) then set this_csv to this_csv & my prepareCSV(creation date of this_record as string, pSeparator)
			if (pAuthor or pRecipient) then
				set this_metadata to meta data of this_record
				if (pAuthor) then
					try
						set this_author to |kMDItemAuthors| of this_metadata
					on error
						try
							set this_author to |kMDItemAuthorEmailAddresses| of this_metadata
						on error
							set this_author to ""
						end try
					end try
					if exists this_author then
						set this_csv to this_csv & my prepareCSV(this_author, pSeparator)
					else
						set this_csv to this_csv & my prepareCSV("", pSeparator)
					end if
				end if
				
				if (pRecipient) then
					try
						set this_recipient to |kMDItemRecipients| of this_metadata
					on error
						try
							set this_recipient to |kMDItemRecipientEmailAddresses| of this_metadata
						on error
							set this_recipient to ""
						end try
					end try
					if exists this_recipient then
						set this_csv to this_csv & my prepareCSV(this_recipient, pSeparator)
					else
						set this_csv to this_csv & my prepareCSV("", pSeparator)
					end if
				end if
			end if
			
			set this_URL to URL of this_record as string
			
			-- SR: decode URL
			if (pDecodeURL) then set this_URL to my urlDecode(this_URL)
			
			if this_URL begins with "x-devonthink-item://" then
				if (pURL) then set this_csv to this_csv & my prepareCSV("", pSeparator)
				set this_URL to (characters 21 thru -1 of this_URL) as string
			else
				if (pURL) then set this_csv to this_csv & my prepareCSV(this_URL, pSeparator)
				set this_URL to ""
			end if
			
			-- SR: new field Path
			if (pPath) then
				set this_PATH to path of this_record as string
				set this_csv to this_csv & my prepareCSV(this_PATH, pSeparator)
			else
				set this_PATH to ""
			end if
			
			-- SR: new field Location
			if (pLocation) then
				set this_LOCATION to location of this_record as string
				set this_csv to this_csv & my prepareCSV(this_LOCATION, pSeparator)
			else
				set this_LOCATION to ""
			end if
			
			-- SR modified: Tags
			-- note: original split tags into multiple lines in exported file, added TagSeparator variable, defaulted to comma
			if (pTags) then
				-- original: set {od, text item delimiters of AppleScript} to {text item delimiters of AppleScript, return}
				set {od, text item delimiters of AppleScript} to {text item delimiters of AppleScript, pTagSeparator}
				set theseTags to (tags of this_record) as string
				set text item delimiters of AppleScript to od
				set this_csv to this_csv & my prepareCSV(theseTags, pSeparator)
			end if
			
			if (pComments) then set this_csv to this_csv & my prepareCSV(comment of this_record as string, pSeparator)
			
			if (pAnnotations) then
				if this_URL is not "" then
					set this_annotation to plain text of (get record with uuid this_URL)
				else
					set this_annotation to ""
				end if
				if exists this_annotation then
					set this_csv to this_csv & my prepareCSV(this_annotation, return)
				else
					set this_csv to this_csv & my prepareCSV("", return)
				end if
			end if
			
			if type of this_record is group or type of this_record is feed then
				step progress indicator this_name
				set this_csv to this_csv & my createCSV(children of this_record)
			end if
		end repeat
	end tell
	return this_csv
end createCSV

on prepareCSV(theString, theSeparator)
	try
		if theString contains "\"" then
			local od
			set {od, text item delimiters of AppleScript} to {text item delimiters of AppleScript, "\""}
			set theString to text items of theString
			set text item delimiters of AppleScript to "\"\""
			set theString to "" & theString
			set text item delimiters of AppleScript to od
		end if
	on error
		set theString to ""
	end try
	return "\"" & theString & "\"" & theSeparator
end prepareCSV

-- this sub-routine is used to decode URL strings 
-- source: http://applescript.bratis-lover.net/library/url/
on urlDecode(str)
	local str
	try
		return (do shell script "/bin/echo " & quoted form of str & ¬
			" | perl -MURI::Escape -lne 'print uri_unescape($_)'")
	on error eMsg number eNum
		error "Can't urlDecode: " & eMsg number eNum
	end try
end urlDecode

sander · April 1, 2015, 6:41pm

To clarify what the last script does vs the original script see results after import (attached graphic):