xlsx_to_csv

pyhelpers.store.xlsx_to_csv(xlsx_pathname, csv_pathname=None, engine=None, if_exists='replace', vbscript=None, sheet_name='1', ret_null=False, verbose=False, **kwargs)

Convert Microsoft Excel spreadsheet (in the format .xlsx/.xls) to a CSV file.

See also [STORE-XTC-1].

Parameters

  • xlsx_pathname (str) – pathname of an Excel spreasheet (in the format of .xlsx)

  • csv_pathname (str or None) – pathname of a CSV format file; when csv_pathname=None (default), the target CSV file is generated as a tempfile.NamedTemporaryFile; when csv_pathname="", the target CSV file is generated at the same directory where the source Excel spreadsheet is; otherwise, it could also be a specific pathname

  • engine (str or None) – engine used for converting .xlsx/.xls to .csv; when engine=None (default), a Microsoft VBScript (Visual Basic Script) is used; when engine='xlsx2csv', the function would rely on xlsx2csv

  • if_exists (str) – how to proceed if the target csv_pathname exists, defaults to 'replace'

  • vbscript (str or None) – pathname of a VB script used for converting .xlsx/.xls to .csv, defaults to None

  • sheet_name (str) – name of the target worksheet in the given Excel file, defaults to '1'

  • ret_null – whether to return something depending on the specified engine, defaults to False

  • verbose (bool or int) – whether to print relevant information in console, defaults to False

  • kwargs – [optional] parameters of the function subprocess.run

Returns

the pathname of the generated CSV file or None, when engine=None; io.StringIO buffer, when engine='xlsx2csv'

Return type

str or _io.StringIO or None

Examples:

>>> from pyhelpers.store import xlsx_to_csv, load_csv
>>> from pyhelpers.dirs import cd
>>> import os

>>> path_to_test_xlsx = cd("tests\data", "dat.xlsx")

>>> path_to_temp_csv = xlsx_to_csv(path_to_test_xlsx)
>>> os.path.exists(path_to_temp_csv)
True

>>> load_csv(path_to_temp_csv, index=0)
             Longitude    Latitude
City
London      -0.1276474  51.5073219
Birmingham  -1.9026911  52.4796992
Manchester  -2.2451148  53.4794892
Leeds       -1.5437941  53.7974185

>>> os.remove(path_to_temp_csv)  # Remove the temporary CSV file