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,

3b414cc2b0556bbd8691e2ecf29ce42f

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,

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

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

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);
}
commented: Great its worked +0
<?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

Thanks mahesh_10, your code was very useful !

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