MainelyDesign.com Blog

Importing an Excel file into CakePHP

Posted on 04/16/2009 at 02:42 pm by Kevin Wentworth
Viewed 33,769 times | 0 comments

I needed to be able to upload an excel file, parse the file, and then add each row as a separate database entry.  I had this working for a CSV file using the php built-in function fgetcsv(), but the client switched to using an excel file (instead of direct PHP access to the in-house database, we decided to have the client upload a CSV/Excel file). 

I did a little research and found the perfect solution- PHP-Excel Reader.  I saved this to my CakePHP vendors directory and had to make one modification: at line 31 in reader.php, I changed the require_once 'blah/blah/oleread.inc' to just require_once 'oleread.inc' and made sure it was in the same directory as reader.php.

I had to adjust some server settings (because I was loading in a huge file- WARNING: this isn't a memory efficient way of doing things):

After I got the vendor library loaded into CakePHP:

I initialized the Spreadsheet_Excel_Reader class (there is some examples that come with the download file)

Then, I read in my data posted from the form (make sure the form type is set to FILE)

This is the cool part.  Here is where you go line by line and change the data as you see fit, and then insert into the DB.  I had to manipulate the data that was in the spreadsheet to match up with my database fields, so I chose to take this route.  You may not need to.

You'll notice a few things- In my form I have a radio button named 'overwrite' that if selected, I will overwrite the data, rather than update it.  I found that CakePHP didn't have a good deleteAll function for my purpose.  You need to supply a condition, you can't just delete all.  When I supplied a condition of 'id !=' => null, it emptied the table but at the cost of the lookup, and then deleting where all IDs where in the 'WHERE id IN(x, xx, xxx)' it took quite a time hit.  

Instead, I wrote my own little function called emptyTable().  I think I'll move it up to my app_model.php file, because it is so simple and so powerful.  Here it is, in all it's wonder:

I hope you found this article/tutorial useful.  It's my first one, so I'm sure it was hard to follow.  I promise to make them better.

Cheers,

-Kevin Wentworth