Misidentification of column data type leads to certain sheets being unreadable

[ivanfsimeon]

Sample column values:

ref_pfr_no
211110015349060
211150015009060
DIV
221400000671023
221170000731023
DIV

When a column starts with a numeric-looking text, and there are other text values within the column, querying & displaying the values gives the error "duckdb.duckdb.InvalidInputException: Invalid Input Error: Failed to cast value: Could not convert string 'DIV' to DOUBLE"

Adjusting the query to cast the values doesn't resolve the issue:
con.sql("SELECT ref_pfr_no::text FROM read_gsheet('', sheet='');").show()

[archiewood]

Yes, this is a bug

Thanks for reporting.

possible options:

• everything is a varchar on read, let users cast to other types
• read more rows before deciding type
• use whatever duckdb uses in the csv sniffer for this

[archiewood]

@Alex-Monahan makes the point that we are already looping through every row for bool/double processing, so we might as well do it for column identification as well!

[mharrisb1]

This is partially solved now with #55

[archiewood]

This issue persists with latest version

D from 'https://docs.google.com/spreadsheets/d/1tFsNhD3jkjKtVm2iWogKfdxq2zMqZyPWhyn5lE-1UvQ/edit?gid=0#gid=0';
Invalid Input Error:
Failed to cast value: Could not convert string 'DIV' to DOUBLE

[mharrisb1]

For now the only workaround is to use all_varchar option in read_gsheet table function and try to clean up the data like this:

SELECT
  IF(ref_pfr_no = 'DIV', NULL, ref_pfr_no)::DOUBLE as ref_pfr_no
FROM read_gsheet('<SHEET_ID>', all_varchar=true);

We can add more complex type sniffing similar to read_csv but this would either use a string for this column and you'd still need that additional processing above or it would result in the same error if the sample size was not large to see the DIV values.

Maybe for things like DIV or other common cell error values we could automatically swap with null, 0, or whatever else would be appropriate but I'm not sure if that's the right direction.

[archiewood]

Where I think we should go with this, in order of priority and sophistication

1. More helpful error message

Type of ref_pfr_no detected as DOUBLE, but failed to cast string 'DIV' to DOUBLE in row 3
Use read_gsheet('<SHEET_ID>',all_varchar=true) to cast all columns to VARCHAR

2. Auto handling

• Read is successful without specifying
• Column is mapped to varchar
• All other columns are whatever they are

3. Configuration

• option which allows you to specify the column type (as double, e.g.)
• option to map invalid values to null/zero
• option to skip rows with invalid values