How to import .xls file to .sql with a foreign key

Question

I have a excel spreadsheet:

id name  region  zone  
1  pokin Polon   Riny
2  lucy  yerni   kinta
...

And i have tables in mysql database which has region_id and zone_id instead which are foreign key to id in region and zone instead.

users table:

id name region_id zone_id
1  retre  1        1
...

region table:

id region_name
1   Polon
...

and zone table

id zone_name
1   kinta
...

I need to import the excel spreadsheet to users table.

Answer 1

1. Export the table as you show it in the spreadsheet (with spelled out names).
2. Import that into MySQL - into table X, say.
3. Perform the following query to do the "normalization" as you create the desired table (with numbers instead of names):

Something like this:

INSERT INTO users
        (id, name, region_id, zone_id)
    SELECT X.id, X.name, r.id, z.id
        FROM X
        LEFT JOIN region AS r ON r.region_name = X.region
        LEFT JOIN zone   AS z ON z.zone_name   = X.zone;

If id is an AUTO_INCREMENT, then you might want to do it slightly differently. (Leave id out of the INSERT and the SELECT.)

I used LEFT in case there are some missing regions or zoned. In which case, you will get NULLs or default values for region_id or zone_id, thereby indicating that something needs fixing.

Answer 2

I suggest that in your .xls sheet, replace the zone and region names with the actual ids on the database for those fields. Then you can export your xls file into a csv file, and then easily import that into your database using mysqlimport

mysqlimport --ignore-lines=1 \
        --fields-terminated-by=, \
        --local -u root \
        -p DatabaseName \
         YourExportedFile.csv

A few things to consider here:

1. The path to the file when you are executing the command.
2. The fiels-terminate-by character
3. "-p DatabaseName", DatabaseName is not the password, is your database name, the password will be prompted when you execute the command.

You could import your CSV file with a SQL statement using LOAD DATA INFILE

LOAD DATA INFILE "/home/user/YourExportedFile.csv"
INTO TABLE YOUR_TABLE
COLUMNS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

Additionally if you are using any graphical MySQL client (mysql workbench, heideSql, mysqlpro, etc) you can use the import functionality instead.

Answer 3

Assuming none of these tables exist, first create three CSV files users.csv, regions.csv, zone.csv.

regions.csv and zones.csv will have a single column which you can import into the DB is region_name or zone_name. I'm assuming the tables will be set up with AUTO_INCREMENT so the id values will take care of themselves.

To create these files in Excel get distinct values using Data > Remove Duplicates. If you have access to something like phpMyAdmin import files to the database, then import these two tables.

Creating users: For getting the foreign keys into the users table, I would create the users table with:

name, regions, zone, region_id, zone_id

You can import your original file into the name,regions,zone fields, then update the foreign keys.

UPDATE users as u SET region_id = (SELECT id FROM regions as r WHERE r.region_name = u.region) 

Then just drop the region and zone columns.