Import Excel data in MySQL Table after few manipulation

[vishalonne]

Hi Everybody

I have to import excel data in MySQL table, I know how to do this for simple excel data to 1 MySQL table. Here also I need to import data from excel to 1 table but data is provided in 1 form which needs to be converted first and then import, this is where I am trapped.
Excel File - studentsmarks.xls -

Exam_Name     **Student_Registation_No  Marks_Obtained**    Subject
HALF YEARLY   201                       51                  ENGLISH
HALF YEARLY   201                       67                  ECONOMICS
HALF YEARLY   201                       68                  HISTORY
HALF YEARLY   202                       45                  ENGLISH
HALF YEARLY   202                       64                  ECONOMICS
HALF YEARLY   202                       59                  HISTORY

I only need to import the highlighted columns which is required for es_mark table
MySQL Table:
Table No 1. es_mark-

es_marksid    es_examdetailsid     es_marksstudentid    es_marksobtined     status
    1             1                   201                   51                  active
    2             2                   201                   67                  active
    3             3                   201                   68                  active
    4             1                   202                   45                  active
    5             2                   202                   64                  active
    6             3                   202                   59                  active  (Student is active or not)

    es_marksid int(11)
    **es_examdetailsid int(11)**
    es_marksstudentid int(11)
    es_marksobtined varchar(255) 0->Default Value
    status  enum('active', 'inactive') active->Default Value

Table No 2. es_exam_details-

es_exam_detailsid  academicexam_id  subject_id  exam_date            exam_duration  total_marks  pass_marks
1                  3                76          2013-12-16 00:00:00  3:00           70           30
2                  3                77          2013-12-21 00:00:00  3:00           70           30
3                  3                78          2013-12-18 00:00:00  3:00           70           30
4                  4                76          2013-12-16 00:00:00  3:00           70           30
5                  4                77          2013-12-21 00:00:00  3:00           70           30
6                  4                78          2013-12-18 00:00:00  3:00           70           30

**s_exam_detailsid int(11)**
**academicexam_id  int(11)**     (3 means FINAL, 4 means HALF YEARLY)
subject_id       int(11)     (76 means English, 77 means Economics, 78 means Geography)
exam_date        datetime
exam_duration    varchar(255)
total_marks      int(11)
pass_marks       int(11)

Table No 3. es_exam-

es_examid  es_examname  es_examordby
3          FINAL        1
4          HALF YEARLY  1

**es_examid int(11)**
es_examname varchar(255)
es_examordby int(11)

Now my problem is user provide me data in excel file as shown in above example. How can I import this to es_mark. I am not able to find the way to convert the Exam_Name with their respective ID as shown in table. I already done a lot of things with these table. Means marks can be entered subject wise and student wise for each students individually.

I need a guidance how can do this? I will be greatful for any suggestion/guidance/solution.
Regards

[damle1]

Try Excel to Mysql freeware by Panofish

[rch1231]

Excel will save files as a .CSV (Comma Separated Values) and MySQL will import a .CSV easily. I would recommend that you first import your data files then update other tables using the tables you imported. Normally I drop the tables and re-create them prior to each import to prevent problems.

You can either use a MySQL LOAD statement or the mysqlimport application which calls the LOAD statement from MySQL.

[vishalonne]

@rch1231 I think you didnot got my question. Data will be imported to only 1 table and no other table need to update or modify.
Istead of this As I have shown in my example Excel data is in text form say EXAM NAME -> HALF YEARLY and Subject -> ENGLISH user will give me this. My question is how can I use this text to fetch the record from Table No 2. es_exam_details because both the information are stored in this table in columns academicexam_id and subject_id.

[João_5]

can you make a link to a excel example?