Convert .xls to .csv via PHP


Reading data from an MS Excel file using PHP is sometimes a pain for most developers. This is so because PHP and xls files have some compatibility issues in the past. Moreover, it is easier to read and manipulate data using PHP in comma separated values (csv) file than the Microsoft-owned proprietary format. One and is the perfect way to have this addressed is the conversion of xls to csv format.

A number of open source PHP scripts are made available online by various developers. In this post, I will show how the conversion is done using a third party library called PHP Excel Reader. Source files and documentations can be downloaded here.

After downloading the PHP Excel Reader class, put in a APACHE server-accessible location. Read contents from an xls file by instantiating the Spreadsheet_Excel_Reader class as in the following code snippet:

 <?php $xls = new Spreadsheet_Excel_Reader('xls_test.xls'); ?>

Loop through the data saved in the $xls object and copy content in a storage variable.

<?php
 $rows = $xls->rowcount();
 $cols = $xls->colcount();
 $csv = "";

 for($r = 0; $r < $rows; $r++) {
    for($c = 0; $c < $cols; $c++) {
       $csv .= $xls->raw($r, $c);
       if($c == $cols - 1) {
          $csv .= "\n";
       } else {
          $csv .= ",";
       }   
    }
 }
?>

Save the accumulated contents of $csv in a file.

 <?php
   $handler = fopen("test.csv", "w");
   fwrite($csv, $handler);
   fclose($handler);
 ?>

You may now access the newly-created file which is just the csv version of your Excel file.

12 Responses

  1. Ebenstein says:

    There are also than less ands less inventory and when that happens prices will go up because of the suppy and demand .

  2. Naseer says:

    Thanks for taking time to post this article but this not works.
    Creates a csv file but there is no data in it.

    • admin says:

      Hi Naseer! You just need to make sure that your xls file you are generating csv from is not empty. :D

  3. sankari says:

    I am getting these errors:
    1. Deprecated: Assigning the return value of new by reference is deprecated in C:\wamp\www\iacc\php-excel-reader-2.21\excel_reader2.php on line 916
    2. Deprecated: Function split() is deprecated in C:\wamp\www\iacc\php-excel-reader-2.21\excel_reader2.php on line 79

    2. Deprecated: Function split() is deprecated in C:\wamp\www\iacc\php-excel-reader-2.21\excel_reader2.php on line 844

    can u help me…

    • admin says:

      Hi Sankari,

      For #1, please show me the code for me to check
      #2, you may use explode() instead.

      Also, what version of PHP are you using?

      Thanks! :D

  4. Al says:

    Warning: fwrite(): supplied argument is not a valid stream resource in …

    And the .csv file is there, but there is no data in it.

    There are data in my .xls file, i’m using php version 5.1.4

    Can you help me ?

    • admin says:

      Hi Al!

      You need to make sure that your reference to the excel file is correct and that it contains data.

      You may post your code so I can check where the problem is coming from. :D

      Thanks!

  5. Al says:

    problem solved

    i changed this :

    $csv .= $xls->raw($r, $c);

    for this :

    $csv .= $xls->val($r, $c);

    And this :

    fwrite($csv, $handler);

    for this :

    fwrite($handler, $csv);

    making this, the code runs perfect

  6. manjeet says:

    It is giving me an error that excel file is not readable. I have set full permission on the excel file but no luck.

    Please suggest.

  7. chhavi says:

    i am getting file not readable error..pls help.cn u suggest wht to do??

  8. admin says:

    Hello Manjeet and Chhavi!

    Please change this:

    fwrite($csv, $handler);

    to this:

    fwrite($csv, $handler);

    Sorry about that. The resource and string content parameters of th fwrite function were interchanged. Thanks Al!

    :D

  9. admirer says:

    rowcount();
    $cols = $xls->colcount();
    $csv = array();

    for($r = 1; $r <= $rows; $r++) {
    $row = array();
    for($c = 1; $c val($r, $c);
    }
    $csv[] = $row;
    }

    $fhandle = fopen(“test.csv”, “w”);
    foreach ($csv as $row){
    fputcsv($fhandle,$row);
    }
    fclose($fhandle);

    //eof

Affiliate Disclosure

Disclosure of Material Connection: The author of this page has the following material connection: the author has an affiliate relationship with a person, company, product, or service whose logos or images appear in the 'Advertisements' section and may receive a commission on referrals or sales generated by that advertisement.

Advertisements



ColdBuffer Blog
Add blog to our blog directory. PH Blogs Technology & Computers - Top Blogs Philippines