Introduction

This part of the documentation covers all the interfaces of Tablib. Tablib is a format-agnostic tabular dataset library, written in Python. It allows you to Pythonically import, export, and manipulate tabular data sets. Advanced features include segregation, dynamic columns, tags/filtering, and seamless format import/export.

Philosophy

Tablib was developed with a few PEP 20 idioms in mind.

All contributions to Tablib should keep these important rules in mind.

Tablib License

Tablib is released under terms of The MIT License.

Copyright 2017 Kenneth Reitz

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

Now, go install Tablib.

Installation

This part of the documentation covers the installation of Tablib. The first step to using any software package is getting it properly installed.

Installing Tablib

Distribute & Pip

Of course, the recommended way to install Tablib is with pip:

$ pip install tablib

You can also choose to install more dependencies to have more import/export formats available:

$ pip install "tablib[xlsx]"

Or all possible formats:

$ pip install "tablib[all]"

which is equivalent to:

$ pip install "tablib[html, pandas, ods, xls, xlsx, yaml]"

Download the Source

You can also install Tablib from source. The latest release (3.9) is available from GitHub.

Once you have a copy of the source, you can embed it in your Python package, or install it into your site-packages easily.

$ python setup.py install

To download the full source history from Git, see Source Control.

Staying Updated

The latest version of Tablib will always be available here:

When a new version is available, upgrading is simple:

$ pip install tablib --upgrade

Now, go get a Quick Start.

Quickstart

Eager to get started? This page gives a good introduction in how to get started with Tablib. This assumes you already have Tablib installed. If you do not, head over to the Installation section.

First, make sure that:

Let's get started with some simple use cases and examples.

Creating a Dataset

A Dataset is nothing more than what its name implies—a set of data.

Creating your own instance of the tablib.Dataset object is simple.

data = tablib.Dataset()

You can now start filling this Dataset object with data.

Adding Rows

Let's say you want to collect a simple list of names.

# collection of names
names = ['Kenneth Reitz', 'Bessie Monke']
for name in names:


    # split name appropriately


    fname, lname = name.split()


    # add names to Dataset


    data.append([fname, lname])

You can get a nice, Pythonic view of the dataset at any time with Dataset.dict:

>>> data.dict
[('Kenneth', 'Reitz'), ('Bessie', 'Monke')]

Adding Headers

It's time to enhance our Dataset by giving our columns some titles. To do so, set Dataset.headers.

data.headers = ['First Name', 'Last Name']

Now our data looks a little different.

>>> data.dict
[{'Last Name': 'Reitz', 'First Name': 'Kenneth'},


 {'Last Name': 'Monke', 'First Name': 'Bessie'}]

Adding Columns

Now that we have a basic Dataset in place, let's add a column of ages to it.

data.append_col([22, 20], header='Age')

Let's view the data now.

>>> data.dict
[{'Last Name': 'Reitz', 'First Name': 'Kenneth', 'Age': 22},


 {'Last Name': 'Monke', 'First Name': 'Bessie', 'Age': 20}]

It's that easy.

Importing Data

Creating a tablib.Dataset object by importing a pre-existing file is simple.

with open('data.csv', 'r') as fh:


    imported_data = Dataset().load(fh)

This detects what sort of data is being passed in, and uses an appropriate formatter to do the import. So you can import from a variety of different file types.

Exporting Data

Tablib's killer feature is the ability to export your Dataset objects into a number of formats.

Comma-Separated Values

>>> data.export('csv')
Last Name,First Name,Age
Reitz,Kenneth,22
Monke,Bessie,20

JavaScript Object Notation

>>> data.export('json')
[{"Last Name": "Reitz", "First Name": "Kenneth", "Age": 22}, {"Last Name": "Monke", "First Name": "Bessie", "Age": 20}]

YAML Ain't Markup Language

>>> data.export('yaml')
- {Age: 22, First Name: Kenneth, Last Name: Reitz}
- {Age: 20, First Name: Bessie, Last Name: Monke}

Microsoft Excel

>>> data.export('xls')
<redacted binary data>

Pandas DataFrame

>>> data.export('df')


  First Name Last Name  Age
0    Kenneth     Reitz   22
1     Bessie     Monke   21

Selecting Rows & Columns

You can slice and dice your data, just like a standard Python list.

