Scribd
Upload
112 views2 pages

Excel File Upload and Data Import

This document describes uploading an Excel file and inserting its data into a database. It includes: 1) An HTML form to upload an Excel file. 2) PHP code to load the Excel file using PHPExcel, extract the data into an array, and insert it into database tables using MySQL queries. 3) Loops through the data array to generate INSERT queries to add the rows to login and user_master tables, and handles errors or confirmation messages.

Uploaded by

muttuswami
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
112 views2 pages

Excel File Upload and Data Import

This document describes uploading an Excel file and inserting its data into a database. It includes: 1) An HTML form to upload an Excel file. 2) PHP code to load the Excel file using PHPExcel, extract the data into an array, and insert it into database tables using MySQL queries. 3) Loops through the data array to generate INSERT queries to add the rows to login and user_master tables, and handles errors or confirmation messages.

Uploaded by

muttuswami
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

<div class="content-wrapper">

<section class="content-header">
<form method="post" action="" enctype="multipart/form-data">
Upload Excel File : <input type="file" name="fileexcel" /><br />
<input type="submit" name="submit" value="Upload" />
</form>
</section>
</div>

<?php
require_once 'PHPExcel.php';

error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
date_default_timezone_set('Asia/Kolkata');

if (PHP_SAPI == 'cli')
die('This example should only be run from a Web Browser');

//$con = mysqli_connect('localhost','root','','samplemipre');
if(isset($_POST["submit"]))
{

$excelfile=$_FILES["fileexcel"]["tmp_name"];
$objPHPExcel = PHPExcel_IOFactory::load($excelfile);
$dataArr = array();

foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {


$worksheetTitle = $worksheet->getTitle();
$highestRow = $worksheet->getHighestRow(); // e.g.
10
$highestColumn = $worksheet->getHighestColumn(); //
e.g 'F'
$highestColumnIndex =
PHPExcel_Cell::columnIndexFromString($highestColumn);

for ($row = 1; $row <= $highestRow; ++ $row) {


for ($col = 0; $col < $highestColumnIndex; ++ $col) {
$cell = $worksheet->getCellByColumnAndRow($col,
$row);
$val = $cell->getValue();
$dataArr[$row][$col] = $val;
}
}
}
unset($dataArr[1]); // first row is the header and not the actual
data

$query1="";
$query2="";

foreach($dataArr as $val){

//INSERT INTO my_model (status, name) select('active',


'bbc') from my_model
//WHERE NOT EXISTS (SELECT name FROM my_model WHERE name = 'bbc');
//$query1="INSERT INTO `login`(`user_id`,
`password`, `user_type`, `status`,`otp`,`firstcheck`) VALUES ('".
$val['0']."','123456',2,1,0,0)";

$result=mysqli_query($connect,$query1);
$login_id=mysqli_insert_id($connect);

if($result)
{
$query2="INSERT INTO `user_master`
(`username`,`res_address`,`mobile`,`login_id`,`image`,`gender`,`country_id`,`zone_i
d`,`center_id`,`user_status`,`title`)VALUES( '".
$val['1']."','".mysqli_real_escape_string($connect,$val['4'])."','".$val['5']."',".
$login_id.",'".$val['7'].".jpg"."',".
$val['8'].",'".trim($val['10'])."','".trim($val['11'])."','".trim($val['12'])."','"
.$val['15']."','".$val['16']."');";

$insertdata=mysqli_query($connect,
$query2);
if($insertdata)
{
echo "<br>$insertdata=data inserted
successfully";
}
else
{
echo
"<br>$insertdata=".mysqli_error($connect);
}

}
else
{
echo
"<br>result=".mysqli_error($connect);
}

?>

You might also like