Excel files

Overview

This page outlines our guidelines for parsing data from Excel files to a primary model.

Strategy

“Tabular” Excel Files

If the raw data is a simple Excel file containing a single table, we recommend using pandas.

Use pandas.read_excel to read the Excel file into a pandas.DataFrame, validating the structure & content via a pandera.DataFrameModel primary model.

Always use dtype=str when reading the Excel file, to preserve the raw string values, which may have more precision than their float representations.

“Mixed Format” Excel Files

In cases where the Excel file contains multiple tables, or other structured sections, we recommend the following:

  • Read the file with pandas.read_excel to a pandas.DataFrame, using dtype=str to preserve the raw string values.

  • Split the dataframe into different sections, according to the structure of the file. Typically this can be done by slicing the dataframe based on when certain headers or keys appear.

  • Where sections contain a table, validate via a pandera.DataFrameModel primary model.

  • Where sections contain non-tabular data, validate via a pydantic.BaseModel primary model.

  • Combine the sections into the final primary model.