Excel

This functionality is in Beta! Beta functionality may have bugs and may change in the future. Help us out by testing this functionality and letting us know if you encounter any issues.

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