RSS

Import Excel Data into MySQL with PHP

19 Feb

phpexcel

To import Excel data, first you need to have a Excel reader. It should be accurate enough to interpret Excel data as expected. There ‘s a good old Excel reader.

Download PHPExcelReader.

In the downloaded archive, you only need Excel directory with files including oleread.inc and reader.php.

Just extract it where your web server can access.

Next place your excel file or just create one with some dummy data. Make sure this file is readble by the web server.

Finally create your php script to connect with database, read Excel file and insert data into db.

<?php require_once 'Excel/reader.php'; $data = new Spreadsheet_Excel_Reader(); $data->setOutputEncoding('CP1251');
$data->read('a.xls');

$conn = mysql_connect("localhost","root","");
mysql_select_db("test",$conn);

for ($x = 2; $x <= count($data->sheets[0]["cells"]); $x++) {
$first = $data->sheets[0]["cells"][$x][1];
$middle = $data->sheets[0]["cells"][$x][2];
$last = $data->sheets[0]["cells"][$x][3];
$sql = "INSERT INTO mytable (First,Middle,Last)
VALUES ('$first','$middle','$last')";
echo $sql."\n";
mysql_query($sql);
}

?>

Even your 1cent donation is appreciated.
Donate Button with Credit Cards

Advertisements
 
24 Comments

Posted by on February 19, 2014 in PHP

 

Tags:

