UnicodeDecodeError on CSV that is successfully read by pandas and polars #93

UnicodeDecodeError                        Traceback (most recent call last)
Cell In[5], [line 1](vscode-notebook-cell:?execution_count=5&line=1)
----> [1](vscode-notebook-cell:?execution_count=5&line=1) con.sql(
      2     f"CREATE TABLE company AS SELECT * FROM '{path}/rhetorik/companies/csv/*.csv.gz'"
      3 )

UnicodeDecodeError: 'utf-8' codec can't decode byte 0xc3 in position 10061: invalid continuation byte

UnicodeDecodeError                        Traceback (most recent call last)
Cell In[19], [line 1](vscode-notebook-cell:?execution_count=19&line=1)
----> [1](vscode-notebook-cell:?execution_count=19&line=1) con.sql(f"INSERT INTO company SELECT * FROM '{path}/rhetorik/companies/csv/{file}'")

UnicodeDecodeError: 'utf-8' codec can't decode byte 0xc3 in position 10061: invalid continuation byte

import duckdb
import polars as pl
import pandas as pd


path = "/run/media/rgilland/T9/data"
file = f"{path}/rhetorik/companies/csv/company-data_15_7_0.csv.gz"

# SUCCEEDS
pl.read_csv(file, encoding='utf8')
pd.read_csv(file, encoding='utf-8')

# FAILS
duckdb.sql(f"SELECT count(*) FROM '{file}'")
duckdb.sql(f"SELECT count(*) FROM read_csv_auto('{file}')")

import os
import duckdb

path = "/run/media/rgilland/T9/data"

if os.path.exists(f"{path}/rhetorik/rhetorik.db"):
    os.remove(f"{path}/rhetorik/rhetorik.db")

with duckdb.connect(f"{path}/rhetorik/rhetorik.db") as con:
    con.sql(
        f"CREATE TABLE company AS SELECT * FROM '{path}/rhetorik/companies/csv/*.csv.gz'"
    )