将Excel数据导入MySQL关系表

Question

我在MySQL数据库中有三个表。第一个是城市，第二个是镇区，第三个是区县。每个镇区都有许多区县。我的表细节如下：

城市：

城市id，城市名称

镇区：

镇区id，城市id，镇区名称，洲名

区县：

区县id，镇区id，区县名称

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

城市名称，镇区名称，区县名称

这个Excel表格中的城市名称总是相同的。由于其区县的原因，镇区名称会有重复。我的意思是，对于一个镇区的每个区县：城市名称和镇区名称相同，而区县名称像往常一样不同。例如：

城市名称，镇区名称，区县名称

X Y A

X Y B

X K C

X K D

- kamaci

---

@george，你具体是什么意思？下面的帖子尽可能地解决了所有问题，而不需要与用户的环境进行交互。 - Fionnuala

@Remou，我不知道ADO是什么意思，也不知道在哪里编写那段代码。 - ilhan

我还有一个额外的级别，使其成为4。 - ilhan

@Remou，你能实现最佳投票答案吗？ - kamaci

@kamaci 你的意思是什么？人们经常建议将数据导出为CSV格式，以便在Excel中使用。但我认为这并没有听起来那么安全。 - Fionnuala

Answer 1

您可以将Excel表格另存为CSV文件，然后使用LOAD DATA INFILE命令将此类文件导入具有与Excel表格相同列的临时MySQL表中，并最终将临时表记录拆分成三个表“cities”、“towns”和“districts”。前提条件是：由于Excel文件中没有出现任何“id”字段，因此我假设所有的id都是“auto_increment”字段；此外，“towns”表中的“continent”字段将始终设置为空值，因为此字段在您的Excel表格中不存在；最后，我将假设所有的“name”字段的最大长度为255个字符。从您的示例数据开始，通过将Excel表格导出为CSV格式并保存（例如）到“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 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;

- user1419445

Answer 2

使用MySQL、Excel和ADO的简单示例。适当的连接字符串可以从http://connectionstrings.com获取。

Dim cn As New ADODB.Connection
Dim sFile As String, scn As String, sSQL As String
Dim MySQLConnectionString As String

''It is probably better to use the name of the workbook
''eg C:\Docs\Cities.xls
sFile = ActiveWorkbook.FullName

''Access 2003 or less
scn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sFile _
    & ";Extended Properties=""Excel 8.0;HDR=Yes"";"
cn.Open scn

MySQLConnectionString = "ODBC;Driver={MySQL ODBC 5.1 Driver};Server=localhost;" _
& "Database=mydatabase;User=myuser;Password=mypass;Option=3;"

''Fields (columns) are case sensitive
''Insert cities from sheet1, you can also use a named range, 
''include or a range
sSQL = "INSERT INTO [" & MySQLConnectionString & "].Cities (Id,City) " _
& "SELECT Id,City FROM [Sheet$] WHERE Id Not In " _
& "(SELECT Id FROM [" & MySQLConnectionString & "].Cities )"

cn.Execute sSQL, dbFailOnError

- Fionnuala

Answer 3

如果您的区，镇或城市名称不是普通ASCII字符（带有重音符号），那么您可能需要对@Enzino的解决方案进行微调；可能只需向任何用户说明添加一个脚注，指示用户在将表格保存为CSV文件时，需要在另存为对话框中选择： 工具->Web选项选项，在结果对话框中选择编码选项卡，并将字符集设置为与相应的mySQL表匹配的字符集：Unicode（UTF-8），然后再保存。

或者，根据导入数据的人员不同，我的以下answer可能会帮助他们直接将数据导入excel_table中。

- arober11