Import excel to mysql with php

Question

Lets say, i want to import/upload excel file to mysql from PHP

Table of Contents Show

How to insert / import excel data into mysql database in php
How do I import Excel data into MySQL using PHP?
How do I import an Excel file into MySQL?
How connect Excel to PHP?
How read Excel file and insert into database in 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>

<head>

<title>How to Import Excel Data into database in PHP</title>

</head>

<body>

<?php

if(isset($_SESSION['message']))

{

echo "<h4>".$_SESSION['message']."</h4>";

unset($_SESSION['message']);

}

?>

<h4>How to Import Excel Data into database in PHP</h4>

</div>

<button type="submit" name="save_excel_data" class="btn btn-primary mt-3">Import</button>

</form>

</div>

</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.

How do I import Excel data into MySQL using PHP?

PHP - import excel file into mysql database tutorial.

1.Download Package..

2.Create db_config.php file..

3.Create index.php file..

4.Create excelUpload.php..

5.Create Upload Folder..

Step 1: Download Package..

Step 2: Create db_config.php file..

db_config.php..

How do I import an Excel file into MySQL?

Step 1: Click on the Browse button and select the Excel file you want to import to MySQL. Step 2: Select MySQL as your desired database. According to your excel file, check or uncheck My File has a Header Row. Step 3: Based on your Excel file, check Use CHECK IF TABLE EXISTS.

How connect Excel to PHP?

Establish a Connection Open the connection to Excel by calling the odbc_connect or odbc_pconnect methods. To close connections, use odbc_close or odbc_close_all. $conn = odbc_connect("CData ODBC Excel Source","user","password"); Connections opened with odbc_connect are closed when the script ends.