If you have Excel files that need to be imported into MySQL, you can import them easily with PHP. First, you will need to download some prerequisites:
PHPExcelReader - http://sourceforge.net/projects/phpexcelreader/
Spreadsheet_Excel_Writer - http://pear.php.net/package/Spreadsheet_Excel_Writer
Once you've downloaded both items, upload them to your server. Your directory listing on your server should have two directories: Excel (from PHPExcelReader) and Spreadsheet_Excel_Writer-x.x.x (from Spreadsheet_Excel_Writer). To work around a bug in PHPExcelReader, copy oleread.inc from the Excel directory into a new path:
Spreadsheet/Excel/Reader/OLERead.php
The PHPExcelReader code will expect OLERead.php to be in that specific location. Once that is complete, you're ready to use the PHPExcelReader class. I made an example Excel spreadsheet like this:
Name Extension Email ---------------------------------------------------- Jon Smith 2001 jsmith@domain.com Clint Jones 2002 cjones@domain.com Frank Peterson 2003 fpeterson@domain.com
After that, I created a PHP script to pick up the data and insert it into the database, row by row:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | require_once 'Excel/reader.php'; $data = new Spreadsheet_Excel_Reader(); $data->setOutputEncoding('CP1251'); $data->read('exceltestsheet.xls'); $conn = mysql_connect("hostname","username","password"); mysql_select_db("database",$conn); for ($x = 2; $x < = count($data->sheets[0]["cells"]); $x++) { $name = $data->sheets[0]["cells"][$x][1]; $extension = $data->sheets[0]["cells"][$x][2]; $email = $data->sheets[0]["cells"][$x][3]; $sql = "INSERT INTO mytable (name,extension,email) VALUES ('$name',$extension,'$email')"; echo $sql."\n"; mysql_query($sql); } |
After the script ran, each row had been added to the database table successfully. If you have additional columns to insert, just repeat these lines, using an appropriate variable for each column:
$variable = $data->sheets[0]["cells"][$row_number][$column_number];
For more details, you can refer to a post in Zend's Developer Zone.
Am I correct in saying line 4 reads the whole spreadsheet into memory?
Won't that have issues with huge files, like 10 or 20 sheets each with 64k lines in them?
Warning: reader does not work with Excel 2007. Author's email addy in the code is no longer valid, and it appears that the sourceforge project has been abandonded.
@AR - Yes, that could be a problem. The phpexcelreader class doesn't allow for reading in chunks.
@OmahaVike - Try PHPExcel: http://www.codeplex.com/PHPExcel - It reads Excel 2007 documents.
Will this work on any server - does it need to be on Windows?
thanks for the sharing.
but i'm still confusing with your explanation above about: