Handling Messy CSV

Datagrunt enforces data integrity by default and provides developer tools to handle real-world messy CSV files. This guide covers CSVReader/CSVWriter’s lenient and normalize_columns options — see the Readers and Writers reference for full signatures.

Fail-Fast Input Validation

CSVReader and CSVWriter validate their inputs at construction time, so mistakes surface immediately with a clear error instead of failing later inside an engine:

• An unrecognized engine name raises a ValueError listing the valid engines.
• A path that points to a directory raises a ValueError.
• A path that does not exist raises a FileNotFoundError.
• Files that are not UTF-8 encoded no longer crash construction; metadata detection handles them gracefully.

Resilient Ingestion & Lenient Mode

Fail Loudly by Default

By default (lenient=False), parsing ragged rows (lines with mismatched column counts) fails loudly:

• Polars & PyArrow engines raise a ComputeError or ArrowInvalid exception.
• DuckDB engine runs with strict_mode=true and raises a parsing exception.

This prevents silent data loss or column misalignment.

Lenient Mode (lenient=True)

When lenient=True is set:

• Missing values are padded with null.
• Extra columns are truncated to match the header length.
• Developer Warnings: Datagrunt scans the file structure (up to 10,000 rows) and issues a standard Python UserWarning detailing row numbers and column count mismatches.
• Engine Fallbacks:
  • Polars: Native line truncation/padding is utilized.
  • PyArrow: Automatically coordinates a Polars-to-Arrow schema conversion under the hood using zero-copy memory mapping to avoid serialization overhead.
  • DuckDB: Disables strict mode and pads missing columns with nulls automatically.

CSVWriter accepts the same lenient parameter — when True, the reader engine handles malformed lines and issues warnings prior to writing the output files. Every writer method, including write_parquet, honors this setting.

reader_lenient = CSVReader('path/to/file.csv', lenient=True)
writer_lenient = CSVWriter('path/to/file.csv', lenient=True)

Comments, Blank Lines, and Empty Files

Datagrunt treats only leading # lines — comment lines at the very top of the file — as comments to skip. Once real data begins, #-prefixed rows (hex color codes like #FF5733, hashtags, etc.) are regular data and are preserved on every engine. Leading blank lines are skipped without corrupting the header or shifting data rows, and mid-file # lines no longer crash the PyArrow engine, which now matches Polars row-for-row.

Known limitation: the DuckDB engine’s CSV sniffer can still mis-detect files that combine a leading # comment block with #-prefixed data rows. For files that mix both, use the Polars (default) or PyArrow engine.

Empty and blank files are handled consistently: get_sample() and every writer produce an empty result rather than raising or inventing a phantom column0.

The row_count_with_header and row_count_without_header properties count logical CSV records, so quoted fields containing embedded newlines count as a single record rather than multiple lines.

Legacy Mac OS Carriage Returns

Legacy Mac OS newlines (\r) are natively supported and parsed by the duckdb engine. Modern engines like Polars and PyArrow do not natively support them:

• Attempting to load a legacy \r CSV with Polars or PyArrow raises a helpful ValueError suggesting to switch to engine='duckdb' or convert the newlines.
• Metadata features (like .columns, .columns_normalized, and .csv_string_sample) automatically use python-native fallbacks to guarantee metadata parity across all engines.

Column Name Normalization

normalize_columns=True is available on every reader and writer constructor (CSVReader/CSVWriter, ExcelReader/ExcelWriter, ParquetReader/ParquetWriter). For CSV specifically, when set, all outputs (and SQL queries for the DuckDB engine) automatically use normalized column names (lowercase, underscores, collision-safe). Colliding names (for example, Col A and col_a) are resolved deterministically (col_a, col_a_1) and consistently across all three CSV engines.

reader_normalized = CSVReader('path/to/file.csv', normalize_columns=True)