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='|')
Line endings
Exporting uses \r\n line endings by default so, make sure to include
newline='' otherwise you will get a blank line between each row
when you open the file in Excel:
with open('output.csv', 'w', newline='') as f:
f.write(dataset.export('csv'))
If you do not do this, and you export the file on Windows, your CSV file will open in Excel with a blank line between each row.
dbf¶
Import/export using the dBASE format.
Binary Warning
The dbf format contains binary data, so make sure to write in binary
mode:
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.
New 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.
Binary Warning
Dataset.ods contains binary data, so make sure to write in binary mode:
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
XLS files are limited to a maximum of 65,000 rows. Use xlsx to avoid this limitation.
Binary Warning
The xls file format is binary, so make sure to write in binary mode:
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.
Changed in version 3.1.0: The skip_lines parameter for import_set() was added.
Note
When reading an xlsx file containing formulas in its cells, Tablib will
read the cell values, not the cell formulas.
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().
Binary Warning
The xlsx file format is binary, so make sure to write in binary mode:
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.