import CSV data to MYSQL

Question

I am building an expert system with naive bayes method. I have a few table : rule, choice and diagnosis Rule:

-----------------------------------
|id|id_rule|id_diagnosis|id_choice|
-----------------------------------
|1 |      1|           1|        1|
|2 |      1|           2|        3|
|3 |      1|           3|        5|
|4 |      1|           4|        7|
|5 |      2|           1|        2|
|6 |      2|           2|        4|
|7 |      2|           3|        6|
|8 |      2|           4|        7|
-----------------------------------

Diagnosis

------------------------
|id_diagnosis|diagnosis|
------------------------
|           1|      AAA|
|           2|      BBB|
|           3|      CCC|
|           4|      DDD|
------------------------

Choice

-------------------------------
|id_choice|id_diagnosis|choice|
-------------------------------
|        1|           1|    aa|
|        2|           1|    bb|
|        3|           2|    cc|
|        4|           2|    dd|
|        5|           3|    ee|
|        6|           3|    ff|
|        7|           4|    gg|
|        8|           4|    hh|
-------------------------------

Within user interface, I can manually input the parameter (add rule) using select option form. With the name of option is id_choice and id_diagnosis. But I have many data that I need to calculate to this system. I have the data in csv format, the same format as the output of rule list ini my system like this:

----------------------
|rule|AAA|BBB|CCC|DDD|
----------------------
|1   |aa |cc |ee |gg | 
|2   |bb |dd |ff |gg |
----------------------

But the problem is, I don't know how to add the data from csv format to database since in the table rule it only record the id, not the name of choice and diagnosis itself. I know that we can convert csv to multidimensional arrat, but I still confuse how to convert from name to id without doing it manually. Thanks

Hitesh Vala Ahir · Accepted Answer · 2015-05-22 13:40:44Z

2

here is command:

    LOAD DATA INFILE 'c:/country.csv' 
    INTO TABLE *table_name*
    FIELDS TERMINATED BY ',' 
    ENCLOSED BY '"'
    LINES TERMINATED BY '\n'
    IGNORE 1 ROWS

Keep one thing in mind, never appear , in second column, otherwise your import will stop

answered May 22, 2015 at 13:40

Hitesh Vala Ahir

8032 gold badges14 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Safira Adiputri Over a year ago

i know how to do that. But i don't know how to convert between the input data and desired structure. please see my example

Angga Lisdiyanto · Accepted Answer · 2015-05-22 13:54:33Z

0

Selamat malam.

You must import these data from one by one table which include a Primary key, then import data from tables which include a Foreign key

Except you want a backup/restore database, you must import all table of database.

answered May 22, 2015 at 13:54

Angga Lisdiyanto

599 bronze badges

4 Comments

Safira Adiputri Over a year ago

can you explain the detail? the problem here is because the different structure between input data (csv) and mysql table. And i need to import the data from csv to avoid input manual one by one

Angga Lisdiyanto Over a year ago

Oh i understand now, so it is about the begining. What sistem you use (phpmyadmin, SQL Server Management Studio)?

Safira Adiputri Over a year ago

I use phpmyadmin. the difference is the structure table not the file itself (SQL and CSV). Please read my question in detail

Angga Lisdiyanto Over a year ago

Well, i told you to import a data from table with foreign key included.

Collectives™ on Stack Overflow

import CSV data to MYSQL

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related