Stop Fixing Your Users' Sloppy Excel Files

Bill Donofrio
Aug 18
7 min read

Are you tired of getting sloppy Excel files where you spend hours making corrections before ever ingesting them into your lakehouse? Excel is flexible and easy to use, which is exactly the problem. Excel files exist in every company, and they are almost never organized. Traditional analysts tend to keep tidy files, but plenty of others do not. These files live in the shadows, where the data governance team can not find them. They are everywhere, and they are bound to cause you problems.

Let me walk you through the nightmare scenario that led me to build the solution below. I worked on a project where a group of researchers created 37 individual surveys in Qualtrics. All of them shared the same 50 core questions, but half included additional ones. No governance was ever built into Qualtrics directly. Even the dropdown menus had small discrepancies between surveys. For example, one question asked for a professor's highest level of education. “Bachelor” was the choice in most surveys, but one or two spelled it as “Bachelor's.” Notice the apostrophe and s in the second version.

Even after we collected all the data, transformed it into a structured format with the proper data types, built the Power BI report, and reviewed it multiple times with the user group, we kept finding one off issues for years. For example, if a question asked “How long have you worked here?” we expected an integer answer. If someone answered 3.5, we could round up to 4, but if they entered “3+,” the query would silently resolve it to NULL.

After a year or two of frustration, I created a simple tool to reject these terrible files before they were ever loaded. This script can be configured to locate the problems and highlight them directly in the Excel file. Simply send the file back to the user, highlights and all, and let them fix it before it ever becomes your problem. Once the burden is on their shoulders, they tend to become much stronger data stewards.

To keep this walkthrough simple and avoid using any real company data, the example below runs against a small sample pet adoption dataset instead of the Qualtrics surveys. The validation approach is exactly the same no matter what your Excel file contains.

Step 1: Import the Necessary Libraries

Start with the handful of libraries the script needs: pandas for the DataFrame, datetime for date validation, re for pattern matching, and statistics for outlier detection.

import pandas as pd

from datetime import datetime

import re

import statistics

Step 2: Load Sample Data for Testing

Here is the sample dataset loaded into a DataFrame. In production, this would be the raw Excel file a user just handed you. Use this command to ingest an Excel file: df = pd.read_excel('test_file.xlsx')

data = {

"name": ["Whiskers", "Luna", "Milo", None, None, "Leo",

"Nala", "Simba", "Cleo", "Tiger"],

"breed": ["Maine Coon", "Siamese", "British Shorthair", "Persian", "Ragdoll",

"Bengal", "Sphynx", "Abyssinian", "Scottish Fold", "Domestic Shorthair"],

"age_years": [4, 2, 6, 8, 1, 3, 5, 7, 2, 31],

"weight_lbs": [105.2, 8.4, 12.1, 10.8, 7.9, 11.5, 6.8, 9.2, 8.0, 13.4],

"color": ["Brown Tabby", "Seal Point", "Blue Gray", "White", "Cream",

"Spotted Gold", "Hairless Pink", "Ruddy", "Gray", "Orange Tabby"],

"is_indoor": [True, True, True, False, True, False, True, True, True, False],

"favorite_toy": ["Feather wand", "Laser pointer", "Crinkle ball", "Ca",

"String", "Puzzle feeder", "Cardboard box", "Feather wand",

"Laser pointer", "Crinkle ball"],

"adoption_date": [

"2022-03-15", "2024-06-01", "2020-01-10", "2018-11-22", "2025-02-29",

"2023-07-19", "2021-09-05", "2019-04-30", "2024-01-08", "2016-12-25"

"vaccinated": [True, True, True, True, False, True, True, True, False, True],

"owner_name": ["Sarah Chen", "James Patel", "Maria Lopez", "David Kim", "Emma Brooks",

"Noah Ortiz", "Ava Thompson", "Liam Walker", "Sofia Reyes", "Ethan Brooks"],

"tag_number": ["tag_634534", "tag_3454", "tag_24342", "tag_342342", "tag_434234",

"tag_11222", "tag_534343", "342342", "tag_11122", "tag_2222"],

}

df = pd.DataFrame(data)

display(df)

Step 3: Import the Configuration File

The real power of this approach comes from a configuration table. Each row defines the expectations for one column. In this example, we review the data type, minimum and maximum length, allowed numeric range, whether it can be blank, an optional regex pattern, and an optional column that becomes required if another column has a value.

config = [

["name", "string", 1, 30, None, None, "No", None, "breed"],

["breed", "string", 1, 30, None, None, "No", None, "name"],

["age_years", "integer", 1, 2, 0, 25, "No", None, None],

["weight_lbs", "float", 1, 2, 1, 30, "No", None, None],

["color", "string", 1, 20, None, None, "Yes", None, None],

["is_indoor", "boolean", 4, 5, None, None, "No", None, None],

["favorite_toy", "string", 3, 30, None, None, "Yes", None, None],

["adoption_date", "date", 8, 8, None, None, "No", None, None],

["vaccinated", "boolean", 4, 5, None, None, "No", None, None],

["owner_name", "string", 3, 30, None, None, "No", None, None],

["tag_number", "string", 3, 10, None, None, "No", "^tag_", None]

]

config_df = pd.DataFrame(

config,

columns=['column_name', 'data_type', 'min_length', 'max_length',

'greater_than', 'less_than', 'nullable', 'pattern', 'conditional_column']

)

display(config_df)

Step 4: Validate the Data Against the Configuration

This is the core of the script. It loops through every column defined in the configuration. It then checks every value against the applicable rules. The final results are stored in a list.

error_list = []

# ----------------------------------------------------------------

# Type-check helpers

# ----------------------------------------------------------------

def is_valid_date(value, fmt='%Y-%m-%d'):

try:

datetime.strptime(value, fmt)

return True

except (ValueError, TypeError):

return False

def is_valid_timestamp(value, fmt='%Y-%m-%d %H:%M:%S'):

try:

datetime.strptime(value, fmt)

return True

except (ValueError, TypeError):

return False

# ----------------------------------------------------------------

# Error recording

# ----------------------------------------------------------------

def add_error(row, value, test_column_list, error_message):

column = row['column_name']

bad_value = value

rows = [i + 1 for i, x in enumerate(test_column_list) if x == bad_value]

error_list.append((column, rows, bad_value, error_message))

# ----------------------------------------------------------------

# Main validation routine

# ----------------------------------------------------------------

for index, row in config_df.iterrows():

test_column_list = list(df[row['column_name']])

test_column_set = set(test_column_list)

# Bonus - calculate outlier bounds: three standard deviations from the median

if row['data_type'] in ('integer', 'float'):

std_dev_allowed = 3

median = statistics.median(test_column_list)

std_dev = statistics.stdev(test_column_list)

three_std = std_dev_allowed * std_dev

lower_bound = median - three_std

upper_bound = median + three_std

else:

lower_bound = 0

upper_bound = 0

for value in test_column_set:

# --- 1. Data type check ---------------------------------

if ((row['data_type'] == 'string' and not isinstance(value, str))

or (row['data_type'] == 'integer' and not isinstance(value, int))

or (row['data_type'] == 'float' and not isinstance(value, float))

or (row['data_type'] == 'boolean' and not isinstance(value, bool))

or (row['data_type'] == 'date' and is_valid_date(value) == False)

or (row['data_type'] == 'timestamp' and is_valid_timestamp(value) == False)

error_message = f'"{value}" is not a {row["data_type"]}.'