pyexcel.save_as

pyexcel.save_as(**keywords)[source]

Save a sheet from a data source to another one

It accepts two sets of keywords. Why two sets? one set is source, the other set is destination. In order to distinguish the two sets, source set will be exactly the same as the ones for pyexcel.get_sheet(); destination set are exactly the same as the ones for pyexcel.Sheet.save_as but require a ‘dest’ prefix.

Saving to source parameters
file dest_file_name, dest_sheet_name,dest_force_file_type keywords with prefix ‘dest’
memory dest_file_type, dest_content, dest_sheet_name, keywords with prefix ‘dest’
sql dest_session, dest_table, dest_initializer, dest_mapdict
django model dest_model, dest_initializer, dest_mapdict, dest_batch_size

Examples on start_row, start_column

Let’s assume the following file is a huge csv file:

>>> import datetime
>>> import pyexcel as pe
>>> data = [
...     [1, 21, 31],
...     [2, 22, 32],
...     [3, 23, 33],
...     [4, 24, 34],
...     [5, 25, 35],
...     [6, 26, 36]
... ]
>>> pe.save_as(array=data, dest_file_name="your_file.csv")

And let’s pretend to read partial data:

>>> pe.get_sheet(file_name="your_file.csv", start_row=2, row_limit=3)
your_file.csv:
+---+----+----+
| 3 | 23 | 33 |
+---+----+----+
| 4 | 24 | 34 |
+---+----+----+
| 5 | 25 | 35 |
+---+----+----+

And you could as well do the same for columns:

>>> pe.get_sheet(file_name="your_file.csv", start_column=1, column_limit=2)
your_file.csv:
+----+----+
| 21 | 31 |
+----+----+
| 22 | 32 |
+----+----+
| 23 | 33 |
+----+----+
| 24 | 34 |
+----+----+
| 25 | 35 |
+----+----+
| 26 | 36 |
+----+----+

Obvious, you could do both at the same time:

>>> pe.get_sheet(file_name="your_file.csv",
...     start_row=2, row_limit=3,
...     start_column=1, column_limit=2)
your_file.csv:
+----+----+
| 23 | 33 |
+----+----+
| 24 | 34 |
+----+----+
| 25 | 35 |
+----+----+

The pagination support is available across all pyexcel plugins.

Note

No column pagination support for query sets as data source.

Formatting while transcoding a big data file

If you are transcoding a big data set, conventional formatting method would not help unless a on-demand free RAM is available. However, there is a way to minimize the memory footprint of pyexcel while the formatting is performed.

Let’s continue from previous example. Suppose we want to transcode “your_file.csv” to “your_file.xls” but increase each element by 1.

What we can do is to define a row renderer function as the following:

>>> def increment_by_one(row):
...     for element in row:
...         yield element + 1

Then pass it onto save_as function using row_renderer:

>>> pe.isave_as(file_name="your_file.csv",
...             row_renderer=increment_by_one,
...             dest_file_name="your_file.xlsx")

Note

If the data content is from a generator, isave_as has to be used.

We can verify if it was done correctly:

>>> pe.get_sheet(file_name="your_file.xlsx")
your_file.csv:
+---+----+----+
| 2 | 22 | 32 |
+---+----+----+
| 3 | 23 | 33 |
+---+----+----+
| 4 | 24 | 34 |
+---+----+----+
| 5 | 25 | 35 |
+---+----+----+
| 6 | 26 | 36 |
+---+----+----+
| 7 | 27 | 37 |
+---+----+----+

Not all parameters are needed. Here is a table

source parameters
loading from file file_name, sheet_name, keywords
loading from string file_content, file_type, sheet_name, keywords
loading from stream file_stream, file_type, sheet_name, keywords
loading from sql session, table
loading from sql in django model
loading from query sets any query sets(sqlalchemy or django)
loading from dictionary adict, with_keys
loading from records records
loading from array array
loading from an url url

Parameters

