Lets say, i want to import/upload excel file to mysql from PHP
My HTML is like below
<form enctype="multipart/form-data" method="post" role="form"> <div class="form-group"> <label for="exampleInputFile">File Upload</label> <input type="file" name="file" id="file" size="150"> <p class="help-block">Only Excel/CSV File Import.</p> </div> <button type="submit" class="btn btn-default" name="Import" value="Import">Upload</button> </form>PHP code is like below
<?php if(isset($_POST["Import"])) { //First we need to make a connection with the database $host='localhost'; // Host Name. $db_user= 'root'; //User Name $db_password= ''; $db= 'product_record'; // Database Name. $conn=mysql_connect($host,$db_user,$db_password) or die (mysql_error()); mysql_select_db($db) or die (mysql_error()); echo $filename=$_FILES["file"]["tmp_name"]; if($_FILES["file"]["size"] > 0) { $file = fopen($filename, "r"); //$sql_data = "SELECT * FROM prod_list_1 "; while (($emapData = fgetcsv($file, 10000, ",")) !== FALSE) { //print_r($emapData); //exit(); $sql = "INSERT into prod_list_1(p_bench,p_name,p_price,p_reason) values ('$emapData[0]','$emapData[1]','$emapData[2]','$emapData[3]')"; mysql_query($sql); } fclose($file); echo 'CSV File has been successfully Imported'; header('Location: index.php'); } else echo 'Invalid File:Please Upload CSV File'; } ?>Excel file is like below
I want to import only the 2nd row values into my table. Please help me how can i solve it or give me any resource. I was able to upload the excel file but it is not correctly formatted. i want to upload 4 column in mysql's 4 column but it is uploading all 4 column in mysql's 1 column. am i missing something ?
How to insert / import excel data into mysql database in php
In this post, you will be learning how to import excel file into mysql database using PHP.
Where we are going to see full example of csv file, xls file, xlsx file import into database using phpspreadsheet package. Click to view documentation
So, let's begin with import excel data into database in php by the following few steps.
Step 1: Download/Install the Package (phpSpreadSheet - Click here ):
Let's install via composer, open your terminal from your application root directory (Demo in video):
HP@HP-PC MINGW64/c/xampp/htdocs/php-excel
$ composer require phpoffice/phpspreadsheet
After successful installation of this package, you will find few files in your application like vendor, composer.json, etc.
Step 2: Create Database Connection in dbconfig.php file:
<?php
$host = "localhost";
$username = "your_username";
$password = "your_password";
$database = "your_database_name";
// Create DB Connection
$conn = mysqli_connect($host, $username, $password, $database);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";
?>
Step 3: Create a form in index.php as given below:
<?php session_start(); ?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>How to Import Excel Data into database in PHP</title>
<link href="//cdn.jsdelivr.net/npm//dist/css/bootstrap.min.css" rel="stylesheet">
</head>
<body>
<div class="container">
<div class="row">
<div class="col-md-12 mt-4">
<?php
if(isset($_SESSION['message']))
{
echo "<h4>".$_SESSION['message']."</h4>";
unset($_SESSION['message']);
}
?>
<div class="card">
<div class="card-header">
<h4>How to Import Excel Data into database in PHP</h4>
</div>
<div class="card-body">
<form action="code.php" method="POST" enctype="multipart/form-data">
<input type="file" name="import_file" class="form-control" />
<button type="submit" name="save_excel_data" class="btn btn-primary mt-3">Import</button>
</form>
</div>
</div>
</div>
</div>
</div>
<script src="//cdn.jsdelivr.net/npm//dist/js/bootstrap.bundle.min.js"></script>
</body>
</html>
After successfully creating the form for import excel, then let's click on the submit button which takes FORM using POST method to code.php and insert or import excel data into database over there.
Step 4: Create a code.php file and paste the below code:
In this code.php file, we are wrting the code of import excel file data into mysql database in php. where we are checking the Valid file upload and redirect with the message using session.
<?php
session_start();
include('dbconfig.php');
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
if(isset($_POST['save_excel_data']))
{
$fileName = $_FILES['import_file']['name'];
$file_ext = pathinfo($fileName, PATHINFO_EXTENSION);
$allowed_ext = ['xls','csv','xlsx'];
if(in_array($file_ext, $allowed_ext))
{
$inputFileNamePath = $_FILES['import_file']['tmp_name'];
$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($inputFileNamePath);
$data = $spreadsheet->getActiveSheet()->toArray();
$count = "0";
foreach($data as $row)
{
if($count > 0)
{
$fullname = $row['0'];
$email = $row['1'];
$phone = $row['2'];
$course = $row['3'];
$studentQuery = "INSERTINTO students (fullname,email,phone,course) VALUES ('$fullname','$email','$phone','$course')";
$result = mysqli_query($con, $studentQuery);
$msg = true;
}
else
{
$count = "1";
}
}
if(isset($msg))
{
$_SESSION['message'] = "Successfully Imported";
header('Location: index.php');
exit(0);
}
else
{
$_SESSION['message'] = "Not Imported";
header('Location: index.php');
exit(0);
}
}
else
{
$_SESSION['message'] = "Invalid File";
header('Location: index.php');
exit(0);
}
}
?>
Thank you.