in Education by
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
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

Related questions

0 votes
    Is there an easy way to run a MySQL query from the Linux command line and output the results in CSV format? Here's what I'm doing now: mysql -u uid -ppwd -D dbname...
asked Mar 11, 2022 in Education by JackTerrance
0 votes
    COPY TO exports data from a csv into table (1)False (2)True...
asked Apr 17, 2021 in Technology by JackTerrance
0 votes
    My goal here is to delete the correspondent data in mysql db table using AJAX. So I've generated a table with several rows and want to a non specific row... PHP Code:...
asked Mar 2, 2022 in Education by JackTerrance
0 votes
    I'm trying to write an Azure table Storage Query result to a .csv file and store it locally on ... JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Jun 19, 2022 in Education by JackTerrance
0 votes
    Unlike writing out a table or CSV file, dump() and dput() preserve the ______ so that another user ... Operations of R Programming Select the correct answer from above options...
asked Feb 13, 2022 in Education by JackTerrance
0 votes
    I need to take the users input from the select box for Allergen1 once an option is selected and the ... Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Apr 16, 2022 in Education by JackTerrance
0 votes
    I've come to this code, but from this i have to manually insert all columns and check it by each ... JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Apr 16, 2022 in Education by JackTerrance
0 votes
    I have a very large MySQL table (billions of rows, with dozens of columns) I would like to ... JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Feb 18, 2022 in Education by JackTerrance
0 votes
    What is heap table and other name of these table in MySQL?...
asked Nov 30, 2020 in Technology by JackTerrance
0 votes
    When you upload a CSV file through UI or External Data API, it is recommended to provide the metadata in the format of __________. A. CSV B. JSON C. XML...
asked Nov 1, 2022 in Education by JackTerrance
0 votes
    I am a beginner and I just need a bit of help on why I getline is showing an error: this is what I have so far ... payments[MAX_ITEMS]; ifstream iFile; if ( argc != 2 ) { cout...
asked Apr 7, 2022 in Education by JackTerrance
0 votes
    I'm using beautifulsoup and selenium to scrape some data in python. Here is my code which I run ... JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Apr 7, 2022 in Education by JackTerrance
0 votes
    I'm using beautifulsoup and selenium to scrape some data in python. Here is my code which I run ... JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Apr 7, 2022 in Education by JackTerrance
0 votes
    I am a beginner and I just need a bit of help on why I getline is showing an error: this is what I have so far ... payments[MAX_ITEMS]; ifstream iFile; if ( argc != 2 ) { cout...
asked Apr 7, 2022 in Education by JackTerrance
0 votes
    Simple select stored procedure but getting only one column as the result others were getting as null. Can ... Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Feb 7, 2022 in Education by JackTerrance
...