Summary of frequently asked questions about importing Excel data into Mysql: How to deal with encoding problems encountered during import?
Importing Excel data into a MySQL database is a common task. However, during this process, encoding problems are often encountered. This article will explore several common coding problems and provide corresponding solutions.
import pandas as pd # 读取Excel数据 df = pd.read_excel('data.xlsx', encoding='utf-8') # 打印前5行数据 print(df.head())
CONVERT
to handle special characters. The following is a sample code: import pandas as pd import pymysql # 连接到MySQL数据库 conn = pymysql.connect(host='localhost', user='root', password='password', db='database') cursor = conn.cursor() # 读取Excel数据 df = pd.read_excel('data.xlsx', encoding='utf-8') # 插入数据 for index, row in df.iterrows(): name = row['name'] address = row['address'] # 处理特殊字符 name = conn.escape(name) address = conn.escape(address) # 插入数据到MySQL数据库 sql = f"INSERT INTO table_name (name, address) VALUES ({name}, {address})" cursor.execute(sql) # 提交事务 conn.commit() # 关闭连接 cursor.close() conn.close()
to_datetime
function to uniformly convert date data to specified format. Here is a sample code: import pandas as pd # 读取Excel数据 df = pd.read_excel('data.xlsx', encoding='utf-8') # 转换日期格式 df['date'] = pd.to_datetime(df['date'], format='%Y-%m-%d') # 打印前5行数据 print(df.head())
Summary:
Encoding issues are a common challenge when importing Excel data into a MySQL database. Most encoding problems can be solved by specifying the encoding format before reading Excel data, using MySQL's built-in functions to handle special characters, and converting date formats. Depending on the situation, appropriate solutions can be selected to handle encoding issues encountered during the import process. Hope this article is helpful to you.
The above is the detailed content of Summary of frequently asked questions about importing Excel data into Mysql: How to deal with encoding problems encountered when importing?. For more information, please follow other related articles on the PHP Chinese website!