I want to transfer the excel's content into my database but how would I do that?

• 
   
  
• Save your Excel sheet as a CSV file.
  
• Create your SQL database using PHPAdmin
  
• Import your CSV file into your SQL DB
  

 

See this video which explains the process

 

http://www.youtube.com/watch?v=diEwQk4uY14

Thanks! It was really helpful but is there a way that doesn't require me to go to the cPanel?

 

Like it'll be on the back-end of the site and the admin would just have to select the .csv? And everytime the admin uploads a new .csv (update on student record) it'll overwrite the previous record

start with something like this and build on it?

http://www.legend.ws/blog/tips-tricks/csv-php-mysql-import/

Thanks but what I mean is without going into phpmyadmin.. Just a button that asks for the .cvs then it'll transfer the content into the database. I think it uses that "explode" thing

Hi there, Simply create the database within PHPmyAdmin once complete create a user for that database with the privileges you require (normally all). Then create a form that allows the user to upload the file to a destination on the server, rename it to something specific during the process.

 

Then proceed to open the file with PHP, establish a connection to the database and read value by value into the database.

 

Be aware however you would need to validate the input, and ensure or mange user access to the script for security purposes.

 

If possible add a link to an example CSV file in the format you utalise and describe your database table structure, that way it is possible to provide acurate code examples

Edited December 27, 201015 yr by CSN-UK

Here's a screenshot of the excel file and the database and the database structure would be like

 

ID,StudentNumber,StudentName,Subject,Period,DaysAbsent(int),Grade(int) and the rest are just text

 

The primary key would be the ID and it'll be just one big table for all of the student's record.

 

Just want to add something. The updates would only be once per semester and what I wanted is that everytime the admin updates the table it'll overwrite the previous record.

 

Really appreciate it everyone's help.. Our thesis defense would be like a month from now and we're kinda on a rush..

 

P.S.

 

It's for an online student information system.. I've searched around the web for samples and it's like they're doing it locally..

Edited December 29, 201015 yr by jhamthehamster

http://www.php.net/manual/en/function.fgetcsv.php

 

<?php
if (($handle = fopen("students.csv", "r")) !== FALSE) {
   while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
       $sql = 'REPLACE INTO `table` (`col1`, `col2`, `col3`) VALUES (\''.$data[0].'\', \''.$data[1].'\', \''.$data[2].'\')'; //etc.
       //execute query or continue to build a multi-row replace then execute later
   }
   fclose($handle);
}
?>

Thanks done it ^^

 

Just noticed that when I export it to .csv it shows like this

 

"Doe, John",04021351,BS101A

 

How can I remove the double quote at the beginning and ending of the name and also make it ignore the , in the name? Cause my record would look like this

 

John" 04021351 BS101A

 

Edited :

Edited January 11, 201115 yr by jhamthehamster

Got it

 

What I've used last time was split, explode then implode and it was kinda complicated for me then I've tried fgetcsv and everything worked fine.

 

Thanks for the help everyone