How to import a ".numbers" spreadsheet?

Question

Does Mathematica support importing a ".numbers" spreadsheet file (from the "Numbers" '09 (ver. 2.3 554) spreadsheet program in Apple's OS X iWork office suite)?

A ".numbers" file is actually a zip file,

 $ file foo.numbers 
 foo.numbers: Zip archive data, at least v2.0 to extract

and the contents of the spreadsheet is stored in "index.xml":

$ unzip -t foo.numbers
Archive:  foo.numbers
 testing: QuickLook/Thumbnail.jpg   OK
 testing: buildVersionHistory.plist   OK
 testing: index.xml                OK
No errors detected in compressed data of foo.numbers.

Although it would be technically possible for me to parse it myself, I imagine a tool already exists for that. Thoughts?

Thanks,

Justin

Edit: Here is an example .numbers spreadsheet.

Why don't you export to an easier to read format from Numbers and try to import that? It's likely much quicker and much less work. — Szabolcs, Nov 10 '13 at 20:03
Is this the old (iWork 09) Numbers, or the new child-friendly Numbers released last month? The file formats changed, I believe. — cormullion, Nov 10 '13 at 20:07
@Szabolcs: Exporting would be the quickest option. But if there's a more direct option, it'd be nice to know. — ConvexMartian, Nov 10 '13 at 20:19
$ImportFormats does not contain this format, nor is it mentioned in the docs, so I assume Mathematica doesn't support it. You must write your own parser to import it directly. — Szabolcs, Nov 10 '13 at 20:30
@Mike: Certainly, here is a .numbers spreadsheet with text, date, number, and formula cells: link — ConvexMartian, Nov 17 '13 at 16:44

cormullion · Accepted Answer · 2013-11-10T23:01:59.977

If you are familiar with AppleScript, you could try an approach like this:

(* from http://github.com/fmeinberg/AppleScript *)
AppleScript["RunFile", file_] := Run["osascript " <> file]
AppleScript["RunScript", script_] := 
 Block[{file = ToFileName[$TemporaryDirectory, "script.txt"]}, 
  Export[file, script, "String"];
  AppleScript["RunFile", file]]

numbersFile = "/Users/me/Desktop/numbers-test.numbers";

script = StringForm["set numbersFile to \"``\"
   tell application \"Numbers\"
   set results to {}
   open numbersFile
   tell first table of first sheet of front document
        repeat with r in every row
            tell r
                repeat with c in every cell
                    tell c
                        set end of results to value & tab
                    end tell
                end repeat
            end tell
            set end of results to return
        end repeat
    end tell
   end tell
   do shell script \"echo \" & results & \" >  /tmp/output.txt\"", 
   numbersFile];

AppleScript["RunScript", script]

Import["/tmp/output.txt"]

giving you some text results:

1.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 
2.0 1.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 
3.0 2.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 
3.0 3.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 
4.0 3.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 
54.0 4.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 
6.0 54.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 
7.0 6.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
....

There's plenty of scope for you to improve and embellish this, and also for you to start foaming at the mouth and shouting at the screen, AppleScript having the ability to drive people to the edge like that... :)

@ConvexMartian :) and good luck if you travel down this road... — cormullion, Nov 17 '13 at 18:02

Mike Honeychurch · Answer 2 · 2013-11-17T23:45:21.020

4

This is not a complete answer but has too much for a comment. I don't have Numbers so do not know what is in the file you linked but the following information can be obtained:

Import["PathTo/example.numbers", "ZIP"]
(*  {"QuickLook/Thumbnail.jpg", "buildVersionHistory.plist", "index.xml"}  *)

Now grab the XML:

Import["PathTo/example.numbers", {"ZIP","index.xml"}]

enter image description here

From here you need to find out more about the XML structure of numbers files. If you know where to look for your data then it should be relatively straight forward to uses Cases and pattern matching to get the data.

edited Nov 17 '13 at 23:45

answered Nov 17 '13 at 23:24

Mike Honeychurch

37,541
3
85
158

It is nice to see that Mathematica can import files from within a zip file! As for XML parsing, the XML seems fairly involved and can vary widely depending on the datatype in the cell. But this manual approach would work. Many thanks. – ConvexMartian Nov 18 '13 at 16:49

How to import a ".numbers" spreadsheet?

2 Answers2

Linked