Upload Large Excel data into database without looping from PHP

Question

I have a .xls file which contains large number of rows, which I have to store to a DB. Now I'm reading each row in a loop and inserting into table. Is there any way, I can dump all rows to the table in a single shot? (with no loops?)

for ($row = 2; $row <= $highestRow; $row++) 
{ 

    $rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row,NULL,TRUE,FALSE);
    $entityName =$headings[0];
    $entityValue=$rowData[0];

    $rowData[0] = array_combine($headings[0], $rowData[0]);
    $num = count($rowData[0]);                                
    $excelDOB=$rowData[0]['date of birth'];
    $student = array(
                        'name'    =>  $name, 
                    );

    $this->common_model->insert_student_data($student);

}

I'm not a CI person, but a quick search gives https://stackoverflow.com/questions/17875706/how-to-create-codeigniter-batch-insert-array, if it works for you then this can be closed as a duplicate. — Nigel Ren, Jul 08 '19 at 06:02
Even if you find a method that does this in one go, internally it will still loop over each row. This is not the issue. The problem is that executing an insert query for each row is slow. Inserting multiple rows in one INSERT query is much faster in MySQL. — Bram Verstraten, Jul 08 '19 at 06:37

score 0 · Answer 1 · answered Jul 08 '19 at 06:35

0

You are using PHPExcel's rangeToarray() to get each single row. The range can be used by not only single row but also multiple rows.

$rowData = $sheet->rangeToArray('A1' . ':' . $highestColumn . $highestRow,NULL,TRUE,FALSE);

It means same

A1:J1
A2:J2
.
.
.
A1:J50

=>

A1:J50

answered Jul 08 '19 at 06:35

Meow Kim

445
4
14

BUT STILL inserting this data to DB will need 'for loop'. this cannot be at once, only performs by row – Meow Kim Jul 08 '19 at 06:39
As I said, inserting multiple data to DB cannot performs without loop – Meow Kim Jul 08 '19 at 07:22

score 0 · Answer 2 · answered Jul 08 '19 at 08:06

Instead of executing the query in Loops, You build Insert statements using loops and once loop is completed. Execute the query once. That way instead of executing 100/200/n number of queries all inserts will happen in 1 single query, Performance will be better.

Upload Large Excel data into database without looping from PHP

2 Answers2