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 stackoverflow.com/questions/17875706/…, if it works for you then this can be closed as a duplicate. — Nigel Ren
– Nigel Ren, Commented Jul 8, 2019 at 6: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
– Bram Verstraten, Commented Jul 8, 2019 at 6:37

Meow Kim · Accepted Answer · 2019-07-08 06:35:36Z

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 8, 2019 at 6:35

Meow Kim

4845 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Meow Kim Over a year ago

BUT STILL inserting this data to DB will need 'for loop'. this cannot be at once, only performs by row

Meow Kim Over a year ago

As I said, inserting multiple data to DB cannot performs without loop

dee flor · Accepted Answer · 2019-07-08 08:06:18Z

0

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.

answered Jul 8, 2019 at 8:06

dee flor

691 silver badge9 bronze badges

Collectives™ on Stack Overflow

Upload Large Excel data into database without looping from PHP

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related