>>> data[0]
('Kenneth', 'Reitz', 22)
>>> data[0:2]
[('Kenneth', 'Reitz', 22), ('Bessie', 'Monke', 20)]

You can also access a row using its index without slicing.

>>> data.get(0)
('Kenneth', 'Reitz', 22)

If we had a set of data consisting of thousands of rows, it could be useful to get a list of values in a column. To do so, we access the Dataset as if it were a standard Python dictionary.

>>> data['First Name']
['Kenneth', 'Bessie']

You can also access the column using its index.

>>> data.headers
['Last Name', 'First Name', 'Age']
>>> data.get_col(1)
['Kenneth', 'Bessie']

Let's find the average age.

>>> ages = data['Age']
>>> float(sum(ages)) / len(ages)
21.0

Removing Rows & Columns

It's easier than you could imagine. Delete a column:

>>> del data['Col Name']

Delete a range of rows:

>>> del data[0:12]

Advanced Usage

This part of the documentation services to give you an idea that are otherwise hard to extract from the API Documentation.

And now for something completely different.

Dynamic Columns

Thanks to Josh Ourisman, Tablib now supports adding dynamic columns. A dynamic column is a single callable object (e.g. a function).

Let's add a dynamic column to our Dataset object. In this example, we have a function that generates a random grade for our students.

import random
def random_grade(row):


    """Returns a random integer for entry."""


    return (random.randint(60,100)/100.0)
data.append_col(random_grade, header='Grade')

Let's have a look at our data.

>>> data.export('yaml')
- {Age: 22, First Name: Kenneth, Grade: 0.6, Last Name: Reitz}
- {Age: 20, First Name: Bessie, Grade: 0.75, Last Name: Monke}

Let's remove that column.

>>> del data['Grade']

When you add a dynamic column, the first argument that is passed in to the given callable is the current data row. You can use this to perform calculations against your data row.

For example, we can use the data available in the row to guess the gender of a student.

def guess_gender(row):


    """Calculates gender of given student data row."""


    m_names = ('Kenneth', 'Mike', 'Yuri')


    f_names = ('Bessie', 'Samantha', 'Heather')


    name = row[0]


    if name in m_names:


        return 'Male'


    elif name in f_names:


        return 'Female'


    else:


        return 'Unknown'

Adding this function to our dataset as a dynamic column would result in:

>>> data.export('yaml')
- {Age: 22, First Name: Kenneth, Gender: Male, Last Name: Reitz}
- {Age: 20, First Name: Bessie, Gender: Female, Last Name: Monke}

When you add new rows to a dataset that contains dynamic columns, you should either provide all values in the row, or only the non-dynamic values and then the dynamic values will be automatically generated using the function initially provided for the column calculation.

..versionchanged:: 3.6.0

Filtering Datasets with Tags

When constructing a Dataset object, you can add tags to rows by specifying the tags parameter. This allows you to filter your Dataset later. This can be useful to separate rows of data based on arbitrary criteria (e.g. origin) that you don't want to include in your Dataset.

Let's tag some students.

students = tablib.Dataset()
students.headers = ['first', 'last']
students.rpush(['Kenneth', 'Reitz'], tags=['male', 'technical'])
students.rpush(['Daniel', 'Dupont'], tags=['male', 'creative' ])
students.rpush(['Bessie', 'Monke'], tags=['female', 'creative'])

Now that we have extra meta-data on our rows, we can easily filter our Dataset. Let's just see Female students.

>>> students.filter(['female']).yaml
- {first: Bessie, Last: Monke}

By default, when you pass a list of tags you get filter type or.

>>> students.filter(['female', 'creative']).yaml
- {first: Daniel, Last: Dupont}
- {first: Bessie, Last: Monke}

Using chaining you can get a filter type and.

>>> students.filter(['female']).filter(['creative']).yaml
- {first: Bessie, Last: Monke}

It's that simple. The original Dataset is untouched.

Open an Excel Workbook and read first sheet

Open an Excel 2007 and later workbook with a single sheet (or a workbook with multiple sheets but you just want the first sheet).

data = tablib.Dataset()
with open('my_excel_file.xlsx', 'rb') as fh:


    data.load(fh, 'xlsx')
print(data)

Excel Workbook With Multiple Sheets

