Excel文件导入数据库（MySQL）

沙漠孤松

怎么把数从excel中转到数据库呢？有什么方法，我就说四种
为什么是四种嘞；
因为我就会四种方法；真的是毫无保留，裤衩都不保留。



excel恶心策略+mysql没有生气了

2种可视化界面导入，1种sql语句导入，1种python导入；

<hr/>以下面的文件为例，之前使用过的的一个文件（来源于excel透视）；
date        name        type        pay
2017/1/1        妈妈        油费        74
2017/1/15        妈妈        食品        235
2017/1/17        爸爸        运动        20
2017/1/21        康霓        书籍        125
2017/2/2        妈妈        食品        235
2017/2/20        康霓        音乐        20
2017/2/25        康霓        门票        125


之前用过的文件

信息：文件存到e盘，文件名为test，路劲为e:\test.xlsx；
随便保存，找不到最好；



-------------------我是一条分割线，哦~~~------------------

方法一、MySQL Workbench界面手动导入
MySQL Workbench，MySQ的可视化工具；安装mysql的时候会有MySQL Workbench勾选；



红色框框里面的内容

首先我们要把想要导入mysql的excel文件格式修改一下，不修改就没得导了；
可以理解成：导入mysql，首先要满足mysql的要求啦，怎么能说导就导；

第一点，excel文件要csv格式，其实txt格式也是可以的；
第二点，编码为utf-8；
满足这两点，就可以MySQL Workbench导入数据了；



如图

怎么做嘞；下面
第一步、Excel文件另存为csv格式（wps转也是可以的）；



如果你的Excel存在utf-8，csv，逗号分隔格式，直接点utf-8，csv，逗号分隔格式就好

完成csv类型保存后，下一步转utf-8格式；



通过记事本修改一下编码就好



附图

重点备注：如果你的Excel保存类型存在utf-8，csv，逗号分隔类型，直接点utf-8，csv，逗号分隔类型就好，不用再转utf-8了

第二步、导入向导



导入步骤.gif

步骤分离:
1）右键数据库，点击table data import wizard（导入数据向导）



table data import wizard

2）browse文件路径（浏览确定路径）



浏览确定路径

3）确认数据库，表名，格式信息；



确认数据库，表名信息



确认格式信息

格式信息是选择的默认；
4）next，finish



next 下一步



finish 完成

很详细；
第三步、验证



数据没有问题

为了直观，我把图片截图了过来，平时不要这样验证；
不然眼睛会聋的
平时验证可以验证一下表格行数是否一致；数值列求和sum(pay)是否一致；
就差不多了；
<hr/>方法二、navict 界面手动导入



红色框框里面的

Navicat数据导入；csv，xlsx，xls都可以；



如图

第一步：
可以直接用Excel文件导入；不用转csv格式了，如果你想转，当然可以的，入口不一样罢了；



很长

步骤分离:
1）导入向导



导入向导

2）不同的入口（常见的txt，csv，xlsx），以xlsx为例；



如何进去



不同的入口，风景不一样哦





新建表



字段格式



模式自己个选



自建表，自导

3）数据验证



以上

<hr/>方法三、mysql语句导入
一条代码就可以轻松搞定，真是太_____了（填空题）
语句导入，可以在cmd命令里面，当然也可以在MySQL Workbench或者navict里面执行
下面在cmd中执行一下；



如图

第一步：建表（不一样了哦）
语句导入表格要有表格，没有表头不能导哦；
CREATE TABLE excel_db.test_3 (
date  date,
NAME VARCHAR ( 10 ),
type VARCHAR ( 10 ),
pay INT
);
-- 建表语句，注意一下格式；
第二步：文件转格式
要转成csv，逗号分隔，utf-8格式；
步骤看方法一的步骤，一模一样；

第三步：导入数据
附cmd操作图



load data local infile 'e:/test.csv' into table   fields terminated by ',';

