The Omni Group
These forums are now read-only. Please visit our new forums to participate in discussion. A new account will be required to post in the new forums. For more info on the switch, see this post. Thank you!

Go Back   The Omni Group Forums > OmniOutliner > OmniOutliner 3 for Mac
FAQ Members List Calendar Search Today's Posts Mark Forums Read

 
Script: Replacement for flat (and broken ?) CSV export Thread Tools Search this Thread Display Modes
Last night I needed to export an OO3 outline to Excel.

The XML Excel output from OO3 doesn't seem to be legible to current versions of Excel. I tried CSV.

Perhaps because my outline was deeply indented (12 levels) or had several user columns (5), the CSV export failed.
I was offered:
  1. A file containing zero bytes, and
  2. an instructive message in the system log.
Code:
Oct 11 00:18:38 b2 OmniOutliner Professional[38536]: xsltApplyXSLTTemplate: A potential infinite template recursion was detected.
	You can adjust xsltMaxDepth (--maxdepth) in order to raise the maximum number of nested template calls and variables/params (currently set to 3000).
Here is a rough sketch of a replacement for the fragile (and flat) CSV export.
I notice from exporting toy data that when it's working, Omni's CSV exporter flattens the output to a single column. This draft script offers a choice between flat and indented CSV export (edit the value of the pblnLevelColumns property near the top of the script) :
  1. With pblnLevelColumns : false it emulates Omni's own exporter, flattening all topic text to a single 'topic' column.
  2. With pblnLevelColumns : true, however, it creates one output column for each level of nesting to preserve the indentation information of the source data. Any user-defined columns come after column LevelN where N is the deepest level of indentation.

It's a very rough sketch - a point of departure rather than a tool for real production - and I only needed text columns last night. (Notes are not exported in this draft).

(To preserve nesting information, both versions also export ID and ParentID fields, and a LevelIndex field).

Code:
-- Copyright (C) 2012 Robin Trew
--
-- Permission is hereby granted, free of charge, 
-- to any person obtaining a copy of this software 
-- and associated documentation files (the "Software"), 
-- to deal in the Software without restriction, 
-- including without limitation the rights to use, copy, 
-- modify, merge, publish, distribute, sublicense, 
-- and/or sell copies of the Software, and to permit persons 
-- to whom the Software is furnished to do so, 
-- subject to the following conditions:

-- *******
-- The above copyright notice and this permission notice 
-- shall be included in ALL copies 
-- or substantial portions of the Software.
-- *******

-- THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, 
-- EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES 
-- OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. 
-- IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, 
-- DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, 
-- TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE 
-- OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

property pTitle : "Save OO3 as CSV"
property pVer : "0.03"
property pAuthor : "Robin Trew"
property pSite : "Orginally published on http://forums.omnigroup.com"

-- TWO CSV EXPORT MODES
-- 1. IF pblnLevelColumns = false then all text goes to a TOPIC column
-- 2. IF pblnLevelColumns = true then a LevelN column is created for each level of indentation
--		(in mode 2, the user columns follow the level columns)
property pblnLevelColumns : true

property pExportColumnPerLevel : 1
property pExportParentIDs : 2
property pExportType : pExportParentIDs

property pQuote : "\""
property pSep : "\",\""

property pstrOutFolder : (path to desktop)

