需要先安装pandas
import pandas as pd
path = r'D:\Desktop\\新建文件夹\test.xlsx'
data = pd.read_excel(path)
table_name = 'table_name '
columns = ', '.join(data.columns)
with open('insert_statements.txt', 'w', encoding='utf-8') as f:
insert_statements = []
for index, row in data.iterrows():
valuesList = []
for col_name, value in row.items():
if pd.isna(value):
value = 'NULL'
valuesList.append(value)
else:
if isinstance(value, (int, float)):
# 如果是数值类型
value = str(value)
valuesList.append(str(value))
else:
# 如果是字符串类型
value = str(value).strip()
value = value.replace("'", "\\'").replace("\r\n", " ").replace("\n", " ")
value = f"'{value}'"
valuesList.append(value)
values = ', '.join(valuesList)
insert_statement = f"INSERT INTO {table_name} ({columns}) VALUES ({values});"
insert_statements.append(insert_statement)
f.writelines(insert_statement)
# 打印出来看看效果
for statement in insert_statements:
print(statement)