duckdb / duckdb/duckdb-excel

Type inference fails when first data row contains empty cells, causing data loss

Open
#69 0 comments 0 reactions 0 assignees View on GitHub
Dominant language
C++
Stars
62
Forks
24
PR merge metrics
No merged PRs in 30d

Description

## Description

Hi! We're using the DuckDB Excel extension in our open-source project [PondPilot](https://github.com/pondpilot/pondpilot) to display Excel files in a data exploration interface. We've encountered a significant issue with type inference that causes data loss.

When an Excel file has empty cells in the first data row (after headers), DuckDB incorrectly infers column types, leading to data loss or import failures.

## Example File Structure
```
Row 1 (headers): [status] [module] [module_version] [duration_seconds]
Row 2 (data): ["downloaded"] [EMPTY] [EMPTY] [EMPTY]
Row 3 (data): [EMPTY] ["abc1"] ["1.0.1"] [5]
Row 4 (data): [EMPTY] ["abc2"] ["1.0.2"] [7]
```

## Current Behavior
1. DuckDB infers column types based on **first data row only** (Row 2)
2. Empty cells are inferred as `DOUBLE` type
3. When processing Row 3, `"abc1"` cannot be converted to `DOUBLE` → error
4. With `ignore_errors=true`: data is replaced with NULL (data loss)
5. Without `ignore_errors`: import fails completely

## Our Current Workaround
We're forced to use `all_varchar=true` which works but has major drawbacks:
- All numeric columns become strings
- No automatic type inference
- Users must manually cast types for calculations
- Poor user experience

## Expected Behavior

The extension should analyze multiple rows (not just the first) to determine appropriate column types, similar to how `read_csv()` works with its `sample_size` parameter.

## Suggested Solutions

1. **Add `sample_size` parameter to `read_xlsx()`** - analyze N rows for type inference
2. **Implement smart type inference** - if first row is empty/mixed, analyze more rows
3. **Add `auto_detect_types` parameter** with better heuristics

## Impact

This affects any Excel file with:
- Missing data in first rows
- Mixed data patterns
- Real-world messy data (very common)

We'd be happy to help test any fixes or provide additional examples. This is blocking proper Excel support in our data exploration tool.

## Environment
- DuckDB Excel Extension: latest
- Use case: Web-based data exploration tool
- File types: Business Excel files with missing/mixed data patterns

**Related**: Our issue tracker [#209](https://github.com/pondpilot/pondpilot/issues/209)
**Test data**: [azure_data.xlsx](https://github.com/user-attachments/files/22022707/azure_data.xlsx)

Contributor guide

No contributing guide indexed for this repository

Research direction

Start by reproducing the failure in read_xlsx() with the attached azure_data.xlsx, focusing on type inference when the first data row has empty cells. Compare the behavior with read_csv()'s sample_size handling. Done means later non-empty values retain appropriate types without import failure or data loss.

Written by the indexing model from the issue text.

Assessment

Tech stack
cpp
Domain
databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.