How to Import Excel file into mysql Database using PHP

[rjony321]

Dear,

I need help, how to import my Excel file into Mysql Database. I can successfully upload excel file but its not correctly formated uploaded or imported. please check this image,

My HTML Form looks like,

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

And my PHP code as like, I used PHP function fgetcsv()

<?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';

}
?>

My Excel file as like,

**Bench No | Product Name | Product Price | Reason**
------------------------------------------------
 CS-102  |   Baby Shop  |  12$          | Expired date Failed

I want only import 2nd row values into my table. Please help me how can i solve it or give me any resource.

Best Regards,

[jstfsklh211]

you cant use fgetcsv on xls files you would need to use something like PHPExcel

[gabrielcastillo]

Here is my version of csv uploading script. I user a library call parsecsvlib.php

include($_SERVER['DOCUMENT_ROOT'].'/public/lib/parsecsv.lib.php');

        $csv = new parsecsv();

        if(isset($_FILES['company_file'])){

            $file = $_FILES['company_file']['name'];
            $upload = $_SERVER['DOCUMENT_ROOT'] . '/uploads/';

            if(!file_exists($upload)){
                mkdir($upload);
            }


            $allowed = array('.csv');

            $ext = substr($file, strpos($file, '.'), strlen($file)-1);

            if(!in_array($ext, $allowed)){
                echo 'File type not excepted!';
                exit;
            }

            $file = str_replace(' ', '-', $file);
            $file = strtolower($file);

            if(move_uploaded_file($_FILES['company_file']['tmp_name'], $upload.$file)){
                $new_file = $upload.$file;

                $csv->heading = false;

                $csv->auto($new_file);

                $companies = array_slice($csv->data, 1);

                foreach($companies as $company){
                    $new_company = array(
                                    'location_number'   => trim($company[0]),
                                    'title'             => trim($company[1]),
                                    'address'           => trim($company[2]),
                                    'city'              => trim($company[3]),
                                    'state'             => trim($company[4]),
                                    'zip'               => trim($company[5]),
                                    'tel_1'             => trim($company[6]),
                                    'tel_2'             => '',
                                    'fax'               => trim($company[7]),
                                    'contact_1'         => trim($company[8]),
                                    'contact_2'         => trim($company[9]),
                                    'email'             => trim($company[10]),
                                    'volume'            => trim($company[11]),
                                    'drums'             => trim($company[12]),
                                    'notes'             => trim($company[13]),
                                    'date_created'      => date('Y-m-d H:i:s')
                                );

                    $insert = "INSERT SQL STATEMENT HERE";
                    mysql_query($insert);

                }
                echo 'File has been uploaded to database!';
                exit;
            }else{
                echo'Failed to create upload directory!';
                exit;
            }
        }

[broj1]

If I understood correctly you just want to skip first row which is heading row. If that is true then start inserting at row 2. Something like:

$heading = true;
while (($emapData = fgetcsv($file, 10000, ",")) !== FALSE)
{
    // check if the heading row
    if($heading) {
        // unset the heading flag
        $heading = false;
        // skip the loop
        continue;
    }
    $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);
}

[Bachu]

Hai;

Try this,Click Here

[mahesh_10]

Example of import-export excel file using php

[mahesh_10]

<?php
 if(isset($_POST['form_submit']))
 {
  include("db.php"); 
  require_once 'Classes/excel_reader.php';

  $file1 = $_FILES['excelfile']['tmp_name'];
  $data = new Spreadsheet_Excel_Reader($file1);
  $sheet = $data->sheets[0];
  $rows = $sheet['cells'];
  $rowCount = count($rows);


  for($i=2;$i<=$rowCount;$i++)
  {
    $name = $data->val($i,1);
    $email = $data->val($i,2);
    $password = $data->val($i,3);

    $sql = "insert into $tabel_name (`name`,`email`,`password`) values ('$name','$email','$password')";
    mysql_query($sql);
  }

 }
?>
<html>
 <head>
  <title>How To Import Excel (.xls) File To MySql Database Using PHP</title>
 </head>
 <body>
  <center><h1>zero-error-script.blogspot.in</h1></center>
  <form name="import_export_form" method="post" action="import.php" enctype="multipart/form-data">
   <label>Select Excel File : </label><input type="file" name="excelfile"/><br>
   <input type="submit" name="form_submit"/>
  </form>
 </body>
</html>

download full example : http://zero-error-script.blogspot.in/2014/06/how-to-import-export-excel-xls-to-excel.html

[Hein Min]

http://zero-error-script.blogspot.in/2014/06/how-to-import-export-excel-xls-to-excel.html

permission denied. Please add me.

[Gulllaume]

Thanks mahesh_10, your code was very useful !

[daksh singh]

not usefull for me ..... still cant insert excel data in mysql database