From Excel (xls/xlsx) to MySql Database

Topics: Developer Forum, Project Management Forum, User Forum
Apr 25, 2013 at 11:14 AM
Edited Apr 25, 2013 at 11:19 AM
Hi,

I have an important project that requires XLS/XLSX files to be uploaded to Mysql Database. I stumbled upon PHPEXCEL and have been trying to write a script but without much success as I am new to PHPEXCEL.

If any one can suggest, guide me how to read and import Excel data to Mysql DB using PHPEXCEL I would be much obliged.

Thanks in advance.
Apr 25, 2013 at 1:32 PM
Hum... A small example easy, directly from the documentation (4.5.5):
<?php
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objReader->setReadDataOnly(true);

$objPHPExcel = $objReader->load("test.xlsx");
$objWorksheet = $objPHPExcel->getActiveSheet();

$highestRow = $objWorksheet->getHighestRow(); // e.g. 10
$highestColumn = $objWorksheet->getHighestColumn(); // e.g 'F'

$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn); // e.g. 5

echo '<table>' . "\n";
for ($row = 1; $row <= $highestRow; ++$row) {
  echo '<tr>' . "\n";

  for ($col = 0; $col <= $highestColumnIndex; ++$col) {
    echo '<td>' . $objWorksheet->getCellByColumnAndRow($col, $row)->getValue() . '</td>' . "\n";
  }

  echo '</tr>' . "\n";
}
echo '</table>' . "\n";
?>
It shows the course of all cells in a worksheet, without the use of iterators. The contents of cells are displayed, but you can easily store it in a table instead.
The type of input file can be changed as needed (type known by a test or determined by the method PHPExcel_IOFactory identify() upstream).
Only data is read (setReadDataOnly), this reduces the resources needed but in return, certain data (date, time) are less easy to interpreted.
There are other ways to do, difficulties can appear, but rather than rewrite documentation - that is freely available for everyone - we will see those you'll be faced.
Apr 25, 2013 at 3:02 PM
So kind of you Lwol to respond immediately. I will start working immediately and keep you updated. Before that I will go thru' the documentation which I didn't earlier.

