Import Excel Data into MySQL with PHP

19 Feb


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

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

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


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


Posted by on February 19, 2014 in PHP



26 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

    • lakshmi

      February 27, 2017 at 3:24 pm

      Successfully Uploaded

      Hai this is lakshmi
      here i am presenting my problem to you please give the correction on this code

      This is my upload excel sheet to database or MYSQL it just inserting zeros to database
      what is the problem in this code can you explain it to me.

  2. Akhilesh

    November 25, 2014 at 6:28 pm

    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

      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();

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

    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.


  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

    • 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

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

  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.

    • 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’];

      $files1 = scandir($dir);
      //var_dump($files1) ;
      foreach ($files1 as $value)


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

      $files1 = scandir($dir);

      foreach ($files1 as $fname)
      //$ext = pathinfo($fname, PATHINFO_EXTENSION);

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

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


      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

      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

  17. deva

    November 24, 2017 at 12:54 pm

    if i want insert excel file dynamically not to particular given one then this time how to import excel file in mysql database using php


Leave a Reply

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

You are commenting using your 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: