LOAD DATA INFILE to load data into different tables

Question

I have two tables:

Company(id int, varchar(name), primary key(id));
Product (id int, c_id int, varchar(name), foreign key(c_id), references Company(id));

Table 'Company' stores a list of company names and 'Product' stores a list of product names, and one company can have multiple products.

If I have data file like this, tab delimited:

1  Apple  iPhone
2  Apple  iPad
3  Apple  iMac
4  Google Gmail
5  Google Google Search
6  Amazon Kindle

Is it possible to use "Load DATA INFILE" to load this file into two tables, where the first column goes to table Company and second coloumn goes to Product? The question is how to load selected fields to a particular table, instead of loading the full record into one table.

Answer 1

You can try an approach like this, with the premise of using a temporary table. I hope you find it useful.

/path/to/file/file.csv

1,Apple,iPhone
2,Apple,iPad
3,Apple,iMac
4,Google,Gmail
5,Google,Google Search
6,Amazon,Kindle

mysql> DELIMITER //

mysql> DROP TRIGGER IF EXISTS `from_load_data`//
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DROP TABLE IF EXISTS `temp_company_product`//
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DROP TABLE IF EXISTS `product`//
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DROP TABLE IF EXISTS `company`//
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE `company` (
    ->     `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ->     `name` VARCHAR(25),
    ->     UNIQUE KEY `unique_name` (`name`)
    -> )//
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `product` (
    ->     `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ->     `c_id` INT UNSIGNED NOT NULL,
    ->     `name` VARCHAR(25),
    ->     FOREIGN KEY (`c_id`) REFERENCES `company`(`id`)
    ->         ON UPDATE CASCADE ON DELETE CASCADE
    -> )//
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `temp_company_product` (
    ->     `id` INT UNSIGNED PRIMARY KEY,
    ->     `company_name` VARCHAR(25),
    ->     `product_name` VARCHAR(25)
    -> )//
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TRIGGER `from_load_data` AFTER INSERT ON `temp_company_product`
    -> FOR EACH ROW
    -> BEGIN
    ->     INSERT INTO `company` (`name`) VALUES (NEW.`company_name`)
    ->         ON DUPLICATE KEY UPDATE `name` = VALUES(`name`);
    ->     INSERT INTO `product` (`c_id`, `name`)
    ->     SELECT `id`, NEW.`product_name`
    ->     FROM `company`
    ->     WHERE `name` = NEW.`company_name`;
    -> END//
Query OK, 0 rows affected (0.00 sec)

mysql> LOAD DATA INFILE '/path/to/file/file.csv'
    ->     INTO TABLE `temp_company_product`
    -> FIELDS TERMINATED BY ',' ENCLOSED BY '"'
    -> LINES TERMINATED BY '\r\n'
    -> (`id`, `company_name`, `product_name`)//
Query OK, 6 rows affected (0.00 sec)
Records: 6  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT
    ->     `id`,
    ->     `company_name`,
    ->     `product_name`
    -> FROM
    ->     `temp_company_product`//
+----+--------------+---------------+
| id | company_name | product_name  |
+----+--------------+---------------+
|  1 | Apple        | iPhone        |
|  2 | Apple        | iPad          |
|  3 | Apple        | iMac          |
|  4 | Google       | Gmail         |
|  5 | Google       | Google Search |
|  6 | Amazon       | Kindle        |
+----+--------------+---------------+
6 rows in set (0.00 sec)

mysql> SELECT
    ->     `id`,
    ->     `name`
    -> FROM
    ->     `company`//
+----+--------+
| id | name   |
+----+--------+
|  6 | Amazon |
|  1 | Apple  |
|  4 | Google |
+----+--------+
3 rows in set (0.00 sec)

mysql> SELECT
    ->     `id`,
    ->     `c_id`,
    ->     `name`
    -> FROM
    ->     `product`//
+----+------+---------------+
| id | c_id | name          |
+----+------+---------------+
|  1 |    1 | iPhone        |
|  2 |    1 | iPad          |
|  3 |    1 | iMac          |
|  4 |    4 | Gmail         |
|  5 |    4 | Google Search |
|  6 |    6 | Kindle        |
+----+------+---------------+
6 rows in set (0.00 sec)

mysql> DROP TRIGGER IF EXISTS `from_load_data`//
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE IF EXISTS `temp_company_product`//
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

Answer 2

I don't believe that LOAD DATA is flexible enough to allow to you selectively load certain columns into two different already-existing tables. It was designed to be a fast work horse, but was not designed to be particularly flexible. An alternative would be to load your data into a temporary table, and then INSERT INTO...SELECT the data into your two tables.

CREATE TABLE temp(id int, company varchar(55), product varchar(55));

LOAD DATA LOCAL INFILE 'file.csv' INTO TABLE temp
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(id, company, product);

And then just use INSERT INTO...SELECT to get the data into your two tables which already exist:

INSERT INTO Company (name)
SELECT company
FROM temp

INSERT INTO Product (name)
SELECT product
FROM temp