Import Excel Data to Relational Tables at MySQL

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 f开发者_StackOverflow中文版ile 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;

---

A rough example of using MySQL with Excel and ADO. Suitable connection strings can be got from 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

---

If your District, Town or City names are not in plain ASCII (have accented characters), then you may want to make one or tweaks to @Enzino solution; possibly just adding a footnote to any user instructions to instruct the user that when they save the sheet to a CSV file, they will need to select, within the Save as Dialog box, the: Tools->Web Options option, select the Encoding tab, in the resulting dialogue box and set the character-set to match that of the corresponding mySQL table: Unicode (UTF-8) before saving.

Alternatively, and depending on whose doing the import, then the following answer of mine may help them get the data directly into the excel_table.