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:
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) :
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).
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:
- A file containing zero bytes, and
- 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).
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) :
- With pblnLevelColumns : false it emulates Omni's own exporter, flattening all topic text to a single 'topic' column.
- 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 "."