I am trying to import multiple csv's in a dataframe at one time using pandas to_sql, to a MySQL database. After creating the engine, I am running the following:
folder_path = (file_path)
os.chdir(folder_path)
for file in os.listdir(folder_path):
if '.csv' in file:
df = pd.read_csv(file, low_memory = False)
table_name = str(file.strip('.csv'))
df.to_sql(table_name, con = engine, if_exists = 'replace')
However, when I run the code, I get the following error: "UnicodeEncodeError: 'charmap' codec can't encode characters in position 0-7: character maps to "
Even when I try using the import wizard to uload that specific table the error is appearing on, it only imports 50 out of the 42,000 records.
Any help is appreciated!
asked Jul 8, 2021 at 10:52
shuaf98
371 gold badge1 silver badge6 bronze badges
1 Answer 1
I am not sure if this is the "correct" way of doing it, but I found a regex which selects only characters in UTF-8, removing the rest, for each field in the dataframe:
df.replace({r'[^\x00-\x7F]+':''}, regex=True, inplace=True)
Ideally though, I would like to keep the non UTF-8 characters, if there are any other solutions.
answered Jul 8, 2021 at 16:52
shuaf98
371 gold badge1 silver badge6 bronze badges
Sign up to request clarification or add additional context in comments.
Comments
default
CSVdata is in the file. Check line 49, 50 and 51 of theCSVread_csv). Do not trust Python will find it for you (and unfortunately WIndows still use unpredictable defaults)