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

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.

  • Writing a function that doesn't return a value in VBA Excel
  • VBA | Macro functions will not compute worksheet data
  • Excel Visual Basic call function as stand-alone routine
  • C# dll calling from VBA can't find DLL entry point
  • Combobox resets to first “blank” entry if cell on left is blank Error
  • Copy cell C value to other sheet if Cell A = Cell B
  • 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.

  • C# Unable to print euro symbol into a file (when opening with Excel)
  • command button click on Userform enter textbox to cell, then loop columns per click
  • How do I get an Excel range using row and column numbers in VSTO / C#?
  • Creating events for checkbox at runtime Excel VBA
  • Fastest way to interface between live (unsaved) Excel data and C# objects
  • Formula and its inverse in Excel
  • 6 Solutions collect form web for “How to import .xls file to .sql with a foreign key”

    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.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.

    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.

    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.

    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

    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:

    1. 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.
    2. Not involve so many things and easier to change in case next time
      needed with new column added.

    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.

    MS Excel Spreadsheet is the best Office Software, Excel VBA and Excel Formulas make Spreadsheet work faster.