The Omni Group Forums - Script: Replacement for flat (and broken ?) CSV export

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:[LIST=1][*]A file containing zero bytes, and[*]an instructive message in the system log.[/LIST][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).[/CODE]

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 [I]pblnLevelColumns[/I] property near the top of the script) :
[LIST=1][*]With [I]pblnLevelColumns : false[/I] it emulates Omni's own exporter, flattening all topic text to a single 'topic' column.[*]With [I]pblnLevelColumns : true[/I], 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.[/LIST]
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[/CODE]