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 |