sohail.io

  • About

AppleScript Export to CSV via iWork Numbers

October 24, 2015

Many of us have to consume reference data in our apps provided by clients, business analysts or other team members. We’re unlikely to have them give us a JSON file or an XML property list.

For structured data, I’ve found that setting up a spreadsheet for clients that I can later parse as a CSV file, works great.

Of course, having to open Microsoft Excel or Apple’s iWork Numbers program manually and go through all of the menu commands to perform an export can become tedious when you have a lot of files to process and/or there’s a high frequency with which you receive updates.

As developers, we know the solution to such tedium is to write code to automate these activities.

Fortunately, Apple has AppleScript support in Numbers that lets us get this done.

I’ve created a public Gist with such an AppleScript program that you’ll find helpful to export Excel or Numbers spreadsheets, into CSV documents.

The script is called SpreadsheetExportToCSV.scpt. You can invoke it from the command line as follows:

    osascript SpreadsheetExportToCSV.scpt <input file> <output file>

Here’s a more concrete example where my source file (assumed to have one worksheet in it) is specified, along with my output CSV file:

    osascript SpreadsheetExportToCSV.scpt "/Users/me/Documents/MySpreadsheet.xlsx" "/Users/me/Documents/Converted/OutputFile.csv"

I find it safer to always places quotation marks around the input and output file paths, so that any spaces or other special characters don’t need to be escaped.

Source

Here’s the script as currently posted in the GitHub Gist:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
 
#! /usr/bin/osascript
 
(*
---------------------------------------------------------------------------------
 
Script: SpreadsheetExportToCSV
 
Command-line tool to convert a spreadsheet document to CSV
This AppleScript is tested with and compatible with Apple iWork Numbers 3.6,
current as at October 23, 2015.
 
Parameters:
1. Full Path to the input file, including file extension
2. Full Path to the output file, including file extension
 
Example command-line invocation:
 
    osascript SpreadsheetExportToCSV.scpt "/Users/me/Documents/MySpreadsheet.xlsx" "/Users/me/Documents/Converted/OutputFile.csv"
 
The spreadsheet to use as an input file can be an Excel file or a Numbers file.
 
Sohail A.
Blog: http://sohail.io
Twitter: @idStar
 
Creation Date: October 23, 2015
---------------------------------------------------------------------------------
*)
 
global _inputFilePathAlias
global _outputFilePath
global _requestedOptions
 
 
(*
run
 
This is our entry point, our main function, where this script
begins execution. We call out to helper functions, to modularize
the design.
*)
on run argv
-- Ensure our CSV files are encoding with UTF8:
ensureUTF8Encoding()
-- Parse and determine input/output paths:
retrieveCommandLineArguments(argv)
-- Perform the actual activation, file open, export and cleanup:
processSpreadsheet()
end run
 
 
 
 
---------------------- SUPPORTING FUNCTIONS --------------------------
 
(*
retrieveCommandLineArguments
 
Handles parsing the command line arguments passed to us.
We return a list, where the first element is the input file
path as an alias. The second element is the output path,
as text (as it may not yet exist).
*)
on retrieveCommandLineArguments(command_line_arguments)
set _inputFilePathAlias to POSIX file (item 1 of command_line_arguments) as alias
set _outputFilePath to (POSIX file (item 2 of command_line_arguments)) as text
log "input file path is: " & _inputFilePathAlias
log "output file path is: " & _outputFilePath
end retrieveCommandLineArguments
 
 
(*
processSpreadsheet
 
This function is the workhorse of this script. We open Numbers,
have it load the source spreadsheet, and invoke the export command
to ultimately write the output CSV to the specified path.
*)
on processSpreadsheet()
tell application "Numbers"
activate
-- Before we open the file asked of us, close out every document
-- that might have opened along with the application having activated:
close every window saving no
-- Retrieve information about the source file:
set fileInfo to (info for (_inputFilePathAlias))
set fileName to name of (fileInfo)
set fileExtension to name extension of (fileInfo)
log "Opening source document " & fileName & "..."
tell (open _inputFilePathAlias)
-- In this scope, we are now implicitly dealing with the document just opened
-- as the current target, which means we access it through the "it" keyword,
-- as per: https://developer.apple.com/library/mac/documentation/AppleScript/Conceptual/AppleScriptLangGuide/conceptual/ASLR_fundamentals.html#//apple_ref/doc/uid/TP40000983-CH218-SW4
set activeDocument to it
-- Note: We could have also gotten to the active document by walking the chain from the top,
-- i.e. right from the Application object:
--set activeDocument to document 1 of application "Numbers"
say "Starting Export."
with timeout of 600 seconds
export activeDocument as CSV to file _outputFilePath
-- Use this instead if you want to export to Excel:
-- export activeDocument as Microsoft Excel to file _outputFilePath
end timeout
say "Completed Export."
-- Since we closed out other windows that might have been open before
-- opening the file we sought, we really should only have one document
-- window open.
close activeDocument
end tell
quit
end tell
end processSpreadsheet
 
 
(*
ensureUTF8Encoding
 
Microsoft Excel on the Mac is not good with exporting special
characters as is Apple Numbers. Part of this is in the ability for
Numbers to correctly process UTF8 formatting when exporting.
 
Setup default export encoding for CSV files to UTF8, so without
specifying anything further for AppleScript, the right format will
be applied automatically. Since we cannot specify the CSV export
encoding via AppleScript, we will set it via the Defaults Database
with a shell command.
Here are the codes that apply: 4=UTF8, 12=windows latin, 30=MacRoman
As such, we'll specify 4 for UTF8.
 
This technique courtesy of: https://discussions.apple.com/thread/4018778?tstart=0
*)
on ensureUTF8Encoding()
do shell script "/usr/bin/defaults write com.apple.iWork.Numbers CSVExportEncoding -int 4"
end ensureUTF8Encoding
 

Filed Under: General Tagged With: AppleScript, csv, Excel, gist, iWork, Numbers

search

Categories

  • General
  • iOS

Recent Posts

  • AppleScript Export to CSV via iWork Numbers
  • Verifying the Status of an Auto-Renewable Subscription
  • Swift Memory Management Exercise
  • Learning Swift: My Approach
  • UIAutomation Command Line Test Runner for Xcode 6

This Blog

My name is Sohail. I'm a developer-consultant and entrepreneur primarily focused on building high quality iOS apps.

I write about software development. Mostly iOS related and sometimes, Ruby on Rails.

See the About page for more bio.

Sohail Ahmed - About page for blog author bio

Category Specific RSS

  • General (2)
  • iOS (6)

Archives

  • October 2015 (2)
  • June 2015 (2)
  • September 2014 (1)
  • October 2013 (3)

Copyright © 2021 Sohail A. · Log in

All opinions expressed are strictly those of the author, and do not necessarily reflect those of guests, partners, sponsors, customers or affiliates.