import csv into MySQL ( UnicodeDecodeError: 'charmap' codec can't decode byte )

on

 I have a problem when importing csv.

my code:

import MySQLdb
import csv
from datetime import datetime
conn = MySQLdb.connect(host="192.168.2.20", user="xxx", password="xxxx", database="admin_mt")
cursor = conn.cursor()
cursor.execute("TRUNCATE TABLE wp_dbo_iclock_transaction2")

csv_data = csv.reader(open('dbo_iclock_transaction.csv'))
header = next(csv_data)


print('Importing the CSV Files')
for row in csv_data:
  
    punch_time = datetime.strptime(row[2], '%d/%m/%Y %H:%M').strftime('%Y-%m-%d %H:%M:%S')
    
    cursor.execute("""INSERT INTO wp_dbo_iclock_transaction2
                                  (id, emp_code, punch_time)
                                       VALUES (%s,%s,%s)""",
                                       (row[0], row[1], punch_time))

conn.commit()
cursor.close()
print('Done')

The error I get :

Traceback (most recent call last):
  File "D:\Server Backup\time_import\import.py", line 13, in <module>
    header = next(csv_data)
  File "C:\Program Files\Python311\Lib\encodings\cp874.py", line 23, in decode
    return codecs.charmap_decode(input,self.errors,decoding_table)[0]
UnicodeDecodeError: 'charmap' codec can't decode byte 0x8a in position 310: character maps to <undefined>

I'm not sure what caused the problem. Or maybe it's because the csv file is encoding utf8 !


The error message suggests that there is a problem with the encoding of the CSV file you are trying to read. The default encoding used by the csv.reader() function is utf-8, but it seems that your CSV file may be encoded with a different character encoding.

You can specify the encoding of the CSV file when you open it using the encoding parameter in the csv.reader() function. You can try specifying the character encoding of your CSV file explicitly by replacing this line:

kotlin
csv_data = csv.reader(open('dbo_iclock_transaction.csv'))

with:

kotlin
csv_data = csv.reader(open('dbo_iclock_transaction.csv', encoding='utf-8'))

If the above solution does not work, you can try to determine the actual encoding of your CSV file using a text editor or command-line tool such as file, and then specify that encoding in the csv.reader() function.

For example, if the CSV file is encoded in cp874, you can specify it by replacing the above line with:

kotlin
csv_data = csv.reader(open('dbo_iclock_transaction.csv', encoding='cp874'))

I hope this helps!

Comments

Post a Comment