How to Transpose Excel Data for MySQL database? (PHP script needed)

I have an excel file, with columns titled with the class ID and followed by student IDs for students in the class. See image for detailed reference. EX:

ART VIS D1 | ART VIS D2

STU000123 | STU000234

etc...

Which I want to then, somehow, parse and insert into the DB. Inside my database, I have 3 tables specific to classes. One is called Class. It contains a unique ID (1,2,3, etc) and the ClassID (ex: ART VIS D1).

The other is called Students, which contains the STU IDs (seen in the first table...STU000123 etc), an ID (1,2,3,4 etc), and other personal information. The ID field is what's used to identify the students.

The third table is called student_to_class, essentially matches the ID of the student with the ID of the class they are assigned to. Structure: id (auto inc),student_id,class_id

Now, what I'm trying to make is a script that will take the Student IDs in the first table, along with the appropriate class, get the class ID and student ID from the Class and Students tables, and then insert those IDs into student_to_class so as to assign them to that class.

Here is a link to the images of all mentioned files/structures: http://img209.imageshack.us/g/excelz.png/

I'd appreciate any help!

Consider saving them as tab-delimited, and then use the LOAD DATA INFILE command in MySQL.

If you do not have access to the server directly, then save them as tab-delimted, and use the file() function in PHP to load and parse the files into lines, and then explode("\t", ...) to explode each row into columns.

If this is something you want to access from a browser repeatedly, then you can upload the files to PHP from the browser, and use the above technique to parse them.

Also, as Marc B pointed out above, http://phpexcel.codeplex.com is a library that may remove the need to save as tab-delimited.

Good luck!

Get Toad for MySQL (http://www.quest.com/toad-for-mysql/) and they have a wizard to import excel files and they don't have to be tab delimited.

Maybe you can use only the bash... a simple example:

cat file.txt | awk -F "|" {'print $FIRST_COLUMN $SECOND_COLUMN'})

And implements your own logic, AWK is a very powerful language.