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:
This is Awesome script and worked for me really very well....
Thanks a lot
gk -
Glad I could help!
Good Morning Colleagues,
I get the exception message below, each time I run the codes above.
Deprecated: Assigning the return value of new by reference is deprecated in C:\wamp\www\LGHS\admin\Excel\reader.php on line 261
Hi Nnamdi
$this->_ole = & new OLERead(); (line 261a)
just remove the '&' sign there and your script will be working this is really awesome script for exporting data from excel file to sql its really worth working with it.
thanks a lot
for any query feel free to post here.
Very nce. thanks
Can't get it to work!
It displays this message:
Fatal error: Maximum execution time of 30 seconds exceeded in oleread.inc on line 172
Can anybody help me, please.
Not working. Getting message "
Fatal error: Maximum execution time of 30 seconds exceeded in C:\wamp\www\Spreadsheet\Excel\Excel\Spreadsheet\Excel\Reader\oleread.php on line 172"
Thanks for your post, tried it on my website and it worked very well with .xls spreadsheet excel 2003, I have not had any problems.
Hello,
I am having a hard time resolving a problem regarding utf-8 characterset, Turkish to be specific. I am trying to import a username that has some of these characters, İŞĞÇÜÖ.
if the column has İ in it, it interprets the rest of these unique characters just fine, meaning that it prints them out ok. but if there is no İ but other characters like ŞĞÇÜÖ, it does not interprets them as it should.
Any idea why?
Hi,
I am having a problem with when excel date. The value of date I got from excel shows only like this (Mar/Tue/2010201020102010). Anyone can help?
Really Awesome script, work great for me. Thanks a lot!!!
hello
the program works but when I home him I find these problems
"Notice: Uninitialized string offset: 2199023255040 in /home/suptec/public_html/charlemagne/Excel/oleread.php on line 27" and
Notice: Undefined index: in /home/suptec/public_html/charlemagne/Excel/oleread.php on line 136
and
Fatal error: Allowed memory size of 67108864 bytes exhausted (tried to allocate 71 bytes) in /home/suptec/public_html/charlemagne/Excel/oleread.php on line 131
plz help me
setOutputEncoding('CP1251');
$data->read('example.xls');
$conn = mysql_connect("10.40.20.201","root","tanla@123");
mysql_select_db("voiceobd",$conn);
for ($x = 2; $xsheets[0]["cells"]); $x++)
{
$msisdn=$data->sheets[0]["cells"][$x][1];
$achievement=$data->sheets[0]["cells"][$x][2];
$date=$data->sheets[0]["cells"][$x][3];
$sql="INSERT INTO achievementlist (msisdn,achieved,date)VALUES ('$msisdn',$achievement,'$date')";
echo $sql."\n";
mysql_query($sql);
}
?>
after executing it is showing:
The filename example.xls is not readable