Import data from Excel to PHP

Question

I want to import data from an excel file using PHP, and then, if possible, save it to a MySQL database.

Answer 1

Importing from Excel files (.xls) is much more complicated than improving from .csv files. I usually save my XLS to CSV using Excel and then work on that CSV with PHP ...

Take a look at the fgetcsv at PHP function: 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 download XLS directly from PHP, this is possible (but how reliable it is) ... A quick search led to http://sourceforge.net/projects/phpexcelreader/ which may be useful.

Answer 2

Pretty possible. You can save the Excel file as a CSV file and use fgetcsv () to read this file in PHP. fgetcsv() will parse your data in an array, which then you can create SQL queries from the database.

If everything you do puts it in a database, you can completely bypass the need for a PHP script and just use the MySQL LOAD DATA INFILE syntax in your CSV file:

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

Answer 3

It is best to export from Excel to CSV (comma separated values). These files are easy to parse and download. If you are reading directly from an XLS file, I do not know how to do this. You might want to look and see if there is libarary for PHP that can read Excel data files.

Answer 4

Here's a tutorial on reading / writing Excel spreadsheet directly (without the need to export to CSV). Required packages are available from SourceForge and PEAR (see Article).

Answer 5

 <? i$db = mysql_connect("localhost", "root", "") or die("Could not connect."); if(!$db) die("no db"); if(!mysql_select_db("test",$db)) die("No database selected."); if(isset($_POST['submit'])) { $filename=$_POST['filename']; $handle = fopen("$filename", "r"); while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) { $import="INSERT into sample(name,email) values('$data[0]','$data[1]')"; mysql_query($import) or die(mysql_error()); } fclose($handle); print "Import done"; } else { print "<form action='import.php' method='post'>"; print "Type file name to import:<br>"; print "<input type='text' name='filename' size='20′><br>"; print "<input type='submit' name='submit' value='submit'></form>"; } ?> 

A source