参考：
1. 将Excel中数据迁移到MySQL中
- 将Excel转化为CSV格式，记住保存的路径和文件名最好别出现中文，删除属性名，仅保留数据即可。之后用Sublime Text3打开，在file选项中有set file encoding to 选择UTF-8
- 更改Mysql编码
我们要把utf8编码格式的文件导入到mysql中，因此需要设置mysql字符集为utf8.
进入mysql，查看mysql当前字符集：
show variables like 'char%';
必须确保以下五项均为utf8.
character_set_client
character_set_connection
character_set_database
character_set_results
character_set_server
如果其中有一项不是，需要修改/etc/mysql/my.cnf文件。
首先停止mysql，然后打开my.cnf文件。
在[mysqld]标签下添加character_set_server=utf8和init_connect=’SET NAMES utf8′
然后保存退出，重启mysql，如果没有变化可以重启机器。
这一步骤，我的没有问题，没有修改，正确性待考证。 - 在MySQL创建表格，表格的属性与EXcel中保持一致。
create table consume(
studentId char(10) not null, #10为数字的号码串
cardId char(6), #6为数字的号码串
address varchar(40), #不定号码串，显示中文地址
Jdatetime datetime, # 显示日期和时间
money int #显示消费金额
);
- 导入数据测试，将CSV中所有数据复制两条到新文件consume.CSV中，执行
load data local infile 'E:\\Desktop\\paper\\consume.CSV' into table consume fields terminated by ',' lines terminated by '\n';
显示其他结果均正常，只有时间显示格式为：0000-00-00 00:00:00
时间导入失败 - 解决时间导入失败问题
重新建立表格，设置时间格式为varchar(30)，重新生成表格和导入数据，显示结果正确，接着将字符串格式转化为时间格式
ALTER TABLE consume MODIFY COLUMN Jdatetime datetime;
结果仍然错误。原因：给出的时间格式为：11/4/2014 13:39:00，SQL需要的时间格式为：2014-11-04 13:39:00 - 从Excel表格解决问题：分列
将日期和时间分成两列，日期格式为M/D/Y，时间格式设为文本即可。
这样分成两列，然后修改日期列的类型为自定义：yyyy-mm-dd，这就再转化为CSV即可，此时一行的数据形式如下：1111111111 ,159777,良乡第二食堂三层 ,2014-11-02,15:04:57,-1400 - 从重新建立表格，导入数据即可，没问题了。
2. 将数据从MySQL导出到Excel中
- 使用命令：
select * into outfile "E:\\test.txt" from consume;
显示错误：Error Code: 1290. The MySQL server is running with the --secure-file-priv option so it cannot execute this statement 0.000 sec - 解决办法：使用代码：
show variables like '%secure%';
找到secure_file_priv的值C:\ProgramData\MySQL\MySQL Server 5.7\Uploads\
把这个路径作为保存路径：select * into outfile "C:\\ProgramData\\MySQL\\MySQL Server 5.7\\Uploads\\test.txt" from consume;这样就生成了txt文档 - 新建excel，选择数据来自于文本即可，设置一下，即可导入。
3. 使用Python将Excel中的数据导入到MySQL
使用pymysql和openpyxl两个库，具体代码如下：
import pymysql
from openpyxl import load_workbook
wb = load_workbook('E:\\Desktop\\paper\\data_deal\\2014-11-12.xlsx')
sheet = wb.active
conn = pymysql.connect(
user="root",
password="******",
port=3306,
host="127.0.0.1", # 本地数据库 等同于localhost
db="student", # 数据库名
charset="utf8"
)
cur = conn.cursor() # 获取对应的操作游标
query = 'insert into consume (studentId, cardId, address, Jdate, Jtime, money) values (%s, %s, %s, %s, %s, %s)'
for i in range(1, sheet.max_row+1):
studentId = (sheet.cell(row=i, column=1)).value
cardId = (sheet.cell(row=i, column=2)).value
address = (sheet.cell(row=i, column=3)).value
Jdate = (sheet.cell(row=i, column=4)).value
Jtime = (sheet.cell(row=i, column=5)).value
money = (sheet.cell(row=i, column=6)).value
values = (studentId, cardId, address, Jdate, Jtime, money)
# 执行sql语句
cur.execute(query, values)
cur.close()
conn.commit()
conn.close()
print(sheet.max_row, "行数据导入结束")
4. 将excel文件中的数据导入到mysql
在你的表格中增加一列，利用excel的公式自动生成sql语句，具体方法如下：
1）增加一列（假设是D列）
2）在第一行的D列，就是D1中输入公式：
=CONCATENATE("insert into tablename (col1,col2,col3) values (",A1,",",B1,",",C1,");")
3）此时D1已经生成了如下的sql语句：
insert into table (col1,col2,col3) values ('a','11','33');
4）将D1的公式复制到所有行的D列（就是用鼠标点住D1单元格的右下角一直拖拽下去啦）
5）此时D列已经生成了所有的sql语句
6）把D列复制到一个纯文本文件中，假设为sql.txt
把sql.txt放到数据库中运行即可，你可以用命令行导入，也可以用phpadmin运行。
网友评论