24 responses to “Import Excel Data into MySQL with PHP

  1. Igor Karpov

    June 19, 2014 at 3:13 pm

    This is fine solution for small xls files. But if you want read 10k rows, this way will not work.
    For big files you should use NoXLS API Parser http://noxls.net/documentation

     
  2. Akhilesh

    November 25, 2014 at 6:28 pm

    $data->read(‘a.xls’);
    what this line do…….
    my browser shows the a.xls file is not readable

     
  3. sidd

    January 16, 2015 at 6:33 pm

    if the code is in server will your code search for a.xls on server or client machine

     
  4. mahesh

    February 17, 2015 at 5:01 pm

    where is require_once ‘Excel/reader.php’; file

     
    • Rajitha

      February 18, 2015 at 10:57 am

      Hi,
      There should be a folder named ‘Excel’ in PHPExcelReader download.

       
  5. Guruprasad

    April 7, 2015 at 4:00 pm

    i dont have any Excel folder, i have PHPExcel folder and no reader.php file in it

     
  6. sabari

    July 10, 2015 at 12:32 pm

    Thank u very much… i got solution from this one

     
  7. pradeep

    July 19, 2015 at 12:50 pm

    Hello Sir i am using codeigniter framework why not work this code plzz help me.

    A PHP Error was encountered

    Severity: Warning

    Message: require_once(Excel/excel_reader2.php): failed to open stream: No such file or directory

    Filename: controllers/lead_controller.php

    Line Number: 163

     
  8. pradeep

    July 19, 2015 at 12:54 pm

    dir this is my code

    $config[‘upload_path’] = ‘./uploads/’;
    $config[‘allowed_types’] = ‘xls|xlsx’;
    $config[‘max_size’] = ‘1024’;
    $config[‘max_width’] = ‘1024’;
    $config[‘max_height’] = ‘768’;
    $this -> load -> library(‘upload’, $config);
    if (!$this -> upload -> do_upload()) {
    $data[‘message’] = $this -> upload -> display_errors();
    } else {
    $data[‘message’] = ‘add new success’;
    }
    $upload_data = $this -> upload -> data();
    $filename = $upload_data[‘file_name’];

    echo $filename;
    require_once ‘Excel/excel_reader2.php’;
    $data = new Spreadsheet_Excel_Reader();
    $data->setOutputEncoding(‘CP1251’);
    $data->read($filename);

    //$conn = mysql_connect(“localhost”,”root”,””);
    //mysql_select_db(“test”,$conn);

    for ($x = 2; $x sheets[0][“cells”]); $x++) {
    $first = $data->sheets[0][“cells”][$x][1];
    $middle = $data->sheets[0][“cells”][$x][2];
    $last = $data->sheets[0][“cells”][$x][3];
    $leads = array(
    // ‘lead_code_id’ => $this -> getLeadCodeId($d2->format(‘y-m-d’)),
    ‘lead_name’ => $first,
    ‘client_name’ => $middle,
    ’email_id’ => $last,
    /// ‘date’ => $d2->format(‘y-m-d’),
    ‘active’ => 1);

    $id = $this -> modellead -> save($leads);

     
    • Rajitha

      July 19, 2015 at 2:14 pm

      Make sure a file called “excel_reader2.php” exists in “Excel” directory. As the error message implies, obviously a failure of loading this file. Where have you placed the downloaded files in your project? You need to include the file in a proper way.

      You can place Excel lib or whatever necessary files within your application libraries. Then include the file in your controller. Something like below.

      require_once(APP_PATH.”libraries/ExcelReader……..”);

       
  9. pradeep

    July 20, 2015 at 6:28 pm

    Dear ,sir this is showing following error:
    unable to read record.xls file. please send me whole code my mail id rawat_pradeep@ymail.com

     
    • Rajitha

      July 20, 2015 at 9:52 pm

      Hi, this is something simple right? Please close excel file before uploading or running the script. And verify that your excel file path is correct and can be accessible if it is placed in a folder.

       
  10. pradeep

    July 22, 2015 at 11:49 am

    Dear, sir this is showing following

    error A PHP Error was encountered
    Severity: Notice
    Message: Undefined offset: 0
    Filename: controllers/lead_controller.php
    Line Number: 170
    Total Sheets in this xls file: 0

    and
    $record= count($data->sheets[0][“cells”]);
    count function also not working.
    int(0)

     
  11. pradeep

    July 22, 2015 at 1:21 pm

    tank you sir i got it..nice example.

     
  12. pradeep

    July 23, 2015 at 10:56 am

    Dear sir how we will upload also xlsx format file. by this code.

     
  13. Rajitha

    July 23, 2015 at 1:39 pm

    For this, you need to add code for uploading the file. You may find several options here. If you need to upload a specific file always, you can rename the file before uploading and then move to a certain folder. In the code, you refers to the file path which would not change unless you are forced to do.

    Without renaming file, still you can get the file path and continue.

    If you ‘re not familiar with file uploading, please play around this. It is not something hard.

    For a all in one tut, Google pointed me this. http://www.discussdesk.com/import-excel-file-data-in-mysql-database-using-PHP.htm

     
    • pradeep

      July 30, 2015 at 6:21 pm

      Dear sir ,
      i have rename file .xls format its not working,if uploading time file extension is .xls its working fine.
      this is my code

      $config[‘upload_path’] = ‘./uploads/’;
      $config[‘allowed_types’] = ‘xls|xlsx’;
      $config[‘max_size’] = ‘1024’;
      $config[‘max_width’] = ‘1024’;
      $config[‘max_height’] = ‘768’;
      $config[‘overwrite’] = TRUE;

      // $config[‘file_name’] = $file_name;
      // $config[‘encrypt_name’] = TRUE;
      $this -> load -> library(‘upload’, $config);
      if (!$this -> upload -> do_upload()) {
      $data[‘message’] = $this -> upload -> display_errors();
      } else {
      $data[‘message’] = ‘add new success’;
      }
      $upload_data = $this -> upload -> data();
      $filename = $upload_data[‘file_name’];

      $dir=’./uploads/’;
      $files1 = scandir($dir);
      //var_dump($files1) ;
      unset($files1[0]);
      unset($files1[1]);
      foreach ($files1 as $value)
      {
      $oldfname=’./uploads/’.$value;
      $fname=”records”;
      $ext=”.xls”;
      $name=$fname.$ext;
      $newfname=’./uploads/’.$name;
      $file_name=rename($oldfname,$newfname);

      }

      $uploaded_file_name = $_FILES[“userfile”][‘name’];
      if ($uploaded_file_name != “” || NULL) {
      require_once ‘Excel/excel_reader2.php’;

      $dir=’./uploads/’;
      $files1 = scandir($dir);

      unset($files1[0]);
      unset($files1[1]);
      foreach ($files1 as $fname)
      {
      //$ext = pathinfo($fname, PATHINFO_EXTENSION);
      $saved_file_name=’./uploads/’.$fname;

      // $path = ‘./uploads/’ . file_name; /*old path*/
      $path=$saved_file_name;
      $data = new Spreadsheet_Excel_Reader();

      $data -> setOutputEncoding(‘CP1251’);
      // $newpath = fopen($path, “r”);
      $data -> read($path);

      error:

      The filename ./uploads/records.xls is not readable

       
  14. Lodewijk

    January 8, 2016 at 3:47 am

    I am getting a error during import, but i can’t figger it out what it is. The error is : Notice: Undefined offset:

     
    • Rajitha

      January 8, 2016 at 7:43 am

      Hi,
      If you still haven’t solved this problem, Please make sure you have set column names properly and excel file has valid content in your context.

       
  15. pradeep

    June 23, 2016 at 4:24 pm

    Warning: require_once(Excel/reader.php): failed to open stream: No such file or directory in D:\xamp\htdocs\import.php on line 2

    Fatal error: require_once(): Failed opening required ‘Excel/reader.php’ (include_path=’.;D:\xamp\php\PEAR’) in D:\xamp\htdocs\import.php on line 2

     
  16. Vishal

    August 12, 2016 at 4:20 pm

    How can we upload large xlsx file into mysql

     

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: