将Excel数据导入MySQL中的关系表 [英] Import Excel Data to Relational Tables at MySQL

问题描述

我的MySQL数据库中有三个表。第一个是城市，第二个是城镇，第三个是城市。每个城镇都有很多地区。我的表格详细信息：

城市：

cityid，城市名称

城镇：

townid，cityid，townname，continent

区域：

zoneid，townid，districtname

我有一个excel文件，其中包含一个城市的城镇和地区名称。它有三列。城市名称，地区名称

城市名称一直相同在这个excel表。镇名称由于其地区而重复。我的意思是一个城镇的每个区域：城市名称和城镇名称相同，地区名称与往常不同。例如：

城市名称，城镇名称，地区名称

XYA

XYB

XKC

XKD

解决方案

您可以将您的Excel工作表另存为CSV文件，然后在Excel表格的相同列的临时MySQL表中导入此类文件通过使用 LOAD DATA INFILE 命令，最后在城市，城镇和区域三个表格中分割临时表格记录。

一个前提：由于Excel文件中没有一个id字段，所以我想ids是auto_increment字段;此外，towns表的大陆字段也将始终设置为空值，因为Excel表单中不存在该字段;最后，我会假设所有的名称字段的最大长度为255个字符。

从样本数据开始，通过以CSV格式导出Excel Sheet并将其保存（例如） C：\Temp\excel.csv 文件，你应该得到这样的东西：

 city name，town name，district name
X，Y，A
X，Y，B 
X，K，C
X，K，D
  

要将此文件导入MySQL数据库，请创建一个 excel2mysql.sql 文件，其中包含以下内容，执行它：

  DROP TABLE如果EXISTS excel_table; 
 CREATE temporary TABLE excel_table（
 city_name VARCHAR（255），
 town_name VARCHAR（255），
 district_name VARCHAR（255）
）DEFAULT CHARSET utf8; 
 
 LOAD DATA LOCAL INFILE'C：/Temp/excel.csv'
 INTO TABLE excel_table 
 CHARACTER SET utf8 
 FIELDS TERMINATED BY'，'
由$
线程终止\\\
'
 IGNORE 1 LINES; 
 
 DROP TABLE如果EXISTS城市; 
 CREATE TABLE城市（
 city_id int NOT NULL auto_increment，
 city_name VARCHAR（255），
主键（city_id）
）DEFAULT CHARSET utf8; 
 
 INSERT INTO cities 
 SELECT distinctrow NULL，city_name 
 FROM excel_table 
 ORDER BY city_name; 
 
 DROP TABLE IF EXISTS towns; 
 CREATE TABLE towns（
 town_id int NOT NULL auto_increment ，
 city_id int NOT NULL，
 town_name VARCHAR（255），
 continent VARCHAR（255），
主键（town_id）
）DEFAULT CHARSET utf8; 
 
 INSERT INTO towns 
 SELECT distinctrow NULL，city_id，town_name，''
 FROM excel_table，cities 
 WHERE cities.city_name = excel_table.city_name 
 ORDER BY town_name; 
 
 DROP TABLE如果EXISTS区域; 
 CREATE TABLE区域（
区域标识int NOT NULL auto_increment，
 town_id int NOT NULL，
区域名VARCHAR（255），
主键（区域标识符）
 ）DEFAULT CHARSET utf8; 
 
 INSERT INTO区域
 SELECT distinctrow NULL，town_id，district_name 
 FROM excel_table，towns 
 WHERE towns.town_name = excel_table.town_name 
 ORDER BY district_name; 
  

I have three tables at my MySQL database. First one is cities, second one is towns and third one is districts. Every town has many districts. My table details:

cities:

cityid, city name

towns:

townid, cityid, townname, continent

districts:

districtid, townid, districtname

I have an excel file that holds the town and district names of one city. It has three columns.

city name, town name, district name

city name is always same at this excel sheet. town name has duplicates because of its districts. I mean for every district of a town: city name and town names are same and district names are different as usual. For example:

city name, town name, district name

X Y A

X Y B

X K C

X K D

解决方案

You could save your Excel sheet as in a CSV file, then import such file in a temporary MySQL table with the same columns of the Excel sheet by using the LOAD DATA INFILE command, and finally split the temporary table records in the three tables "cities", "towns" and "districts".
A premise: since none of the "id" fields is present in the Excel file, I suppose that all the ids are "auto_increment" fields; also, the "continent" field of the "towns" table will always be set to a blank value, since this field is not present in your Excel sheet; finally, I'll assume that all the "name" fields have a maximum length of 255 characters.
Starting from your sample data, by exporting the Excel Sheet in the CSV format and saving (for example) into the "C:\Temp\excel.csv" file, you should get something like this:

"city name","town name","district name"
"X","Y","A"
"X","Y","B"
"X","K","C"
"X","K","D"

To import this file into your MySQL database, create a "excel2mysql.sql" file with the following content, and execute it:

DROP TABLE IF EXISTS excel_table;
CREATE temporary TABLE excel_table (
  city_name VARCHAR(255),
  town_name VARCHAR(255),
  district_name VARCHAR(255)
) DEFAULT CHARSET utf8;

LOAD DATA LOCAL INFILE 'C:/Temp/excel.csv' 
INTO TABLE excel_table 
CHARACTER SET utf8
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\n' 
IGNORE 1 LINES;

DROP TABLE IF EXISTS cities;
CREATE TABLE cities (
  city_id int NOT NULL auto_increment,
  city_name VARCHAR(255),
  primary key (city_id)
) DEFAULT CHARSET utf8;

INSERT INTO cities 
  SELECT distinctrow NULL, city_name 
    FROM excel_table 
   ORDER BY city_name;

DROP TABLE IF EXISTS towns;
CREATE TABLE towns (
  town_id int NOT NULL auto_increment,
  city_id int NOT NULL,
  town_name VARCHAR(255),
  continent VARCHAR(255),
  primary key (town_id)
) DEFAULT CHARSET utf8;

INSERT INTO towns 
  SELECT distinctrow NULL, city_id, town_name, '' 
    FROM excel_table, cities 
   WHERE cities.city_name=excel_table.city_name 
   ORDER BY town_name;

DROP TABLE IF EXISTS districts;
CREATE TABLE districts (
  district_id int NOT NULL auto_increment,
  town_id int NOT NULL,
  district_name VARCHAR(255),
  primary key (district_id)
)  DEFAULT CHARSET utf8;

INSERT INTO districts 
  SELECT distinctrow NULL, town_id, district_name 
    FROM excel_table, towns 
   WHERE towns.town_name=excel_table.town_name 
   ORDER BY district_name;

这篇关于将Excel数据导入MySQL中的关系表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！