Thanks again.
Apr 25, 2013 at 5:35 PM
Edited Apr 25, 2013 at 6:06 PM
LWol wrote:
Hum... A small example easy, directly from the documentation (4.5.5):
``` It shows the course of all cells in a worksheet, without the use of iterators. The contents of cells are displayed, but you can easily store it in a table instead.
The type of input file can be changed as needed (type known by a test or determined by the method PHPExcel_IOFactory identify() upstream).
Only data is read (setReadDataOnly), this reduces the resources needed but in return, certain data (date, time) are less easy to interpreted.
There are other ways to do, difficulties can appear, but rather than rewrite documentation - that is freely available for everyone - we will see those you'll be faced.
Hello LWol,

With minor changes made in your script, I made it thru' to display my excel data on a webpage.

Output from a test xls file:
Username Email Gender Country
psb psb67@hotmail.com M India
naga naga@hotmail.com F France
sudha sudha@gmail.com F Germany
raama rama@yahoo.com F Spain
I would be much obliged if you can just give me some clues on where to place mysqli prepared insert statements in the script.
To quote Mr. Mark's reply to a query from another thread, " Within the for row loop, instead of echoing each cell value, build it into an SQL insert statement, and then at the end of the col loop, where you currently echo '</tr>', execute the insert"; I am unable to interpret it, although its a simple and straight forward one.
Thanks again in advance.
Apr 25, 2013 at 7:26 PM
Edited Apr 25, 2013 at 7:29 PM
LWol wrote:
Hum... A small example easy, directly from the documentation (4.5.5):

It shows the course of all cells in a worksheet, without the use of iterators. The contents of cells are displayed, but you can easily store it in a table instead.
The type of input file can be changed as needed (type known by a test or determined by the method PHPExcel_IOFactory identify() upstream).
Only data is read (setReadDataOnly), this reduces the resources needed but in return, certain data (date, time) are less easy to interpreted.
There are other ways to do, difficulties can appear, but rather than rewrite documentation - that is freely available for everyone - we will see those you'll be faced.
Hello LWol,

Thanks for citing 4.5.5- Looping cells using iterators - from the documentation. It has been highly useful.

Regards
Apr 26, 2013 at 8:25 AM
Well... You need to have something like :
INSERT INTO YourTable (FieldUsername, FieldEmail, FieldGender, FieldCountry) VALUES ('psb', 'psb67@hotmail.com', 'M', 'India'), ('naga', 'naga@hotmail.com', 'F', 'France'), ...

In your loop (skip the first row, containing header), you build the values part :
if($SQL!='') $SQL.=',';
$SQL.='(\''.mysqli_real_escape_string($Link, $objWorksheet->getCellByColumnAndRow($colUsername, $row)->getValue()).'\',\''.
        mysqli_real_escape_string($Link, $objWorksheet->getCellByColumnAndRow($colEmail, $row)->getValue()).'\',\''.
        mysqli_real_escape_string($Link, $objWorksheet->getCellByColumnAndRow($colGender, $row)->getValue()).'\',\''.
        mysqli_real_escape_string($Link, $objWorksheet->getCellByColumnAndRow($colCountry, $row)->getValue()).'\')';
After the loop :
if($SQL!=''){
    $SQL='INSERT INTO YourTable (FieldUsername, FieldEmail, FieldGender, FieldCountry) VALUES '.$SQL;
    $result=mysqli_query($Link, $SQL);
}
$colUsername, $colEmail, .... contains the appropriate column number. $SQL, empty string on loop entry.
If you have numerous entries or the MySQL server don't accept big query, you need to check the size into the loop.
Adapt this if you use a cell iterator, add some checks on data(gender, email, ...), on result, use INSERT IGNORE or REPLACE...

My example uses not the "prepared statement" in the MySQL sense because I think that this is not the best solution here.
Apr 29, 2013 at 12:05 PM
LWol wrote:
Well... You need to have something like :
INSERT INTO YourTable (FieldUsername, FieldEmail, FieldGender, FieldCountry) VALUES ('psb', 'psb67@hotmail.com', 'M', 'India'), ('naga', 'naga@hotmail.com', 'F', 'France'), ...
Hello LWol,

I sincerely appreciate you for guiding me from the beginning with immense patience. I tried to implement your code but could not succeed and was almost on the verge of giving up but finally managed to update data in excel sheet to mysql DB with mysqli query(insert into....). Now, I will try to make the update automatic by adding auto-save vb script in excel sheet.

Once again thank you so much and will be in touch.
Regards.
Apr 29, 2013 at 12:41 PM
Glad to have been able to help.

psb1967 wrote:
Now, I will try to make the update automatic by adding auto-save vb script in excel sheet.

Can you develop what you would like to realize?
Apr 29, 2013 at 3:38 PM
Edited Apr 29, 2013 at 8:08 PM
LWol wrote:
Glad to have been able to help.

psb1967 wrote:
Now, I will try to make the update automatic by adding auto-save vb script in excel sheet.

Can you develop what you would like to realize?
Hello LWol,

First and foremost, a big thanks to Mr. MarkBaker for his extraordinary contribution.

<<Now, I will try to make the update automatic by adding auto-save vb script in excel sheet.

Can you develop what you would like to realize?>>

It is highly a valuable question. Frankly, in my excitement I had completely overlooked the fact that the vb script is only used to save excel file but the question of refreshing or reloading the php page, only then mysql DB will get updated, did not strike my mind. I think I will find a solution for that. Lets see. :)

Will keep you updated.

Regards.
May 2, 2013 at 4:53 PM
LWol wrote:
Glad to have been able to help.
Hello LWol,

Gave my script a final touch up, replaced mysqli->query("INSERT INTO.....) statement with mysqli INSERT INTO prepared statement - binding and execute. Now its complete.

Nice day.