一次巨坑的Excel导入MySQL

2018-09-15, 825 Words, 2 Minutes

一次巨坑的Excel导入MySQL

事情是这样的,小伙伴给了一个excel,我需要把execl导入到MySQL数据库,方便增删改查。

之前在Win下开发一直用Navicat,现在Mac下也不例外。

这个问题就很有趣了:

我把excel直接通过Navicat导入到数据库,查看中文内容是正常的,数据表的DDL显示字符编码是Latin1,列没有定义。Mac的Navicat在编辑连接的地方有个“高级”选项卡,里面的编码我设置的是utf-8。理论上说应该乱码才对,但是从Navicat里面看都是正常的,其他所有的客户端都乱码。包括命令行下设置了set names utf8也不行。

现在,我把数据表改成utf-8编码的。

然后,我寻思可能是excel的问题,尝试把excel另存为csv,默认得到的文件编码是GBK的(高版本的excel在另存为csv的时候,可以选择utf-8编码的csv),用文本编辑器将其转码成utf-8。然后再用Navicat进行导入,导入的时候选择编码是utf-8,感觉已经全都考虑到了,结果仍然不行。

至此我怀疑是Navicat的问题,所以开始尝试使用命令进行导入。

load data local infile '/data/www/tmp/aa.csv' 
into table test.t5 
FIELDS TERMINATED BY ','  -- 列分隔符
OPTIONALLY enclosed by '"' -- 如果csv文件的每个列的值两边有引号,需要加这个
escaped by '"'  -- 转义符号,没有可以不加,默认是反斜线
LINES TERMINATED BY '\r\n' -- 换行符
IGNORE 1 lines; -- 一般第一列都是表头,可以忽略

然而仍然不行。。。

执行了help load data,偶然发现可以设置字符集,试一下:

load data local infile '/data/www/tmp/aa.csv'
into table test.t5
character set utf8  -- 这里
FIELDS TERMINATED BY ',' 
OPTIONALLY enclosed by '"' 
escaped by '"'
LINES TERMINATED BY '\r\n' 
IGNORE 1 lines;

居然可以了。。。

总结一下步骤:

  1. 设置数据表编码为utf-8。
  2. 将excel另存为csv,然后用文本编辑器转码为utf-8。
  3. 使用load data命令,设置编码为utf-8。

Excel取消科学计数法显示

默认情况下,Excel中较长的数字会显示为科学计数法的形式,导出csv的时候也是科学计数法的,我们可以通过一下步骤处理:

  1. 选中相关的列
  2. 格式 -> 单元格 -> 数字 -> 自定义 -> 0

(完)


Next:
Prev: