Import Excel Data into MySQL Database using PHP

Question

I have a website. The website shows a schedule of school.

Every day might have some changes, for example: John calls and informs he is sick so he can't arrive tomorrow. His students should have a substitute teacher.

So, I have an email address that should get a mail containing the Excel file of the changes. and what I want is create a PHP code that take the file from the eMail and import it to SQL Database, and show it in the website. of course - it's should be done automatically and done on a daily basis.

If you have a better idea or a better way for how to "code" the site - I will be happy to hear it.

BTW I found a little piece of code in PHP from Webslesson

Database Table

CREATE TABLE IF NOT EXISTS `tbl_excel` (
  `excel_id`    INT(11)      NOT NULL AUTO_INCREMENT,
  `excel_name`  VARCHAR(250) NOT NULL,
  `excel_email` VARCHAR(300) NOT NULL,
  PRIMARY KEY (`excel_id`)
)
  ENGINE = MyISAM
  DEFAULT CHARSET = latin1
  AUTO_INCREMENT = 1;

index.php

 <?php  
 $connect = mysqli_connect("localhost", "root", "", "test_db");  
 include ("PHPExcel/IOFactory.php");  
 $html="<table border='1'>";  
 $objPHPExcel = PHPExcel_IOFactory::load('example.xls');  
 foreach ($objPHPExcel->getWorksheetIterator() as $worksheet)   
 {  
      $highestRow = $worksheet->getHighestRow();  
      for ($row=2; $row<=$highestRow; $row++)  
      {  
           $html.="<tr>";  
           $name = mysqli_real_escape_string($connect, $worksheet->getCellByColumnAndRow(0, $row)->getValue());  
           $email = mysqli_real_escape_string($connect, $worksheet->getCellByColumnAndRow(1, $row)->getValue());  
           $sql = "INSERT INTO tbl_excel(excel_name, excel_email) VALUES ('".$name."', '".$email."')";  
           mysqli_query($connect, $sql);  
           $html.= '<td>'.$name.'</td>';  
           $html .= '<td>'.$email.'</td>';  
           $html .= "</tr>";  
      }  
 }  
 $html .= '</table>';  
 echo $html;  
 echo '<br />Data Inserted';  
 ?>

Change the title, tell to us what have you tried so far, have you tried the code? what error are you getting? please review your question and provide to us a proper description. — gon250
– gon250, Commented Oct 6, 2016 at 9:21
I haven't try something because I don't have any idea how to do it.. — 0xCFkZr47
– 0xCFkZr47, Commented Oct 6, 2016 at 11:22

pawan sen · Accepted Answer · 2016-10-06 09:24:58Z

4

this code working fine

 $file = "your-file.xls";
 $handle = fopen($file, "r");
 $c = 0;
 while(($filesop = fgetcsv($handle, 1000, ",")) !== false)
 {
 $name = $filesop[0];
 $email = $filesop[1];

 $sql = mysql_query("INSERT INTO xls (name, email) VALUES ('$name','$email')");
 }

 if($sql){
 echo "You database has imported successfully";
 }else{
 echo "Sorry! There is some problem.";
 }

Try this

answered Oct 6, 2016 at 9:24

pawan sen

7365 silver badges14 bronze badges

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

4 Comments

0xCFkZr47 Over a year ago

Yeah. But I wanna to take the file from the eMail.. the file needs to send from the email to the FTP server. That is my problem..

0xCFkZr47 Over a year ago

Ohh.. sorry for the dummy - but how can I do that?

pawan sen Over a year ago

specify your server location example localhost/project/folder/your-file.xls

0xCFkZr47 Over a year ago

Ok.. wiil try it later

Collectives™ on Stack Overflow

Import Excel Data into MySQL Database using PHP

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related