将Excel表中数据导入mysql

处理Excel表格

首先将Excel需要的数据筛选出来, 导出为csv文件。

筛选的数据只需要行, 不需要列名。

之后用记事本打开, 另存为, 将文件编码修改为utf8

创建数据库和相应表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
CREATE DATABASE scores;

USE scores;

CREATE TABLE tab2021(
school VARCHAR(80),
major VARCHAR(80),
sc int DEFAULT null,
rk int DEFAULT null
);

CREATE TABLE tab2020(
school VARCHAR(80),
major VARCHAR(80),
sc int DEFAULT null,
rk int DEFAULT null
);

CREATE TABLE tab2019(
school VARCHAR(80),
major VARCHAR(80),
sc int DEFAULT null,
rk int DEFAULT null
);

将csv文件数据导入到表中

首先输入命令 SHOW VARIABLES LIKE '%secure%';

查看 secure_file_priv 的值, 确保导入的文件位置在该值指代的位置之下。

如果其值为null或没有包含导入文件的位置, 则在mysql安装目录下的my.ini 文件最后添加正确的路径, 如 secure_file_priv="D:\works\Summer2022ScoreTables"

之后重启mysql服务, 查看该值正确后, 导入数据。

1
2
3
4
5
6
7
8
9
10
LOAD DATA INFILE 'D:/works/Summer2022ScoreTables/2021.CSV'
INTO TABLE tab2021
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
(@col1, @col2, @col3)
SET
school=@col1,
major=@col2,
sc=nullif(@col3, '');
1
2
3
4
5
6
7
8
9
10
11
LOAD DATA INFILE 'D:/works/Summer2022ScoreTables/2020.CSV'
INTO TABLE tab2020
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
(@col1, @col2, @col3, @col4)
SET
school=@col1,
major=@col2,
sc=nullif(@col3, ''),
rk=nullif(@col4, '');
1
2
3
4
5
6
7
8
9
10
11
LOAD DATA INFILE 'D:/works/Summer2022ScoreTables/2019.CSV'
INTO TABLE tab2019
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
(@col1, @col2, @col3, @col4)
SET
school=@col1,
major=@col2,
sc=nullif(@col3, ''),
rk=nullif(@col4, '');