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.
6 Answers
Answers 1
- Export the table as you show it in the spreadsheet (with spelled out names).
- Import that into MySQL - into table
X, say. - 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.name LEFT JOIN zone AS z ON z.zone_name = X.name; 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.
Answers 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:
- The path to the file when you are executing the command.
- The fiels-terminate-by character
- "-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.
Answers 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.
Answers 4
First we need to bring the spreadsheet in the table format in MySQL by using Export/Import feature Export the table as you show it in the spreadsheet (with spelled out names). Import that into MySQL - into table tempUsers, say. 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 tempUsers.id, tempUsers.name, region.id, zone.id FROM X LEFT JOIN region AS region ON region.region_name = tempUsers.region LEFT JOIN zone AS zone ON zone.zone_name = tempUsers.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. Because Auto_Increment will make the rowcount to get increased by 1 whenever the new record enters)
Happy Programming
Answers 5
Normally this is what i Do.
Create excel formula to generate insert script. Id would be an auto generate so just skip it. Assuming your id is in A cell, name in B cell, region and zone in C and D respectively.
="INSERT INTO USER_TABLE(NAME, REGION_ID, ZONE_ID) VALUES ('"&B1&"',(select id from region where region_name = '"&C1&"'), (select id from zone where zone_name = '"&D1&"')"; After create 1 drag the formula down to the end rows, copy the script and execute it.
Things I consider:
- Need to patch in a lot of environment, Local, SIT, UAT, Production maybe. I got no DB access to Production environment so Insert script is the best way.
- Not involve so many things and easier to change in case next time needed with new column added.
Answers 6
For a repeated requirement, Foreign keys could be mapped in Excel. Export region table and zone table to Excel file. like (ex: filename: lookupmaster.xlsx)
region name region Id Polon 1 yerni 2 In the spreadsheet with actual data, add two columns as
region_id, zone_id Get region Id for all rows using vlookup
VLOOKUP(B2,[lookumaster.xlsx]Sheet1!$A$1:$B$4,2,FALSE) To remove dependency on master, key column, copy region Id column and paste special as values only in its place.
Repeat the same for zone Id
You could remove the region and zone column from spreadsheet and import it to database.
0 comments:
Post a Comment