gist

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:


#! /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

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

AppleScript Export to CSV via iWork Numbers

Source

Archives