How I Fixed a Production Data Cleaner That Misread Excel Files as CSV

This is a submission for DEV's Summer Bug Smash: Clear the Lineup powered by Sentry.

Project Overview

CRM Lead Data Cleaner is a production Apify Actor that turns messy CSV and Excel lead files into clean, CRM-ready datasets.

The Actor normalizes phone numbers, validates and cleans email addresses, removes duplicate records, standardizes text fields, and assigns lead-quality scores. It accepts either a dataset URL or an uploaded file and processes the results into structured output that can be used in sales, lead-generation, and automation workflows.

While testing the production pipeline, I encountered a file-handling bug that caused a valid spreadsheet download to fail with a UnicodeDecodeError. Instead of treating the failure as an isolated exception, I used Sentry alongside the Actor's runtime logs to trace what the pipeline was actually downloading and how the file was being parsed.

The investigation revealed a format-routing problem: Excel content could reach the CSV parsing path and be decoded as UTF-8 text. This submission documents the bug, the production fix, and the validation that followed.

Bug Fix or Performance Improvement

The bug appeared when the Actor received spreadsheet data that was not actually plain UTF-8 CSV content.

The original loading path could route downloaded content directly into pandas.read_csv(). When the downloaded file was an Excel workbook, or otherwise contained binary spreadsheet data, the CSV parser attempted to decode those bytes as UTF-8 text.

That resulted in failures such as:

UnicodeDecodeError: 'utf-8' codec can't decode byte ...

The important issue was not simply catching the exception. The Actor needed to determine the input format before choosing the parser.

The fix was to make the file-loading stage format-aware:

• .xlsx and .xls inputs are routed to pandas.read_excel().
• CSV inputs continue through pandas.read_csv().
• Downloaded content is first wrapped in an in-memory BytesIO buffer.
• Parsing failures go through the Actor's defensive safe_exit() path instead of producing an uncontrolled failure.
• Sentry captures the failure reason while Apify logs preserve the runtime context needed for debugging.

This changed the pipeline from assuming that every downloaded dataset was CSV into explicitly selecting the correct parser for the incoming file format.

After the fix, the Actor successfully processed the test dataset, produced two cleaned lead records, and exited normally with exit code 0.

Code

The core fix was in the file-loading path. Instead of sending every downloaded file through the CSV parser, the Actor now chooses the parser based on the input file type.

file_name = (file_url or file_key or "").lower()
file_buffer = io.BytesIO(content)

if file_name.endswith((".xlsx", ".xls")) or "output=xlsx" in file_name:
    Actor.log.info("📗 Reading Excel file")
    df = pd.read_excel(file_buffer)
else:
    Actor.log.info("📄 Reading CSV file")
    df = pd.read_csv(
        file_buffer,
        sep=None,
        engine="python",
        encoding="utf-8",
        on_bad_lines="skip"
    )

Parsing errors are then routed through a controlled failure path:

except Exception as e:
    await safe_exit("invalid_file", str(e))
    return

The safe_exit() helper records a structured error in the Actor dataset, sends the failure signal to Sentry, flushes pending Sentry events, and terminates the run with an explicit failure:

async def safe_exit(reason, message=""):
    Actor.log.error(f"❌ {reason}: {message}")

    sentry_sdk.capture_message(
        f"Actor failure: {reason}",
        level="error",
    )
    sentry_sdk.flush(timeout=5)

    await Actor.push_data({
        "status": "error",
        "reason": reason,
        "message": message
    })

    raise RuntimeError(f"Actor failed: {reason}")

Sentry itself is configured through an environment variable rather than hard-coding the DSN, with default PII collection disabled:

sentry_dsn = os.getenv("SENTRY_DSN")

if sentry_dsn:
    sentry_sdk.init(
        dsn=sentry_dsn,
        send_default_pii=False,
    )

My Improvements

I approached the fix as a reliability problem rather than patching only the visible UnicodeDecodeError.

1. Made file parsing format-aware

The pipeline now separates Excel and CSV parsing instead of assuming that downloaded content is UTF-8 CSV. This prevents binary spreadsheet data from reaching the text decoder.

2. Preserved the existing CSV workflow

The fix was deliberately small. Valid CSV inputs still use the existing pandas.read_csv() path, while Excel files are routed to pandas.read_excel(). This reduced the risk of introducing regressions into an already working path.

3. Added controlled failure handling

Input, download, file-not-found, and parsing failures are converted into explicit failure reasons such as:

• missing_input
• invalid_input
• file_not_found
• download_failed
• invalid_file

This makes failures easier to distinguish operationally instead of treating every problem as the same generic exception.

4. Connected failures to observability

The Actor logs the specific failure and reports the failure reason to Sentry before terminating. This gave me both the application-level context from Apify and centralized error visibility in Sentry.

5. Regression-tested the production path

After the fix, I reran the Actor using a valid CSV endpoint. The production run downloaded the file, detected two input rows, completed the cleaning pipeline, and exited with code 0.

The final output contained two structured CRM-ready records with normalized phone numbers, validated email addresses, scoring fields, and HIGH lead-quality classifications.

The main lesson from this bug was that successful downloading does not guarantee successful parsing. An HTTP 200 only confirms that bytes arrived; the application still needs to route those bytes to the correct parser.

Best Use of Sentry

Sentry turned this from a generic parsing failure into an observable debugging trail.

I integrated the Sentry Python SDK directly into the Actor and used Error Monitoring together with the runtime context captured around each failure. Rather than reporting only a generic crash, the Actor records explicit failure reasons such as missing_input, file_not_found, download_failed, and invalid_file.

During the failing spreadsheet run, Sentry captured the production exception:

UnicodeDecodeError: 'utf-8' codec can't decode byte 0xac in position 11: invalid start byte

The event showed that the exception originated from the Actor's invalid_file path. More importantly, the breadcrumbs preserved what happened immediately before the exception: the Google Sheets request completed successfully, the requested resource used output=xlsx, and the Actor then logged Reading CSV file.

That sequence exposed the real problem:

successful XLSX download -> CSV parsing path -> UTF-8 decoding -> UnicodeDecodeError

This was much more useful than the exception message by itself. The HTTP request had succeeded with a 200 response, so the failure was not simply a network problem. The runtime context showed that the downloaded bytes were being handed to the wrong parser.

I also used Sentry while deliberately exercising other failure paths. This confirmed that operational failures were being separated into meaningful issue types instead of disappearing into one generic error.

After correcting the parser routing, I reran the production Actor. The same pipeline completed normally:

Downloading -> Reading CSV file -> Rows before: 2 -> DONE -> exit code 0

The final Apify Output view displayed both cleaned records successfully.

Sentry therefore served two purposes in this fix: it helped preserve the evidence needed to identify the parser mismatch, and it gave me a repeatable way to verify that failure paths remained observable while the successful production path was restored.