file_name :
a file with supported file extension
file_content :
the file content
file_stream :
the file stream
file_type :
the file type in file_content or file_stream
session :
database session
table :
database table
model:
a django model
adict:
a dictionary of one dimensional arrays
url :
a download http url for your excel file
with_keys :
load with previous dictionary’s keys, default is True
records :
a list of dictionaries that have the same keys
array :
a two dimensional array, a list of lists
sheet_name :
sheet name. if sheet_name is not given, the default sheet at index 0 is loaded
start_row : int
defaults to 0. It allows you to skip rows at the begginning
row_limit: int
defaults to -1, meaning till the end of the whole sheet. It allows you to skip the tailing rows.
start_column : int
defaults to 0. It allows you to skip columns on your left hand side
column_limit: int
defaults to -1, meaning till the end of the columns. It allows you to skip the tailing columns.
skip_row_func:

It allows you to write your own row skipping functions.

The protocol is to return pyexcel_io.constants.SKIP_DATA if skipping data, pyexcel_io.constants.TAKE_DATA to read data, pyexcel_io.constants.STOP_ITERATION to exit the reading procedure

skip_column_func:

It allows you to write your own column skipping functions.

The protocol is to return pyexcel_io.constants.SKIP_DATA if skipping data, pyexcel_io.constants.TAKE_DATA to read data, pyexcel_io.constants.STOP_ITERATION to exit the reading procedure

skip_empty_rows: bool
Defaults to False. Toggle it to True if the rest of empty rows are useless, but it does affect the number of rows.
row_renderer:
You could choose to write a custom row renderer when the data is being read.
auto_detect_float :
defaults to True
auto_detect_int :
defaults to True
auto_detect_datetime :
defaults to True
ignore_infinity :
defaults to True
library :
choose a specific pyexcel-io plugin for reading
source_library :
choose a specific data source plugin for reading
parser_library :
choose a pyexcel parser plugin for reading
skip_hidden_sheets:
default is True. Please toggle it to read hidden sheets

Parameters related to csv file format

for csv, fmtparams are accepted

delimiter :
field separator
lineterminator :
line terminator
encoding:
csv specific. Specify the file encoding the csv file. For example: encoding=’latin1’. Especially, encoding=’utf-8-sig’ would add utf 8 bom header if used in renderer, or would parse a csv with utf brom header used in parser.
escapechar :
A one-character string used by the writer to escape the delimiter if quoting is set to QUOTE_NONE and the quotechar if doublequote is False.
quotechar :
A one-character string used to quote fields containing special characters, such as the delimiter or quotechar, or which contain new-line characters. It defaults to ‘”’
quoting :
Controls when quotes should be generated by the writer and recognised by the reader. It can take on any of the QUOTE_* constants (see section Module Contents) and defaults to QUOTE_MINIMAL.
skipinitialspace :
When True, whitespace immediately following the delimiter is ignored. The default is False.
pep_0515_off :
When True in python version 3.6, PEP-0515 is turned on. The default is False
Parameters related to xls file format:
Please note the following parameters apply to pyexcel-xls. more details can be found in xlrd.open_workbook()
logfile:
An open file to which messages and diagnostics are written.
verbosity:
Increases the volume of trace material written to the logfile.
use_mmap:

Whether to use the mmap module is determined heuristically. Use this arg to override the result.

Current heuristic: mmap is used if it exists.

encoding_override:
Used to overcome missing or bad codepage information in older-version files.
formatting_info:

The default is False, which saves memory.

When True, formatting information will be read from the spreadsheet file. This provides all cells, including empty and blank cells. Formatting information is available for each cell.

ragged_rows:

The default of False means all rows are padded out with empty cells so that all rows have the same size as found in ncols.

True means that there are no empty cells at the ends of rows. This can result in substantial memory savings if rows are of widely varying sizes. See also the row_len() method.

dest_file_name:
another file name.
dest_file_type:
this is needed if you want to save to memory
dest_session:
the target database session
dest_table:
the target destination table
dest_model:
the target django model
dest_mapdict:
a mapping dictionary see pyexcel.Sheet.save_to_memory()
dest_initializer:
a custom initializer function for table or model
dest_mapdict:
nominate headers
dest_batch_size:
object creation batch size. it is Django specific
dest_library:
choose a specific pyexcel-io plugin for writing
dest_source_library:
choose a specific data source plugin for writing
dest_renderer_library:
choose a pyexcel parser plugin for writing

if csv file is destination format, python csv fmtparams are accepted

for example: dest_lineterminator will replace default ‘ ‘ to the one you specified

In addition, this function use pyexcel.Sheet to render the data which could have performance penalty. In exchange, parameters for pyexcel.Sheet can be passed on, e.g. name_columns_by_row.