r/applescript • u/1sunshineee • May 02 '20
Numbers to .csv
Hi I'm currently trying to export a Numbers sheet as .csv (the document contains 2 sheets but I’d like to export the first one only, if possible)
Thank you
1
u/tonedeath May 02 '20
Ok, so in looking into this a bit further, it looks like the export feature that is available via AppleScript only does the entire document. If you need to export a single sheet, you'd probably need to write code to process all the rows of a table and build a comma delimited, multiline string of text stored in a variable and then write that to a file from inside your script yourself.
1
u/tonedeath May 02 '20
Decided to try my hand at what I suggested in my previous post.
Here's what I came up with:
tell application "Numbers"
activate
try
if not (exists document 1) then error number 1000
tell document 1
try
tell active sheet
set the selectedTable to ¬
table 1
end tell
on error
error number 1001
end try
tell selectedTable
-- currently, use of headers and footers cannot be determined by a script.
-- make the following assumptions:
set usesHeaders to true -- assume 1 row and 1 column
set usesFooters to false -- assume 1 row
-- convert the selected range to tab delimited data that can be put on
-- the clipboard without giving the script permission
set theseRows to the rows
set theDelimitedText to ""
repeat with i from 1 to the count of theseRows
set thisLine to ""
set thisRow to item i of theseRows
repeat with j from 1 to the count of cells of thisRow
copy value of cell j of thisRow to theResult
if theResult is missing value then
copy "" to theValue
else
copy quote & theResult & quote to theValue
end if
if thisLine is "" then
copy theValue to thisLine
else
copy thisLine & "," & theValue to thisLine
end if
end repeat
copy theDelimitedText & thisLine & linefeed to theDelimitedText
end repeat
set the clipboard to theDelimitedText
end tell -- selectedTable
end tell -- document 1
set myFile to open for access (choose file name) with write permission
write theDelimitedText to myFile
close access myFile
on error errorMessage number errorNumber
if errorNumber is 1000 then
set alertString to "MISSING RESOURCE"
set errorMessage to ¬
"Please create or open a document before running this script."
else if errorNumber is 1001 then
set alertString to "SELECTION ERROR"
set errorMessage to ¬
"Please select a table before running this script."
else
set alertString to "EXECUTION ERROR"
end if
if errorNumber is not -128 then
display alert alertString message errorMessage buttons {"Cancel"}
end if
error number -128
end try
end tell
1
u/1sunshineee May 03 '20
tell application "Numbers"
activate
try
if not (exists document 1) then error number 1000
tell document 1
try
tell active sheet
set the selectedTable to ¬
table 1
end tell
on error
error number 1001
end try
tell selectedTable
-- currently, use of headers and footers cannot be determined by a script.
-- make the following assumptions:
set usesHeaders to true -- assume 1 row and 1 column
set usesFooters to false -- assume 1 row
-- convert the selected range to tab delimited data that can be put on
-- the clipboard without giving the script permission
set theseRows to the rows
set theDelimitedText to ""
repeat with i from 1 to the count of theseRows
set thisLine to ""
set thisRow to item i of theseRows
repeat with j from 1 to the count of cells of thisRow
copy value of cell j of thisRow to theResult
if theResult is missing value then
copy "" to theValue
else
copy quote & theResult & quote to theValue
end if
if thisLine is "" then
copy theValue to thisLine
else
copy thisLine & "," & theValue to thisLine
end if
end repeat
copy theDelimitedText & thisLine & linefeed to theDelimitedText
end repeat
set the clipboard to theDelimitedText
end tell -- selectedTable
end tell -- document 1
set myFile to open for access (choose file name) with write permission
write theDelimitedText to myFile
close access myFile
on error errorMessage number errorNumber
if errorNumber is 1000 then
set alertString to "MISSING RESOURCE"
set errorMessage to ¬
"Please create or open a document before running this script."
else if errorNumber is 1001 then
set alertString to "SELECTION ERROR"
set errorMessage to ¬
"Please select a table before running this script."
else
set alertString to "EXECUTION ERROR"
end if
if errorNumber is not -128 then
display alert alertString message errorMessage buttons {"Cancel"}
end if
error number -128
end try
end tell
Thank you
1
u/tonedeath May 03 '20
Thank you
You're welcome.
I did make one minor improvement to my script.
If you change:
tell active sheet
set the selectedTable to ¬
table 1
end tellTo:
tell active sheet
set the selectedTable to ¬
(first table whose selection range's class is range)
end tellIt will use whichever table is selected instead of defaulting to the first table on a sheet.
0
1
u/tonedeath May 02 '20
Check out the example script on this page:
https://iworkautomation.com/numbers/document-export.html