Reading Excel Files And Loading Into The Database

Yesterday I spent most of the day reading everything I could find on the process of reading an excel file.

I have worked with writing models out to excel from cgridview pages, it was pretty easy.

Reading turned out to be a bit more challenging. I got it to work using phpexcel extension but found the pathing and

the setup of the extension to be a bit fragile. The auto load feature of PHPExcel and the pathing seemed to work

one minute and break the next.

It was trying to use FILE to read and write when I was using a different directory from my application/module/view…

I have seen other people try to simplify this process and I now understand why. It should not be that hard.

I don’t feel I have the expertise to create a new module or extend this PHPExcel module to clean it up, but I would

welcome the opportunity to work with anyone to do so.

So now I have my file being read and I display it in the view. It is a memory hog (21MB to load a 60kb file) but it runs pretty fast. I have not played with it to see how it scales. I have experienced some problems with writing excel files from within Yii with just 3000 rows / 20 columns…but that is another thread.

I need to create a few features to make it user friendly.

What I am looking for is best practice to load data into a table.

Is there any advantage to creating an array and doing an insert versus updating each cell?

$model = new Model();

for each row





versus using sql command

$command->insert(‘table’, array(





The only "downside" I can think of that validation rules will be ran against the cols, which might take some time.

I went the route of using SQL Insert after building the string.

Insert into (col,col…) values (1,2,3),(4,5,6),(7,8,9)…

Worked pretty fast. Acceptable anyway.

I did have a problem with a private variable declaration…just didn’t work with my code…saw it in an example.

Good stuff overall.