on run
	tell application id "OOut"
		-- EXIT IF THERE IS NO FRONT DOCUMENT
		set lstDocs to documents
		if length of lstDocs < 1 then return
		
		-- PROMPT THE USER FOR THE NAME AND FOLDER OF THE CSV FILE
		-- Prepare a default .csv name based on the name of the oo3 file
		set oDoc to item 1 of lstDocs
		set {dlm, my text item delimiters} to {my text item delimiters, "."}
		set lstParts to text items of ((name of oDoc) as string)
		if length of lstParts > 1 then
			set strDefault to ((items 1 thru -2 of lstParts) as string) & ".csv"
		else
			set strDefault to (lstParts as string) & ".csv"
		end if
		set my text item delimiters to dlm
		
		set strOutPath to (POSIX path of ¬
			(choose file name with prompt pTitle default name strDefault default location pstrOutFolder))
		
		
		-- START WITH A SET OF BASIC COLUMNS (id of parent and node)
		set lstCols to {"ParentUID", "UID", "LevelIndex"}
		
		if pblnLevelColumns then
			-- IF WE ARE CREATING LEVEL COLUMNS, HOW MANY DO WE NEED ?
			set lngDepth to my MaxDepth(oDoc)
			repeat with i from 1 to lngDepth
				set end of lstCols to "Level" & i
			end repeat
		else
			set end of lstCols to "Topic"
		end if
		
		-- AND USER COLUMNS
		tell oDoc
			set {idTopic, idNote} to {id of topic column, id of note column}
			set refColumns to a reference to (columns where its id is not idTopic and its id is not idNote)
			set {lstUserColID, lstUserColName} to {id, name} of refColumns
			set lstCols to lstCols & lstUserColName
		end tell
		
		-- BEGIN THE CSV WITH A HEADER
		set {dlm, my text item delimiters} to {my text item delimiters, pSep}
		set strCSV to pQuote & (lstCols as string) & pQuote & return
		set my text item delimiters to dlm
		
		-- AND ADD DATA FOR EACH ROW
		tell oDoc
			repeat with oRow in rows
				tell oRow
					set strTopic to my escape(its topic)
					-- Fill in the parent and id columns and level
					set lngLevel to (its level)
					set strCSV to strCSV & pQuote & (id of its parent) & pSep & id & pSep & lngLevel & pSep
					
					if pblnLevelColumns then
						-- Prepend topic with empty indentation columns
						repeat with iLevel from 1 to (lngLevel - 1)
							set strCSV to strCSV & pSep
						end repeat
						set strCSV to strCSV & my strTopic
						-- and then postpend the topic with the unused levels
						repeat with iLevel from lngLevel + 1 to lngDepth
							set strCSV to strCSV & pSep
						end repeat
					else
						set strCSV to strCSV & my strTopic
					end if
					
					-- add any data from the user columns
					repeat with i from 1 to length of lstUserColID
						set varValue to value of cell id (item i of lstUserColID)
						if varValue is missing value then set varValue to ""
						set strCSV to strCSV & pSep & varValue
					end repeat
				end tell
				set strCSV to strCSV & pQuote & return
			end repeat
			set the clipboard to strCSV
		end tell
	end tell
	WriteText2Path(strCSV, strOutPath)
end run

on Sedreplace(str, strFind, strReplace)
	do shell script "echo " & quoted form of str & " | sed -e 's/" & strFind & "/" & strReplace & "/g'"
end Sedreplace

-- USE THE EXCEL CONVENTION OF A DUPLICATED DOUBLE QUOTE WITHIN A FIELD
on escape(str)
	set {dlm, my text item delimiters} to {my text item delimiters, "\""}
	set lstPart to text items of str
	if length of lstPart > 1 then
		set my text item delimiters to "\"\""
		set strOut to lstPart as string
	else
		set strOut to str
	end if
	set my text item delimiters to dlm
	return strOut
end escape

-- THE DEEPEST LEVEL OF INDENTATION IN THE OO3 FILE
on MaxDepth(oDoc)
	tell application id "OOut"
		set lstLevels to level of rows of oDoc
		set lngMax to 0
		repeat with oLevel in lstLevels
			if oLevel > lngMax then set lngMax to oLevel
		end repeat
		return contents of lngMax
	end tell
end MaxDepth

on WriteText2Path(strText, strPosixPath)
	strPosixPath
	set f to (POSIX file strPosixPath)
	open for access f with write permission
	write strText as «class utf8» to f
	close access f
end WriteText2Path

Last edited by RobTrew; 2012-10-12 at 12:26 AM.. Reason: ver .03 fixes assumption that all file names contain "."
 
Reinstalling the OO3 Exports plugin, as advised by the Ninjas, has fixed the problem which lead to a system log error and an empty file.

The design problem (indentation / nesting information discarded) remains, so I will continue to use my quick Applescript fix to preserve the data as an outline rather than a flat tabular list.
 
I have been searching for ways to have better export and agree, the Excel plugin is pretty much unuseable.

I tried your script (Installed in OO Scripts folder) but received the following error:

-------------
AppleScript reported the following error:
Can’t make items 1 thru -2 of {"Changes"} into type string.

With the last "string" in this section highlighted:


-- PROMPT THE USER FOR THE NAME AND FOLDER OF THE CSV FILE
-- Prepare a default .csv name based on the name of the oo3 file
set oDoc to item 1 of lstDocs
set {dlm, my text item delimiters} to {my text item delimiters, "."}
set lstParts to text items of ((name of oDoc) as string)
set strDefault to ((items 1 thru -2 of lstParts) as string) & ".csv"
set my text item delimiters to dlm

This Applescript is way out of my league. ANy suggestions on getting it working?
 
Ver 0.03 above should fix that – thanks for pointing it out.

(The filename selection code was assuming that all file names contain a ".")
 
 


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Broken visio export gberks OmniGraffle General 0 2013-03-26 07:51 AM
MultiMarkdown export broken OlivierPS OmniOutliner 3 for Mac 4 2011-09-02 01:12 PM
clip-o-tron broken due to flat forwarding jbkendrick OmniFocus 1 for Mac 0 2009-12-06 05:29 PM
SVG export broken? gumba OmniGraffle General 0 2008-09-22 02:07 PM
Project export broken neurack OmniPlan General 1 2006-11-06 05:18 PM


All times are GMT -8. The time now is 09:40 AM.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2024, vBulletin Solutions, Inc.