Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to Import Excel file into mysql Database from PHP

Tags:

php

mysql

excel

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

excel file picture

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 ?

like image 646
user3342646 Avatar asked Feb 23 '14 07:02

user3342646


People also ask

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 import PHP file into phpMyAdmin using Excel?

php use Phppot\DataSource; use PhpOffice\PhpSpreadsheet\Reader\Xlsx; require_once 'DataSource. php'; $db = new DataSource(); $conn = $db->getConnection(); require_once ('./vendor/autoload. php'); if (isset($_POST["import"])) { $allowedFileType = [ 'application/vnd. ms-excel', 'text/xls', 'text/xlsx', 'application/vnd.

How do you import images from Excel sheet to database in PHP?

php //excel sheet data insert $conn = mysqli_connect("localhost","root","","hep"); require_once('C:\xampp\phpMyAdmin\vendor\php-excel-reader\excel_reader2. php'); require_once('C:\xampp\phpMyAdmin\vendor\SpreadsheetReader. php'); if (isset($_POST["import"])){ $allowedFileType = ['application/vnd.

How connect Excel to PHP?

Establish a ConnectionOpen 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.


2 Answers

For >= 2nd row values insert into table-

$file = fopen($filename, "r");
//$sql_data = "SELECT * FROM prod_list_1 ";

$count = 0;                                         // add this line
while (($emapData = fgetcsv($file, 10000, ",")) !== FALSE)
{
    //print_r($emapData);
    //exit();
    $count++;                                      // add this line

    if($count>1){                                  // add this line
      $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);
    }                                              // add this line
}
like image 185
Atanu Saha Avatar answered Sep 21 '22 03:09

Atanu Saha


You are probably having a problem with the sort of CSV file that you have.

Open the CSV file with a text editor, check that all the separations are done with the comma, and not semicolon and try the script again. It should work fine.

like image 43
Victor del Ama Avatar answered Sep 20 '22 03:09

Victor del Ama