How to insert EXCEL data sheet into MYSQL database using PHP(xls,xlsx,csv)
We gonna insert EXCEL data Sheet (xls,xlsx,csv) into a MYSQL database using PHP
Exemple:
a) First :
We gonna use two columns : 1) name 2) email in our excel sheet:
b) Second: We gonna create three columns(excel_id , excel_name , excel_email) in our MYSQL database :
Why excel_id? : because we need a unique key to identifer every fields in the table
c) Thrid: This is the PHP script that will allow us to take the data from the EXCEL sheet, means: name and email column and put it into the MYSQL database.
COPY AND PASTE this code into your console(NOTEPAD ++)
<?php
$connect = mysqli_connect("localhost", "root", "", "test");
$output = '';
if(isset($_POST["import"]))
{
$extension = end(explode(".", $_FILES["excel"]["name"])); // For getting Extension of selected file
$allowed_extension = array("xls", "xlsx", "csv"); //allowed extension
if(in_array($extension, $allowed_extension)) //check selected file extension is present in allowed extension array
{
$file = $_FILES["excel"]["tmp_name"]; // getting temporary source of excel file
include("PHPExcel/IOFactory.php"); // Add PHPExcel Library in this code
$objPHPExcel = PHPExcel_IOFactory::load($file); // create object of PHPExcel library by using load() method and in load method define path of selected file
$output .= "<label class='text-success'>Data Inserted</label><br /><table class='table table-bordered'>";
foreach ($objPHPExcel->getWorksheetIterator() as $worksheet)
{
$highestRow = $worksheet->getHighestRow();
for($row=2; $row<=$highestRow; $row++)
{
$output .= "<tr>";
$name = mysqli_real_escape_string($connect, $worksheet->getCellByColumnAndRow(0, $row)->getValue());
$email = mysqli_real_escape_string($connect, $worksheet->getCellByColumnAndRow(1, $row)->getValue());
$query = "INSERT INTO tbl_excel(excel_name, excel_email) VALUES ('".$name."', '".$email."')"; // gere l'insertion
$queryUpdate="UPDATE tbl_excel set excel_name='$name', excel_email='$email' WHERE excel_name='$name' and excel_email='$email' "; // Gere la mise a jour pour ne pas avoir de doublon
mysqli_query($connect, $query);
mysqli_query($connect, $queryUpdate);
$output .= '<td>'.$name.'</td>';
$output .= '<td>'.$email.'</td>';
$output .= '</tr>';
}
}
$output .= '</table>';
}
else
{
$output = '<label class="text-danger">Invalid File</label>'; //if non excel file then
}
}
?>
<html>
<head>
<title>Import Excel to Mysql using PHPExcel in PHP</title>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.3/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
<link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" rel="stylesheet" />
<style>
body
{
margin:0;
padding:0;
background-color:#f1f1f1;
}
.box
{
width:700px;
border:1px solid #ccc;
background-color:#fff;
border-radius:5px;
margin-top:100px;
}
</style>
</head>
<body>
<div class="container box">
<h3 align="center">Import Excel to Mysql using PHPExcel in PHP</h3><br />
<form method="post" enctype="multipart/form-data">
<label>Select Excel File</label>
<input type="file" name="excel" />
<br />
<input type="submit" name="import" class="btn btn-info" value="Import" />
</form>
<br />
<br />
<?php
echo $output;
?>
</div>
</body>
</html>
STOP Here, and read the logic:
What we'll happen is :
We gonna populate our EXCEL SHEET means the fields: name and email .
SAVE the documents and execute thet PHP scripts,
that will save our data into a MYSQL database.
Wanna contact me?
Exemple:
a) First :
We gonna use two columns : 1) name 2) email in our excel sheet:
b) Second: We gonna create three columns(excel_id , excel_name , excel_email) in our MYSQL database :
Why excel_id? : because we need a unique key to identifer every fields in the table
COPY AND PASTE this code into your console(NOTEPAD ++)
- URGENT: replace root with your user, replace test with your database's name, put your password between root and test if you have one.
<?php
$connect = mysqli_connect("localhost", "root", "", "test");
$output = '';
if(isset($_POST["import"]))
{
$extension = end(explode(".", $_FILES["excel"]["name"])); // For getting Extension of selected file
$allowed_extension = array("xls", "xlsx", "csv"); //allowed extension
if(in_array($extension, $allowed_extension)) //check selected file extension is present in allowed extension array
{
$file = $_FILES["excel"]["tmp_name"]; // getting temporary source of excel file
include("PHPExcel/IOFactory.php"); // Add PHPExcel Library in this code
$objPHPExcel = PHPExcel_IOFactory::load($file); // create object of PHPExcel library by using load() method and in load method define path of selected file
$output .= "<label class='text-success'>Data Inserted</label><br /><table class='table table-bordered'>";
foreach ($objPHPExcel->getWorksheetIterator() as $worksheet)
{
$highestRow = $worksheet->getHighestRow();
for($row=2; $row<=$highestRow; $row++)
{
$output .= "<tr>";
$name = mysqli_real_escape_string($connect, $worksheet->getCellByColumnAndRow(0, $row)->getValue());
$email = mysqli_real_escape_string($connect, $worksheet->getCellByColumnAndRow(1, $row)->getValue());
$query = "INSERT INTO tbl_excel(excel_name, excel_email) VALUES ('".$name."', '".$email."')"; // gere l'insertion
$queryUpdate="UPDATE tbl_excel set excel_name='$name', excel_email='$email' WHERE excel_name='$name' and excel_email='$email' "; // Gere la mise a jour pour ne pas avoir de doublon
mysqli_query($connect, $query);
mysqli_query($connect, $queryUpdate);
$output .= '<td>'.$name.'</td>';
$output .= '<td>'.$email.'</td>';
$output .= '</tr>';
}
}
$output .= '</table>';
}
else
{
$output = '<label class="text-danger">Invalid File</label>'; //if non excel file then
}
}
?>
<html>
<head>
<title>Import Excel to Mysql using PHPExcel in PHP</title>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.3/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
<link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" rel="stylesheet" />
<style>
body
{
margin:0;
padding:0;
background-color:#f1f1f1;
}
.box
{
width:700px;
border:1px solid #ccc;
background-color:#fff;
border-radius:5px;
margin-top:100px;
}
</style>
</head>
<body>
<div class="container box">
<h3 align="center">Import Excel to Mysql using PHPExcel in PHP</h3><br />
<form method="post" enctype="multipart/form-data">
<label>Select Excel File</label>
<input type="file" name="excel" />
<br />
<input type="submit" name="import" class="btn btn-info" value="Import" />
</form>
<br />
<br />
<?php
echo $output;
?>
</div>
</body>
</html>
What this code above does?
This code takes your Excel sheet column data which is name and email and it INSERT the data into a MYSQL database , it also UPDATE the data in the database for example if you change value of columns in the Excel sheet.
STOP Here, and read the logic:
What we'll happen is :
We gonna populate our EXCEL SHEET means the fields: name and email .
SAVE the documents and execute thet PHP scripts,
that will save our data into a MYSQL database.
Watch the video to see what happen:
Download this Android Mobile my contacts ( email and Whatsapp number ) are in it
Clic here to downloads: https://play.google.com/store/apps/details?id=jbej90.automation.test.haitiangeek
Commentaires
Enregistrer un commentaire