0 votes
in Education by (1.7m points)
i have one csv file which contains library group and it's data...

group consider as sheet and for each sheet contains phrase name and phrase value...

I want to insert this csv data to two different table using mysql how can i ?

suppose i have 2 table like

phrase_library_group

+-------------+--------------+------+-----+---------+----------------+

| Field       | Type         | Null | Key | Default | Extra          |

+-------------+--------------+------+-----+---------+----------------+

| GROUP_ID    | bigint(20)   | NO   | PRI | NULL    | auto_increment |

| GROUP_NAME  | varchar(100) | NO   |     | NULL    |                |

| ------------+--------------+------+-----+---------+----------------+

phrase_list

+-------------+--------------+------+-----+---------+----------------+

| Field       | Type         | Null | Key | Default | Extra          |

+-------------+--------------+------+-----+---------+----------------+

| PHRASE_ID   | bigint(20)   | NO   | PRI | NULL    | auto_increment |

| PHRASE_NAME | varchar(100) | NO   |     | NULL    |                |

| PHRASE_DESC | varchar(500) | NO   |     | NULL    |                |

| GROUP_ID    | bigint(20)   | NO   | MUL | NULL    |                |

| ------------+--------------+------+-----+---------+----------------+

here in image Additional_meds/Antibiotic Warnings/... are groups

and each group contains different phrases..like Additional_meds contains total 12 records as see below.

csv like

JavaScript questions and answers, JavaScript questions pdf, JavaScript question bank, JavaScript questions and answers pdf, mcq on JavaScript pdf, JavaScript questions and solutions, JavaScript mcq Test , Interview JavaScript questions, JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)

1 Answer

0 votes
by (1.7m points)
Do it natively in MySQL, this should be much faster and consume less ressources. Create a table in MySQL containing all fields from your CSV, i.e.

tmp_phrases

+-------------+--------------+------+-----+---------+----------------+

| Field       | Type         | Null | Key | Default | Extra          |

+-------------+--------------+------+-----+---------+----------------+

| GROUP_NAME  | varchar(100) | NO   |     | NULL    |                |

| PHRASE_NAME | varchar(100) | NO   |     | NULL    |                |

| PHRASE_DESC | varchar(500) | NO   |     | NULL    |                |

| ------------+--------------+------+-----+---------+----------------+

load data from CSV into this table via LOAD INFILE statement inside MySQL, i.e.

LOAD DATA INFILE '~/phrases.csv' INTO TABLE tmp_phrases FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"';

After that you can split your data with two INSERT statements.

INSERT INTO phrase_library_group (GROUP_NAME)

    SELECT DISTINCT GROUP_NAME FROM tmp_phrases

INSERT INTO phrase_list (PHRASE_NAME,PHRASE_DESC,GROUP_ID)

    SELECT x.PHRASE_NAME,x.PHRASE_DESC,y.GROUP_ID

    FROM tmp_phrases x

    INNER JOIN phrase_library_group y ON x.GROUP_NAME=y.GROUP_NAME
...