Import Excel into MySql Database Without Plugin

Import Excel into MySql Database Without Plugin

Dec 25, 2018 / PHP / MySQL /

This HTML form with the file upload option is used to choose the excel source. On submitting this form, the excel file will be sent to the PHP to parse the data source. This file upload option will only allow the excel files to choose by using the accept attribute. This code also contains the response HTML for displaying the message returned from PHP. This message is shown based on the type of response sent from PHP after excel import.

HTML Code: form.html

Choose CSV File to Import Data

 


<html>
 <head>
    
 </head>
 <body>
   <form action="import.php" method="post" enctype="multipart/form-data">
   <div>
    <label>Import CSV File:</label>
    <input type="file"  name="filename" id="filename">
    <button type="submit" id="submit" name="submit">Upload</button>
   </div>
  </form>
 <body>
</html>

In this PHP code, I specified the array of allowed file type and check the uploaded file type. The excel file is uploaded to a target and its data are parsed using reader functions. It computes the number of sheets and runs a loop to parse data sheet by sheet. For each sheet iteration, I have created a nested loop for parsing data row by row. After reading the non-empty row data, I run the database insert and show the response. 

PHP Code: import.php

 


$servername = "localhost";
$username = "root";
$password = "";
$dbname = "test";

$conn = mysqli_connect($servername, $username, $password, $dbname);

if (isset($_POST['submit'])) 
{
 //Import uploaded file to Database
 $handle = fopen($_FILES['filename']['tmp_name'], "r");
 while(($data = fgetcsv($handle)) !== FALSE){
 $sql = "INSERT into testtbl (user_name, first_name, last_name, date_added)
  values('".$data[0]."', '".$data[1]."', '".$data[2]."', '".$date."')";
 
  mysqli_query($conn, $sql) ;
}
}

Comments

Chandana ( Jul 8, 2019 08:37:24 )

Many thanks for this useful guide..

hom savalia ( Jun 28, 2019 11:14:10 )

Notice: Undefined offset: 1 and data is not proper forment insert in database

hom savalia ( Jun 28, 2019 11:14:06 )

Notice: Undefined offset: 1 and data is not proper forment insert in database

Would you like to share your thoughts?

Your email address will not be published. Required field are marked*

Copyright 2018. All rights are reserved Significant Techno Web Design & Web Development By Significant Techno