备注：数据量一旦很大的时候要少用select *，可以加上limit；
上面的图片就等于


load data local infile 'e:/test.csv' into table  excel_db.test_3
fields terminated by ',';
-- e:/文件导入数据库excel中的表里，逗号分隔第三步：数据验证



哪来的？？？

1、为什么第一行有Excel表头
是因为语句把所以的数据导入test_3表中了；
2、为什么date列第一行是0000-00-00
因为data列建表为date(日期)格式，把非日期数据都转成了0；
3、怎么解决嘞
把第一行删掉；
DELETE FROM excel_db.test_3 LIMIT 1备注：自己真实使用的时候，还会遇到很多，各种各样的问题；



哈哈

前三种方式就结束了，先小结一下各种方式的优势和劣势：
方法一、MySQL Workbench界面手动导入
需要转格式，且需要手动导入数据，要改字段的格式，3颗星；
方法二、navict 界面手动导入
不需要转格式，手动导数据，不需要建表，但要改字段的格式，4颗星；
方法三、mysql语句导入
要转格式，要建表，但数据可以自动导，单元格中有英文逗号也很尴尬（还要引号包围），3颗星；
综上:可以结合一下来使用；
<hr/>方法四、通过python将excel文件导入mysql中；
这个就很强喽



如图

python，直接上代码；
import pymysql       ##PyMySQL是在 Python3.x 版本中用于连接 MySQL 服务器的一个库
import pandas as pd  ##Pandas是Python的一个数据分析包 导入panda命名为pd
from sqlalchemy import create_engine ## 导入引擎

file = r'e:/test.xlsx'    ## 文件
df = pd.read_excel(file) ## 读文件

## 连接数据库
engine = create_engine("mysql+mysqlconnector://root:****@/excel_db") ## ****代表你的数据库密码
df.to_sql('test',con=engine,if_exists='replace',index=False)  ##导入数据库，如果存在就替换导入数据库后，文本会默认text格式，text占用内存大些，不过问题不大；
if_exists='replace' 如果存在就替换，那追加呢？
if_exists='append'
<hr/>over，撒花；

没那么矫情

你好，我是用第一种方法导入的数据，为什么行数少了很多？谢谢！

寂寞如斯

哈哈

郭亚军

第一种方法导入好像有大量数据不是很友好（十万条以上级别），速度非常之慢（不知道是因为我的电脑原因还是方法本身就是这样）；第二条导入方式，亲测导入xlsx时，数据表选中打不开，在第二步就卡住了，然后将excel文件转换成csv格式倒是可以导入，但是也回存在问题，一个是将excel文件转换为csv文件时如果数据过长（超过12位好像），就会出现科学计数法表示，不利于进行后续统计处理等操作，另外如果数字开头为0（譬如一些编号，类似身份证号），转换成csv时也会自动给去除掉，自己在网上搜了方法进行解决，大概是改变数据格式之类的，好不容易用第二种方法导进去之后，又出现部分数据不一致的现象，真是按起葫芦又起瓢，折腾了半天，真是太心累了，希望有能力的人能科普更多问题解决办法，帮助更多人解决实际问题....

闵光吁

请问这种方法建的数据库，数据是存在本地是么？excel导入有最大数据的限制么？例如我有100万行的excel？

知荣守辱

你可以试一下看看，这数据太大了，excel最大也就100万行吧

孤鸿俊

大佬 问一下我用的是第一种方法。数据一直显示("Unknown column 'None' in 'field list'", 1054)是为什么，我确定没有空数据了。我用utf-8保存他识别不了必须用另一个csv保存然后用utf-8识别。在选格式的位置我没有数据类型的选项，只有一个column_name.

知荣守辱

我也是！请问您解决这个问题了吗

尚微

解决了，但我已经忘了当初怎么解决的了。。[捂脸]

伴糖

好想问一下为什么第一列汉字就是插入不进去呢[大哭]