load_spreadsheets

pyhelpers.store.load_spreadsheets(path_to_file, as_dict=True, verbose=False, prt_kwargs=None, raise_error=False, **kwargs)[source]

Load one or multiple sheets from a Microsoft Excel or an OpenDocument format file.

Parameters:

  • path_to_file (str | os.PathLike) – Path where the spreadsheet file is saved.

  • as_dict (bool) – Whether to return the retrieved data as a dictionary; defaults to True.

  • verbose (bool | int) – Whether to print relevant information to the console; defaults to False.

  • raise_error (bool) – Whether to raise the provided exception; if raise_error=False (default), the error will be suppressed.

  • prt_kwargs (dict | None) – [Optional] Additional parameters for the function pyhelpers.store._check_loading_path(); defaults to None.

  • kwargs – [Optional] Additional parameters for the method pandas.ExcelFile.parse().

Returns:

Data of all worksheets in the file from the specified pathname path_to_file.

Return type:

list | dict | None

Note

  • Example data can be referred to in the functions save_multiple_spreadsheets() and save_spreadsheet().

Examples:

>>> from pyhelpers.store import load_spreadsheets
>>> from pyhelpers.dirs import cd

>>> dat_dir = cd("tests", "data")
>>> path_to_xlsx = cd(dat_dir, "dat.ods")

>>> wb_data = load_spreadsheets(path_to_xlsx, verbose=True, index_col=0)
Loading "tests/data/dat.ods" ...
  'TestSheet1'. ... Done.
  'TestSheet2'. ... Done.

>>> list(wb_data.keys())
['TestSheet1', 'TestSheet2']

>>> wb_data['TestSheet1']
            Longitude   Latitude
City
London      -0.127647  51.507322
Birmingham  -1.902691  52.479699
Manchester  -2.245115  53.479489
Leeds       -1.543794  53.797418

>>> path_to_xlsx = cd(dat_dir, "dat.xlsx")
>>> wb_data = load_spreadsheets(path_to_xlsx, verbose=True, index_col=0)
Loading "tests/data/dat.xlsx" ...
  'TestSheet1'. ... Done.
  'TestSheet2'. ... Done.
  'TestSheet11'. ... Done.
  'TestSheet21'. ... Done.
  'TestSheet12'. ... Done.
  'TestSheet22'. ... Done.

>>> list(wb_data.keys())
['TestSheet1',
 'TestSheet2',
 'TestSheet11',
 'TestSheet21',
 'TestSheet12',
 'TestSheet22']

>>> wb_data = load_spreadsheets(path_to_xlsx, as_dict=False, index_col=0)

>>> type(wb_data)
list

>>> len(wb_data)
6

>>> wb_data[0]
            Longitude   Latitude
City
London      -0.127647  51.507322
Birmingham  -1.902691  52.479699
Manchester  -2.245115  53.479489
Leeds       -1.543794  53.797418