When dealing with a large number of Datasets in spreadsheet format, it's quite common to group multiple spreadsheets into a single Excel file, known as a Workbook. Tablib makes it extremely easy to build workbooks with the handy Databook class.

Let's say we have 3 different Datasets. All we have to do is add them to a Databook object...

book = tablib.Databook((data1, data2, data3))

... and export to Excel just like Datasets.

with open('students.xls', 'wb') as f:


    f.write(book.export('xls'))

The resulting students.xls file will contain a separate spreadsheet for each Dataset object in the Databook.

Separators

When constructing a spreadsheet, it's often useful to create a blank row containing information on the upcoming data. So,

daniel_tests = [


    ('11/24/09', 'Math 101 Mid-term Exam', 56.),


    ('05/24/10', 'Math 101 Final Exam', 62.)
]
suzie_tests = [


    ('11/24/09', 'Math 101 Mid-term Exam', 56.),


    ('05/24/10', 'Math 101 Final Exam', 62.)
]
# Create new dataset
tests = tablib.Dataset()
tests.headers = ['Date', 'Test Name', 'Grade']
# Daniel's Tests
tests.append_separator('Daniel\'s Scores')
for test_row in daniel_tests:


   tests.append(test_row)
# Susie's Tests
tests.append_separator('Susie\'s Scores')
for test_row in suzie_tests:


   tests.append(test_row)
# Write spreadsheet to disk
with open('grades.xls', 'wb') as f:


    f.write(tests.export('xls'))

The resulting tests.xls will have the following layout:

----

Now, go check out the API Documentation or begin Tablib Development.

Formats

Tablib supports a wide variety of different tabular formats, both for input and output. Moreover, you can register your own formats.

cli

The cli format is currently export-only. The exports produce a representation table suited to a terminal.

When exporting to a CLI you can pass the table format with the tablefmt parameter, the supported formats are:

>>> import tabulate
>>> list(tabulate._table_formats)
['simple', 'plain', 'grid', 'fancy_grid', 'github', 'pipe', 'orgtbl',


 'jira', 'presto', 'psql', 'rst', 'mediawiki', 'moinmoin', 'youtrack',


 'html', 'latex', 'latex_raw', 'latex_booktabs', 'tsv', 'textile']

For example:

dataset.export("cli", tablefmt="github")
dataset.export("cli", tablefmt="grid")

This format is optional, install Tablib with pip install "tablib[cli]" to make the format available.

csv

When you import CSV data, you can specify if the first line of your data source is headers with the headers boolean parameter (defaults to True):

import tablib
tablib.import_set(your_data_stream, format='csv', headers=False)

It is also possible to provide the skip_lines parameter for the number of lines that should be skipped before starting to read data.

Changed in version 3.1.0: The skip_lines parameter was added.

When exporting with the csv format, the top row will contain headers, if they have been set. Otherwise, the top row will contain the first row of the dataset.

When importing a CSV data source or exporting a dataset as CSV, you can pass any parameter supported by the csv.reader() and csv.writer() functions. For example:

tablib.import_set(your_data_stream, format='csv', dialect='unix')
dataset.export('csv', delimiter=' ', quotechar='|')

with open('output.csv', 'w', newline='') as f:


    f.write(dataset.export('csv'))

dbf

Import/export using the dBASE format.

