PHP

Read Excel file and save details to database using PHP

Read Excel file and save details to database using PHP
  • Amit

    How to solve this issue :
    Deprecated: Assigning the return value of new by reference is deprecated in reader.php on line 263

    • 1-The problem is in php version. Open the reader.php file and find the following line 261.

      $this->_ole =& new OLERead();

      2-Comment or remove that line and put this script.

      $a = new OLERead();
      $this->_ole =& $a ;

      3-Save the file.

      If not success, do this.
      Replaced with the following, where “&” symbol has been removed:
      $this->_ole = new OLERead();

      In some cases(at least in recursive loops while creating a tree of nodes containing child nodes) requires unset($this->_ole); before the actual object assignment line to avoid all child nodes becoming identical.
      which is not the case here as its in class contractor which will be called once.

      Thanks

  • wasim

    Sir i want upload excel data in my sql database with the browsing file, with the you above we just upload sample.xls file which is in same folder. But I want upload any file in any location. Please help me.

    • Thank you for your comment.
      In the current example, these is no user upload the file.It is just read existing file. So you can do like this to upload any Excel file to specific folder as you wish.But you need to give folder name to upload it.

      read($excelfilename);

      $x=1;
      while($xsheets[1][‘numRows’]) {
      echo “tn”;
      $y=1;
      while($ysheets[1][‘numCols’]) {
      $cell = isset($excel->sheets[1][‘cells’][$x][$y]) ? $excel->sheets[1][‘cells’][$x][$y] : ”;
      echo “tt$celln”;
      $y++;
      }
      echo “tn”;
      $x++;
      }

      }
      }
      ?>

      Upload File
      File :

      Regards

  • taqi

    i want to convert a .xml file to .xls file .. can you help me

  • Pingback: Convert xml file into excel file using php()

  • ramesh

    goodmorning Mr.Sumith harshan source code is not downloded. ur given table structure its enough to exceute or need to create the database in php myadmin

    • Hello Ramesh,
      After click download button, open the new page. Then click “Skip this Add” button. Then you can download the source code.
      In the save details to db process, you need to change field names to match the excel sheet columns. That means in the excel sheet i put , Name, Country, Age,…
      But in the INSERT query and table:- id,name,job,email. So change the excel sheet or table fields to match them. I forgot to change it. 😉

      Regards

  • ramesh

    please help me sir

    I got this out:

    for

    http://localhost/xsltoweb/insert.php :-

    The filename sample.xls is not readable
    for
    http://localhost/xsltoweb/details.php
    Sheet 1:
    The filename sample.xls is not readable

    but i create a file name with sample.xsl
    plz help me

    • 1-Check the excel file permission. need to be 755 in the web server. In localhost, check read only and hidden is checked or not.(Right click ->properties)

      2-check the allow_url_fopen is set to “on” in php.ini file.

      3-Check the excel file format. “sample.xls” . I’m using ms office 2003.

      4- If not success,
      a-Open the reader.php file and find the following line 261.

      $this->_ole =& new OLERead();

      b-Comment or remove that line and put this script.(NOTE THERE IS A “&” SYMBOL)

      $a = new OLERead();
      $this->_ole =& $a ;

      c-Save the file.

      If not success,
      Replaced with the following, where “&” symbol has been removed:
      $this->_ole = new OLERead();

      If you success, please leave a comment here.

      Cheers

  • Hajjaji-Tech

    thx Mr.Sumith,

    Can you help me !!

    After saving details to the database using sample.xls, can I update the same database using the same file like sample2.xls with other content.

    and thx again

    • Yes, you can update it.But you need to put correct file name in here.
      $excel->read(‘sample.xls’); // with new file name

      In the above I used 3 column names,also query. So you need to modify these are match with excel file by edit the excel file.
      Query change to update.

      So there 2 points need to change.

      Regards
      Sumith

  • shkdxb

    Nice post. i am trying to update some of the fields in my mysql database file from excel. i am trying your sample. i could able to make dbconnection and new table users_details in my database. but opening save-details-to-database.php doesn’t update anything in my table. what am i doing wrong? pl help.

  • vishwanath tadahal

    Hi Sumith Harshan thank u for ur post
    I have problem, it is not working for .xlsx extension it say, file is not readable.

    • 1-Check the excel file permission of the excel file. need to be 755 in the web server. In localhost, check read only and hidden is checked or not.(Right click ->properties)

      2-check the allow_url_fopen is set to “on” in php.ini file.

      3-If xlsx format , you need this format into $excel->read(‘sample.xlsx’);

      4- If not success,
      a-Open the reader.php file and find the following line 261.

      $this->_ole =& new OLERead();

      b-Comment or remove that line and put this script.(NOTE THERE IS A “&” SYMBOL)

      $a = new OLERead();
      $this->_ole =& $a ;

      c-Save the file.

      If not success,
      Replaced with the following, where “&” symbol has been removed:
      $this->_ole = new OLERead();

      If you success, please leave a comment here.

      Cheers

      • vishwanath tadahal

        Sorry dude it is not working for .xlsx
        is this class works for .xslx

    • Hi,

      My new post here and it is working for excel all formats.

      http://webexplorar.com/read-excel-file-and-save-details-to-database-using-php-repost/

  • rawan

    it is really good but it not working with Excel 2007 (xlsx) 🙁

  • Divya Pai

    how to give permission to the excel file if it is locked

  • leke

    how can i resolve this issue: i keep getting this error: file is not readable. please help

  • Hello Sir,
    I want insert “sample.xml” excel data in my SQL database, but i have an error

    ————— X — X —————-
    Successfully connected to server

    No database selected
    ————— X — X —————-

    // Save details
    $sql_insert=”INSERT INTO users_details (id,name,job,email) VALUES (”,’$name’,’$job’,’$email’)”;
    $result_insert = mysql_query($sql_insert) or die(mysql_error());

    Please help me.

    • Hi

      Alfatah,

      Check the config file for correct database details.Above issue happened because of the not entered database or wrong database you have entered.Check it again.

      Regards

      • Thank you sir,
        Now it’s working. I store my data in Database. <3 🙂

  • bhaskar

    hi sumith ……. thanks for your code
    but one problem i am facing like while retrieving data from the excel sheet it only reads the last row ……suggestion pls

    • Hello Bhaskar,
      Can you check the while loop is reading all? May be missed x++ or issue of $excel->sheets[0][‘numRows’].Check that again.

      Regards

  • shekhar

    The filename a.xls is not readable

    • Hi,
      Check above excel file has correct file permissions. 644 is recommend.

      Also check correct file name and it’s path here:-

      $excel->read(‘a.xls’); // file name with path.

      Thanks

  • Alexanedeo

    Hello

    i am using this code but not working for me please anyone can help me.

    i am using this code and it’s generate error The filename sample.xls is not readable.

    please anyone can help me as well as soon.

    • Hi,

      1-Check the excel file permission. need to be 755 in the web server.
      In localhost, check read only and hidden is checked or not.(Right click
      ->properties)

      2-check the allow_url_fopen is set to “on” in php.ini file.

      3-Check the excel file format. “sample.xls” . I’m using ms office 2003 here.

      4- If not success,
      a-Open the reader.php file and find the following line 261.

      $this->_ole =& new OLERead();

      b-Comment or remove that line and put this script.(NOTE THERE IS A “&” SYMBOL)

      $a = new OLERead();
      $this->_ole =& $a ;

      c-Save the file.

      If not success,
      Replaced with the following, where “&” symbol has been removed:
      $this->_ole = new OLERead();

      Also, cannot read .xlsx format files, only the older .xls format files here. If you want to read .xlsx files check this post.

      http://webexplorar.com/read-excel-file-and-save-details-to-database-using-php-repost/

      Thanks

      • Alexanedeo

        ok thankyou for help me.. 🙂

  • upen

    how to sove this issue.
    I want to check the my excel sheet whether any value is empty or not If had then excel sheet unable to insert any value in database…..

  • Pingback: Read Excel file and save details to database using PHP-Repost()

  • zakeri

    hello
    Please help Icant import arabic character to db

  • Evaria Ayu

    this tutorial helped me a lot! Thank you so much

  • Nurul Amirah

    How to convert xlsx or xls to csv by using php.
    please help me and thank you in advance.

    • Hi,
      Try this. Download the class files from here:-https://github.com/PHPOffice/PHPExcel

      require_once ‘Classes/PHPExcel/IOFactory.php’;

      $inputFileType = ‘Excel5’;
      $inputFileName = ‘YOUR_EXCEL_FILE_PATH’;

      $objReader = PHPExcel_IOFactory::createReader($inputFileType);
      $objPHPExcelReader = $objReader->load($inputFileName);

      $loadedSheetNames = $objPHPExcelReader->getSheetNames();

      $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcelReader, ‘CSV’);

      foreach($loadedSheetNames as $sheetIndex => $loadedSheetName) {
      $objWriter->setSheetIndex($sheetIndex);
      $objWriter->save($loadedSheetName.’.csv’);
      }

  • Shishir

    How can use this code in magento. I want to upload the excel file at the magento product import

  • Shishir

    Thanks for the code.
    Can you tell me, how can i use this code in magento.
    I want to upload the products from excelsheet in magento, how can i do that ?

  • Tharunjeni

    how to get name of the worksheets?

  • Prabhakar H.D

    Dear Sumith,
    Greetings of the day!!
    I need your help on updating the database using sample.xls file.
    i have a exported file of sample.xls file and i made the changes on name and email for more than 10 column and save it.
    when am importing this xls file to databse it should be updated not to inserted.
    how am able to that using your code.
    UPDATING EXISTENCE DATABASE TO NEW XLS FILE.please help me.
    Thank You..