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