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
The code is working but every time it shows error which is given below.
"The filename exceltestsheet.xls is not readable"
can anyone help me i am using ms excel 2010 but i've saved the file into 2003 format but it could not read my file any more... kind reply me soon thanks in advance
Hi,
Is there any way to do the revers? I want to get all the data I have in the mysql database to excel.
I can see a class named Excelwriter. I am not much of a coder. Is that useful anyhow?
I tried a lot to add more number of columns but it only imports 6 rows in to the mysql db... Any alternative method to do that.. I tried with a lot of variables and looping structure but it only imports 6 no of columns.. HELP ME!!!
Sir it only takes up six columns , as u told to repeat the steps using a no of variables i too did the same but no results. At max six columns are added..Is there any way to modify the "reader.php" or "oleread.inc" files ? to make it to take more than six colums.. Please reply soon..
If you are using this code:
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 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);
}
As given in the article,
Then it should not be a problem.
I myself have added more than 20-25 rows in the table using this code.
Make sure that you store the data in the variable first and then use itin sql query.
Also make sure, there is no problem in your excel file also.
Actually i did the same but i am able to add columns more than 6.. Rows are being added up leaving behind the columns ..No problem in excel file.. "$phno = $data->sheets[0]["cells"][$x][7];" This doesn't work out...no column is added to the db.
The adding of columns more than six doesn't work out.. Actually when we try to add the columns more than six the specified row is not inserted to the db.. PLEASE HELP ME ,... I am in a urgent need..
thanks, it very easy
require_once 'Spreadsheet/Excel/reader.php';
$data = new Spreadsheet_Excel_Reader();
$data->setOutputEncoding('CP1251');
$data->read('mom.xls');
$conn = mysql_connect("localhost","root","123");
mysql_select_db("test",$conn);
for ($x = 2; $xsheets[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);
}
work well.
Hey
I am trying to make it happen with the phpexcelreader but it keeps throwing an error 'The filename is not readable' What might be the problem?
Please Help Me
every thing is ok
but i have the ERROR
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
The filename exceltestsheet.xls is not readable >
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
how can i solve it
i saved my Excel file AS 2003
And Put the same name appeared in u r php code in it
HOW CAN I SOLVE THIS
PLEASE HELP
Hi
Major
It is very easy.
Thanks