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