Excel

Installation

To work with Excel files in SDV, please install SDV with the optional dependencies using:

pip install 'sdv[excel]'

ExcelHandler

Use this object to create a handler for reading and writing an local Excel spreadsheet

from sdv.io.local import ExcelHandler

connector = ExcelHandler()

Parameters (None)

Output An ExcelHandler object you can use to read and write an Excel spreadsheet

read

Use this function to read an Excel spreadsheet from your local machine

data = connector.read(
    filepath='project/data.xlsx',
    sheet_names=['guests', 'hotels']
)

Parameters

• (required) filepath: A string name of the Excel spreadsheet, which must end in .xlsx

• sheet_names : A list of sheet names to read from the overall spreadsheet. Each sheet is a tab in your spreadsheet file, and represents a different table.

  • (default) None: Read all the sheets of the overall spreadsheet

  • <list>: Read only the sheets listed from the overall spreadsheet

Output A dictionary that contains all the data found the Excel file. The key is the name of the sheet and the value is a pandas DataFrame containing the data.

write

Use this function to write synthetic data into an Excel spreadsheet

connector.write(
  synthetic_data,
  filepath='project/synthetic_data.xlsx',
  mode='x'
)

Parameters

• (required) synthetic_data: You data, represented as a dictionary. The key is the name of each table and the value is a pandas DataFrame containing the data.

• (required) filepath: A string name of the file where you would like to write the synthetic data

• sheet_name_suffix: The suffix to add to each sheet name. Use this if to add specific version numbers or other info.

  • (default) None: Do not add a suffix. Each sheet will be named with table name

  • <string>: Append the suffix after each table name. Eg. a suffix '_synth1' will write a sheet as table_synth1

• mode: A string signaling which mode of writing to use

  • (default) 'w': Write sheets to a new Excel file, clearing any existing file that may exist

  • 'a': Write the data as new sheets within an existing Excel file

Output (None) The data will be written as an Excel spreadsheet