import data from excel in php

Question

i want to import data from excel file using php.

and then if possible to save it mysql database.

Thanks

Answer 1

Best bet is to export from Excel to a CSV (Comma separated values) file. These files are easy to parse and load. If you are reading directly from an XLS file, I'm not sure how to do that. You might want to look and see if there is a libarary for PHP that can read Excel data files.

Answer 2

Importing from Excel files (XLS) is way harder than improting from CSV files. Usually I save my XLS to CSV with Excel then work on this CSV with PHP...

Look at PHP function fgetcsv at: http://ca.php.net/manual/en/function.fgetcsv.php

<?php
$row = 1;
if (($handle = fopen("test.csv", "r")) !== FALSE) {
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
        $num = count($data);
        echo "<p> $num fields in line $row: <br /></p>\n";
        $row++;
        for ($c=0; $c < $num; $c++) {
            echo $data[$c] . "<br />\n";
        }
    }
    fclose($handle);
}
?> 

If you still want to load XLS directly from PHP it's possible (but how reliable)... A quick seach resulted in http://sourceforge.net/projects/phpexcelreader/ which might be helpful.

Answer 3

Quite possible. You can save your Excel file as a CSV file, and use fgetcsv() to read that file in to PHP. fgetcsv() will parse your data into an array, which you can then create SQL queries out of to put into your database.

If all you're doing is putting it into a database, you might be able to bypass the need for a PHP script entirely and just use MySQL's LOAD DATA INFILE syntax on your CSV file instead:

LOAD DATA LOCAL INFILE '/importfile.csv'
INTO TABLE test_table
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(field1, filed2, field3);

Answer 4

Here's a tutorial on reading/writing an Excel spreadsheet directly (without having to export to CSV). The necessary packages are available from SourceForge and PEAR (cf. article).

Answer 5

Hi,

you can use dbTube.org if you want to import the excel into a mySQL db. You cna configure your import with a drag&drop GUI....very similar to a workflow editor.

Greetings