Numbers-parser - Python module for parsing Apple Numbers .numbers files

Overview

numbers-parser

build:

numbers-parser is a Python module for parsing Apple Numbers .numbers files. It supports Numbers files generated by Numbers version 10.3, and all 11.x up to 11.2 (current as of November 2021).

It supports and is tested against Python versions from 3.6 onwards. It is not compatible with earlier versions of Python.

Currently supported features of Numbers files are:

  • Multiple sheets per document
  • Multiple tables per sheet
  • Text, numeric, date, currency, duration, percentage cell types

Formulas have very limited support and rely wholly on Numbers saving values in cells as part of the saved document, which is not always guaranteed. When a formula value is not present, the value *FORMULA* is returned. Any formula that results in a Numbers error returns a value *ERROR*.

Installation

python3 -m pip install numbers-parser

Usage

Reading documents:

from numbers_parser import Document
doc = Document("my-spreasdsheet.numbers")
sheets = doc.sheets()
tables = sheets[0].tables()
rows = tables[0].rows()

Referring to sheets and tables

Both sheets and names can be accessed from lists of these objects using an integer index (list syntax) and using the name of the sheet/table (dict syntax):

# list access method
sheet_1 = doc.sheets()[0]
print("Opened sheet", sheet_1.name)

# dict access method
table_1 = sheets["Table 1"]
print("Opened table", table_1.name)

Accessing data

Table objects have a rows method which contains a nested list with an entry for each row of the table. Each row is itself a list of the column values. Empty cells in Numbers are returned as None values.

data = sheets["Table 1"].rows()
print("Cell A1 contains", data[0][0])
print("Cell C2 contains", data[2][1])

Cell references

In addition to extracting all data at once, individual cells can be referred to as methods

doc = Document("my-spreasdsheet.numbers")
sheets = doc.sheets()
tables = sheets["Sheet 1"].tables()
table = tables["Table 1"]

# row, column syntax
print("Cell A1 contains", table.cell(0, 0))
# Excel/Numbers-style cell references
print("Cell C2 contains", table.cell("C2"))

Merged cells

When extracting data using data() merged cells are ignored since only text values are returned. The cell() method of Table objects returns a Cell type object which is typed by the type of cell in the Numbers table. MergeCell objects indicates cells removed in a merge.

doc = Document("my-spreasdsheet.numbers")
sheets = doc.sheets()
tables = sheets["Sheet 1"].tables()
table = tables["Table 1"]