with open('output.dbf', 'wb') as f:


    f.write(dataset.export('dbf')

df (DataFrame)

Import/export using the pandas DataFrame format. This format is optional, install Tablib with pip install "tablib[pandas]" to make the format available.

html

The exports produce an HTML page with the data in a <table>. If headers have been set, they will be used as table headers (thead).

When you import HTML, you can specify a specific table to import by providing the table_id argument:

import tablib
tablib.import_set(your_html, format='html', table_id='some_table_id')

Otherwise, the first table found will be imported.

Changed in version 3.6.0: The ability to import HTML was added. The dependency on MarkupPy was dropped.

jira

The jira format is currently export-only. Exports format the dataset according to the Jira table syntax:

||heading 1||heading 2||heading 3||
|col A1|col A2|col A3|
|col B1|col B2|col B3|

json

Import/export using the JSON format. If headers have been set, a JSON list of objects will be returned. If no headers have been set, a JSON list of lists (rows) will be returned instead.

Import assumes (for now) that headers exist.

latex

Import/export using the LaTeX format. This format is export-only. If a title has been set, it will be exported as the table caption.

ods

Import/export data in OpenDocument Spreadsheet format.

Added in version 3.6.0: Import functionality was added.

This format is optional, install Tablib with pip install "tablib[ods]" to make the format available.

The import_set() method also supports a skip_lines parameter that you can set to a number of lines that should be skipped before starting to read data.

with open('output.ods', 'wb') as f:


    f.write(data.ods)

rst

Export data as a reStructuredText table representation of a dataset. The rst format is export-only.

Exporting returns a simple table if the text in the first column is never wrapped, otherwise returns a grid table:

>>> from tablib import Dataset
>>> bits = ((0, 0), (1, 0), (0, 1), (1, 1))
>>> data = Dataset()
>>> data.headers = ['A', 'B', 'A and B']
>>> for a, b in bits:
...     data.append([bool(a), bool(b), bool(a * b)])
>>> table = data.export('rst')
>>> table.split('\\n') == [
...     '=====  =====  =====',
...     '  A      B    A and',
...     '                B  ',
...     '=====  =====  =====',
...     'False  False  False',
...     'True   False  False',
...     'False  True   False',
...     'True   True   True ',
...     '=====  =====  =====',
... ]
True

tsv

A variant of the csv format with tabulators as fields separators.

xls

Import/export data in Legacy Excel Spreadsheet representation.

This format is optional, install Tablib with pip install "tablib[xls]" to make the format available.

Its import_set() method also supports a skip_lines parameter that you can set to a number of lines that should be skipped before starting to read data.

Changed in version 3.1.0: The skip_lines parameter for import_set() was added.

NOTE:

with open('output.xls', 'wb') as f:


    f.write(data.export('xls'))

xlsx

Import/export data in Excel 07+ Spreadsheet representation.

This format is optional, install Tablib with pip install "tablib[xlsx]" to make the format available.

The import_set() and import_book() methods accept keyword argument read_only. If its value is True (the default), the XLSX data source is read lazily. Lazy reading generally reduces time and memory consumption, especially for large spreadsheets. However, it relies on the XLSX data source declaring correct dimensions. Some programs generate XLSX files with incorrect dimensions. Such files may need to be loaded with this optimization turned off by passing read_only=False.

The import_set() method also supports a skip_lines parameter that you can set to a number of lines that should be skipped before starting to read data.

The export_set() method supports a column_width parameter. Depending on the value passed, the column width will be set accordingly. It can be either None, an integer, or default "adaptive". If "adaptive" is passed, the column width will be unique and will be calculated based on values' length. For example:

data = tablib.Dataset()
data.export('xlsx', column_width='adaptive')

This works with Databook as well:

data = tablib.Databook()
data.export('xlsx', column_width='adaptive')

The adaptive width will be calculated for each sheet in the databook.

Changed in version 3.8.0: The column_width parameter for export_set() was added.

Changed in version 3.1.0: The skip_lines parameter for import_set() was added.

NOTE:

Changed in version 2.0.0: Reads cell values instead of formulas.

You can export data to xlsx format by calling export('xlsx'). There are optional parameters to control the export. For available parameters, see tablib.formats._xlsx.XLSXFormat.export_set().

with open('output.xlsx', 'wb') as f:


    f.write(data.export('xlsx'))

yaml

Import/export data in the YAML format. When exporting, if headers have been set, a YAML list of objects will be returned. If no headers have been set, a YAML list of lists (rows) will be returned instead.

Import assumes (for now) that headers exist.

This format is optional, install Tablib with pip install "tablib[yaml]" to make the format available.

sql

Added in version 3.9.0.

The sql format is export-only. It produces SQL INSERT statements (one per row) assuming the target table already exists with the same columns. The table name can be passed as an argument or will be taken from the dataset's title (or defaults to export_table). Columns can be passed as an argument or will be taken from the dataset's headers. Values are rendered as ANSI SQL literals. Additionally the argument commit can be passed to add a COMMIT; statement at the end.

Example:

import datetime
from tablib import Dataset
data = Dataset(title='users')
data.headers = ['id', 'name', 'joined']
data.append([1, 'Alice', datetime.date(2021,1,1)])
print(data.export('sql'))
print(data.export('sql', table='\"User_Updates\"', columns=['id', 'username', 'update_date'], commit=True))

Output:

INSERT INTO users (id,name,joined) VALUES (1, 'Alice', DATE '2021-01-01');
INSERT INTO "User_Updates" (id,username,update_date) VALUES (1, 'Alice', DATE '2021-01-01');
COMMIT;

Development

Tablib is under active development, and contributors are welcome.

If you have a feature request, suggestion, or bug report, please open a new issue on GitHub. To submit patches, please send a pull request on GitHub.

Design Considerations

Tablib was developed with a few PEP 20 idioms in mind.

A few other things to keep in mind:

Source Control

Tablib source is controlled with Git, the lean, mean, distributed source control machine.

The repository is publicly accessible.

git clone git://github.com/jazzband/tablib.git

The project is hosted on GitHub.

Git Branch Structure

Feature / Hotfix / Release branches follow a Successful Git Branching Model . Git-flow is a great tool for managing the repository. I highly recommend it.

Each release is tagged.

When submitting patches, please place your feature/change in its own branch prior to opening a pull request on GitHub.

Adding New Formats

Tablib welcomes new format additions! Format suggestions include:

Coding by Convention

Tablib features a micro-framework for adding format support. The easiest way to understand it is to use it. So, let's define our own format, named xxx.

From version 1.0, Tablib formats are class-based and can be dynamically registered.

class MyXXXFormatClass:


    title = 'xxx'


    @classmethod


    def export_set(cls, dset):


        ....


        # returns string representation of given dataset


    @classmethod


    def export_book(cls, dbook):


        ....


        # returns string representation of given databook


    @classmethod


    def import_set(cls, dset, in_stream):


        ...


        # populates given Dataset with given datastream


    @classmethod


    def import_book(cls, dbook, in_stream):


        ...


        # returns Databook instance


    @classmethod


    def detect(cls, stream):


        ...


        # returns True if given stream is parsable as xxx

from tablib.formats import registry
registry.register('xxx', MyXXXFormatClass())

3. From then on, you should be able to use your new custom format as if it were a built-in Tablib format, e.g. using dataset.export('xxx') will use the MyXXXFormatClass.export_set method.

Testing Tablib

Testing is crucial to Tablib's stability. This stable project is used in production by many companies and developers, so it is important to be certain that every version released is fully operational. When developing a new feature for Tablib, be sure to write proper tests for it as well.

When developing a feature for Tablib, the easiest way to test your changes for potential issues is to simply run the test suite directly.

$ tox

Continuous Integration

Every pull request is automatically tested and inspected upon receipt with GitHub Actions. If you broke the build, you will receive an email accordingly.

Anyone may view the build status and history at any time.

Additional reports will also be included here in the future, including PEP 8 checks and stress reports for extremely large datasets.

Building the Docs

Documentation is written in the powerful, flexible, and standard Python documentation format, reStructured Text. Documentation builds are powered by the powerful Pocoo project, Sphinx. The API Documentation is mostly documented inline throughout the module.

The Docs live in tablib/docs. In order to build them, you will first need to install Sphinx.

$ pip install sphinx

Then, to build an HTML version of the docs, simply run the following from the docs directory:

$ make html

Your docs/_build/html directory will then contain an HTML representation of the documentation, ready for publication on most web servers.

You can also generate the documentation in epub, latex, json, &c similarly.

----

Make sure to check out the API Documentation.

API

This part of the documentation covers all the interfaces of Tablib. For parts where Tablib depends on external libraries, we document the most important right here and provide links to the canonical documentation.

Dataset Object

data = tablib.Dataset()
data.headers = ('name', 'age')
for (name, age) in some_collector():


    data.append((name, age))

data = tablib.Dataset()
data.headers = ('first_name', 'last_name')
data.append(('John', 'Adams'))
data.append(('George', 'Washington'))
data.append_col((90, 67), header='age')

headers = ('first_name', 'last_name')
data = [('John', 'Adams'), ('George', 'Washington')]
data = tablib.Dataset(*data, headers=headers)

data = tablib.Dataset()
data.dict = [{'age': 90, 'first_name': 'Kenneth', 'last_name': 'Reitz'}]

data.append_col(col=random.randint)

Databook Object

Functions

Exceptions

Now, go start some Tablib Development.