cell = table.cell("A1")
print(cell.merge_range)
print(f"Cell A1 merge size is {cell.size[0]},{cell.size[1]})

Row and column iterators

Tables have iterators for row-wise and column-wise iteration with each iterator returning a list of the cells in that row or column

for row in table.iter_rows(min_row=2, max_row=7, values_only=True):
   sum += row
for col in table.iter_cole(min_row=2, max_row=7):
   sum += col.value

Pandas

Since the return value of data() is a list of lists, you should be able to pass it straight to pandas like this

import pandas as pd

doc = Document("simple.numbers")
sheets = doc.sheets()
tables = sheets[0].tables()
data = tables[0].rows(values_only=True)
df = pd.DataFrame(data, columns=["A", "B", "C"])

Numbers File Formats

Numbers uses a proprietary, compressed binary format to store its tables. This format is comprised of a zip file containing images, as well as Snappy-compressed Protobuf .iwa files containing metadata, text, and all other definitions used in the spreadsheet.

Protobuf updates

As numbers-parser includes private Protobuf definitions extracted from a copy of Numbers, new versions of Numbers will inevitably create .numbers files that cannot be read by numbers-parser. As new versions of Numbers are released, the following steps must be undertaken:

  • Run proto-dump on the new copy of Numbers to dump new Proto files.
    • proto-dump assumes version 2.5.0 of Google Protobuf which may need changes to build on more modern OSes. The version linked here is maintained by the author and tested on recent macOS for both arm64 and x86_64 architectures.
    • Any . characters in the Protobuf definitions must be changed to _ characters manually, or via the rename_proto_files.py script in the protos directory of this repo.
  • Connect to a running copy of Numbers with lldb (or any other debugger) and manually copy and reformat the results of po [TSPRegistry sharedRegistry] into mapping.py.
    • Versions of macOS >= 10.11 may protect Numbers from being attached to by a debugger - to attach, temporarily disable System IntegrityProtection to get this data.
    • The generate_mapping.py script in protos should help turn the output from this step into a recreation of mapping.py

Running make bootstrap will perform all of these steps and generate the Python protos files as well as mapping.py. The makefile assumes that proto-dump is in a repo parallel to this one, but the make variable PROTO_DUMP can be overridden to pass the path to a working version of proto-dump.

Credits

numbers-parser was built by Jon Connell but derived enormously from prior work by Peter Sobot. Both modules are derived from previous work by Sean Patrick O'Brien.

Decoding the data structures inside Numbers files was helped greatly by previous work by Steven Lott.

Formula tests were adapted from JavaScript tests used in fast-formula-parser.

License

All code in this repository is licensed under the MIT License

Comments
  • Duration formatting

    Duration formatting

    https://github.com/SheetJS/test_files/blob/master/duration_112/duration_112.numbers test file.

    There are a few places where the duration formatting details are stored. For modern sheets, they are in the format table (.TST.DataStore field 22)

    There are three types of duration format and 21 manually specified field sets (stored as start and end granularity) as well as an "automatic" option.

    opened by SheetJSDev 10
  • merge ranges

    merge ranges

    File under "undocumented black magic": merge-test.zip

    It's ok if you don't think this is valid -- Numbers 11.2 won't write this field, but it parses correctly all the way back to Numbers 3.5.

    opened by SheetJSDev 9
  • Numbers 11.2 Support

    Numbers 11.2 Support

    Unable to read a Numbers file without throwing an error on Numbers 11.2:

    >>> from numbers_parser import Document
    >>> doc = Document('numbers-test.numbers')
    Traceback (most recent call last):
      File "/usr/local/lib/python3.9/site-packages/numbers_parser/iwafile.py", line 148, in from_buffer
        klass = ID_NAME_MAP[message_info.type]
    KeyError: 6372
    
    During handling of the above exception, another exception occurred:
    
    Traceback (most recent call last):
      File "/usr/local/lib/python3.9/site-packages/numbers_parser/iwafile.py", line 25, in from_buffer
        chunk, data = IWACompressedChunk.from_buffer(data, filename)
      File "/usr/local/lib/python3.9/site-packages/numbers_parser/iwafile.py", line 84, in from_buffer
        archive, data = IWAArchiveSegment.from_buffer(data, filename)
      File "/usr/local/lib/python3.9/site-packages/numbers_parser/iwafile.py", line 150, in from_buffer
        raise NotImplementedError(
    NotImplementedError: Don't know how to parse Protobuf message type 6372
    
    The above exception was the direct cause of the following exception:
    
    Traceback (most recent call last):
      File "/usr/local/lib/python3.9/site-packages/numbers_parser/unpack.py", line 62, in _extract_iwa_archives
        iwaf = IWAFile.from_buffer(contents, filename)
      File "/usr/local/lib/python3.9/site-packages/numbers_parser/iwafile.py", line 31, in from_buffer
        raise ValueError("Failed to deserialize " + filename) from e
    ValueError: Failed to deserialize Index/CalculationEngine-860970.iwa
    
    During handling of the above exception, another exception occurred:
    
    Traceback (most recent call last):
      File "<stdin>", line 1, in <module>
      File "/usr/local/lib/python3.9/site-packages/numbers_parser/document.py", line 35, in __init__
        self._object_store = ObjectStore(filename)
      File "/usr/local/lib/python3.9/site-packages/numbers_parser/containers.py", line 54, in __init__
        read_numbers_file(path,
      File "/usr/local/lib/python3.9/site-packages/numbers_parser/unpack.py", line 35, in read_numbers_file
        _get_objects_from_zip_stream(zipf, handler, store_objects)
      File "/usr/local/lib/python3.9/site-packages/numbers_parser/unpack.py", line 51, in _get_objects_from_zip_stream
        _extract_iwa_archives(contents, filename, handler, store_objects)
      File "/usr/local/lib/python3.9/site-packages/numbers_parser/unpack.py", line 64, in _extract_iwa_archives
        raise FileFormatError(f"{filename}: invalid IWA file {filename}") from e
    NameError: name 'FileFormatError' is not defined
    >>>
    
    opened by tsouchlarakis 7
  • Unable to read image cell

    Unable to read image cell

    When I read a cell with an image, I got EmptyCell.

    >>> table.cell("B2")
    <numbers_parser.cell.EmptyCell object at 0x10e1b2b80>
    
    opened by yifeikong 6
  • performance against large files

    performance against large files

    test file https://github.com/SheetJS/nodeno/blob/master/large_strings.numbers

    From SheetJS, you can get a sense for expected performance by cloning the repo https://github.com/SheetJS/nodeno and either:

    A) setting up https://bun.sh/ and running time bun read.js large_strings.numbers

    B) setting up https://nodejs.org/en/ and running time node read.mjs large_strings.numbers

    It takes ~3 seconds on a 2018 i9 MBP either way.

    Using the PyPI version (2.3.12) with time cat-numbers -b large_strings.numbers on Python 3.9.13, it runs close to 57 seconds.

    Is there some library option or missing setup to improve performance?

    opened by SheetJSDev 6
  • IndexError: list index out of range

    IndexError: list index out of range

    Rename attachment to .numbers

    numbers_parser_issue.zip

      File "/usr/local/lib/python3.9/site-packages/numbers_parser/model.py", line 131, in owner_id_map
        ce_id = self.find_refs("CalculationEngineArchive")[0]
    IndexError: list index out of range
    
    opened by reviewher 6
  • Error installing package

    Error installing package

    I don't know what it means but got this error trying to install this package. Thanks for looking at it.

    ERROR: Command errored out with exit status 1: /Applications/Xcode.app/Contents/Developer/usr/bin/python3 -u -c 'import sys, setuptools, tokenize; sys.argv[0] = '"'"'/private/var/folders/pc/3r24mm_j7vj7pbsccb25x9fh0000gn/T/pip-install-6xw1j_pi/python-snappy/setup.py'"'"'; file='"'"'/private/var/folders/pc/3r24mm_j7vj7pbsccb25x9fh0000gn/T/pip-install-6xw1j_pi/python-snappy/setup.py'"'"';f=getattr(tokenize, '"'"'open'"'"', open)(file);code=f.read().replace('"'"'\r\n'"'"', '"'"'\n'"'"');f.close();exec(compile(code, file, '"'"'exec'"'"'))' install --record /private/var/folders/pc/3r24mm_j7vj7pbsccb25x9fh0000gn/T/pip-record-t9m3oii6/install-record.txt --single-version-externally-managed --user --prefix= --compile --install-headers /Users/paul/Library/Python/3.8/include/python3.8/python-snappy Check the logs for full command output.

    opened by walmer-bolfet 6
  • Request for numbers-parser information

    Request for numbers-parser information

    I wouldn’t say this is an issue, but a request for information.

    1. I have one.numbers file with multiple sheets and one table per sheet. Is it possible to use numbers-parser to replace a table (or sheet) in this one.numbers file with a table (or sheet) from another.numbers file? It is not intuitive to me how this might be done.

    2. Since numbers can open a .csv file, is it possible to use numbers-parser to open a .csv file and save it as a .numbers file?

    opened by walmer-bolfet 5
  • Bug with Numerical Zero as Cell Value None

    Bug with Numerical Zero as Cell Value None

    I have a basic numbers spreadsheet with a cell value of 0 (cell data format set to Automatic).

    2021-12-12 at 10 44 AM

    When run through numbers-parser I see...

    2021-12-12 10:42:35,792 DEBUG Table 1@[3,3]: value=0.0

    But the cell value from iter_rows is None.

    If I change the data format to Text, the result is a string "0" (which I expect).

    The None result seems like a bug in conditional logic.

    (p.s. Thank you so much for this library. I'm finally adding Numbers support to my app 🥳)

    opened by semireg 5
  • Cannot parse sheets if edited in iCloud.com

    Cannot parse sheets if edited in iCloud.com

    Any numbers file which is edited online on icloud.com cannot be parsed with doc.sheets(). I get an error:

    File "numbers_parser/model.py", line 49, in sheet_ids
        return [o.identifier for o in self.objects[1].sheets]
      File "numbers_parser/containers.py", line 40, in __getitem__
        return self._objects[key]
    
    KeyError: 1
    

    Probably the parser isn't build for numbers created or edited on icloud.com Numbers but could be an added feature.

    opened by nikostito-electobox 4
  • '\xa0'

    '\xa0'

    Hello again mate, When reading .numbers file I have to use some additional code to remove / replace '\xa0' from strings in pandas columns. This occurs all the time when there is a space (' ') OR ('-') and string look like this: 'python\xa0is\xa0cool'

    There is no need to add these when I use standard read from excel in pandas.

    Any solution to make it work from the start without these additional strings replacing?

    opened by meeffe 4
  • 3.9.1 dependency issues

    3.9.1 dependency issues

    When trying to install globally (sudo -H python3 -m pip install numbers_parser) there were a few missing deps:

    1. the package depends on poetry. Installation error:
    ...
      File "<frozen importlib._bootstrap>", line 241, in _call_with_frames_removed
      File "<frozen importlib._bootstrap>", line 1050, in _gcd_import
      File "<frozen importlib._bootstrap>", line 1027, in _find_and_load
      File "<frozen importlib._bootstrap>", line 1004, in _find_and_load_unlocked
    ModuleNotFoundError: No module named 'poetry'
    

    Looking at the current tarball (https://files.pythonhosted.org/packages/8e/d4/d32d8935fc3a3fbe777e988925e3bf3b60e0daa45f7b9d8a7fe689f4fb84/numbers_parser-3.9.1.tar.gz) the setup.py does not reference poetry dependency:

    1. the package depends on libmagic. Runtime error:
      File "/usr/local/lib/python3.10/site-packages/numbers_parser/model.py", line 23, in <module>
        from numbers_parser.cell import (
      File "/usr/local/lib/python3.10/site-packages/numbers_parser/cell.py", line 1, in <module>
        import magic
      File "/usr/local/lib/python3.10/site-packages/magic/__init__.py", line 209, in <module>
        libmagic = loader.load_lib()
      File "/usr/local/lib/python3.10/site-packages/magic/loader.py", line 49, in load_lib
        raise ImportError('failed to find libmagic.  Check your installation')
    ImportError: failed to find libmagic.  Check your installation
    

    This was resolved with brew install libmagic.

    opened by SheetJSDev 2
Releases(v3.0.0)
Owner
Jon Connell
Jon Connell
We provide useful util functions. When adding a util function, please add a description of the util function.

Utils Collection Motivation When we implement codes, we often search for util functions that are already implemented. Here, we are going to share util

6 Sep 09, 2021
Adding two matrix from scratch using python.

Adding-two-matrix-from-scratch-using-python. Here, I have take two matrix from user and add it without using any library. I made this program from scr

Sachin Vinayak Dabhade 4 Sep 24, 2021
Backup a folder to an another folder by using mirror update method.

Mirror Update Backup Backup a folder to an another folder by using mirror update method. How to use Install requirement pip install -r requirements.tx

1 Nov 21, 2022
Simple web index to use bloom filter for Pwned Passwords

pwbloom Simple web index to use bloom filter for Pwned Passwords The index.py runs a simple CGI web service checking passwords with a bloom filter for

Hanno Böck 4 Nov 23, 2021
cpp20.py is a Python script to compile C++20 code using modules.

cpp20.py is a Python script to compile C++20 code using modules. It browses the source files to determine their dependencies. Then, it compiles then in order using the correct flags.

Julien VERNAY 6 Aug 26, 2022
Runes - Simple Cookies You Can Extend (similar to Macaroons)

Runes - Simple Cookies You Can Extend (similar to Macaroons) is a paper called "Macaroons: Cookies with Context

Rusty Russell 22 Dec 11, 2022
Simple script to export contacts from telegram into vCard file

Telegram Contacts Exporter Simple script to export contacts from telegram into vCard file Getting Started Prerequisites You must to put your Telegram

Pere Antoni 1 Oct 17, 2021
Auto-generate /etc/hosts for HackTheBox machines

Auto-generate /etc/hosts for HackTheBox machines Save yourself some tedium on getting started on a new machine by having your /etc/hosts ready to go.

3 Feb 16, 2022
Prime Path Generator is a prime path generator used to generate prime paths.

Prime Path Generator is a prime path generator used to generate prime paths.

1 Nov 06, 2021
VerSign: Easy Signature Verification in Python

VerSign: Easy Signature Verification in Python versign is a small Python package which can be used to perform verification of offline signatures. It a

Muhammad Saif Ullah Khan 3 Dec 01, 2022
Patch the pclntable from Go binaries

Pretrain and Fine-tune a T5 model with Flax on GCP This tutorial details how pretrain and fine-tune a FlaxT5 model from HuggingFace using a TPU VM ava

6 Oct 05, 2022
Check the basic quality of any dataset

Data Quality Checker in Python Check the basic quality of any dataset. Sneak Peek Read full tutorial at Medium. Explore the app Requirements python 3.

MalaDeep 8 Feb 23, 2022
Entropy-controlled contexts in Python

Python module ordered ordered module is the opposite to random - it maintains order in the program. import random x = 5 def increase(): global x

HyperC 36 Nov 03, 2022
A Python package for floating-point binary fractions. Do math in base 2!

An implementation of a floating-point binary fractions class and module in Python. Work with binary fractions and binary floats with ease!

10 Oct 29, 2022
async parser for JET

This project is mainly aims to provide an async parsing option for NTDS.dit database file for obtaining user secrets.

15 Mar 08, 2022
Python script to launch burp scans automatically

SimpleAutoBurp Python script that takes a config.json file as config and uses Burp Suite Pro to scan a list of websites.

Adan Álvarez 26 Jul 18, 2022
Creating low-level foundations and abstractions for asynchronous programming in Python.

DIY Async I/O Creating low-level foundations and abstractions for asynchronous programming in Python (i.e., implementing concurrency without using thr

Doc Jones 4 Dec 11, 2021
Michael Vinyard's utilities

Install vintools To download this package from pypi: pip install vintools Install the development package To download and install the developmen

Michael Vinyard 2 May 22, 2022
A string to hashtags module

A string to hashtags module

Fayas Noushad 4 Dec 01, 2021
Analyze metadata of your Python project.

Analyze metadata of your Python projects Setup: Clone repo py-m venv venv (venv) pip install -r requirements.txt specify the folders which you want to

Pedro Monteiro de Carvalho e Silva Prado 1 Nov 10, 2021