How can I import csv file to MySQL database more efficiently with PHP?

Question

I explain, I have a Symfony2 project and I need to import users via csv file in my database. I have to do some work on the datas before importing it in MySQL. I created a service for this and everything is working fine but it takes too much time to execute and slow my server if I give it my entire file. My files have usually between 500 and 1500 rows and I have to split my file in ~200 rows files and import one by one.

I need to handle related users that can be both in the file and/or in database already. Related users are usually a parent of a child.

Here is my simplified code :

$validator = $this->validator;

$members = array();
$children = array();
$mails = array();

$handle = fopen($filePath, 'r');
$datas = fgetcsv($handle, 0, ";");

while (($datas = fgetcsv($handle, 0, ";")) !== false) {

    $user = new User();

    //If there is a related user
    if($datas[18] != ""){
        $user->setRelatedMemberEmail($datas[18]);

        $relation = array_search(ucfirst(strtolower($datas[19])), UserComiti::$RELATIONSHIPS);
        if($relation !== false)
            $user->setParentRelationship($relation);
    }
    else {
        $user->setRelatedMemberEmail($datas[0]);
        $user->addRole ( "ROLE_MEMBER" );
    }

    $user->setEmail($mail);
    $user->setLastName($lastName);
    $user->setFirstName($firstName);
    $user->setGender($gender);
    $user->setBirthdate($birthdate);
    $user->setCity($city);
    $user->setPostalCode($zipCode);
    $user->setAddressLine1($adressLine1);
    $user->setAddressLine2($adressLine2);
    $user->setCountry($country);
    $user->setNationality($nationality);
    $user->setPhoneNumber($phone);

    //Entity Validation
    $listErrors = $validator->validate($user);

    //In case of errors
    if(count($listErrors) > 0) {
         foreach($listErrors as $error){
              $nbError++;
              $errors .= "Line " . $line . " : " . $error->getMessage() . "\n";
         }
   }

   else {
       if($mailParent != null)
            $children[] = $user;

       else{
            $members[] = $user;
            $nbAdded++;
       }
   }

   foreach($members as $user){
        $this->em->persist($user);
        $this->em->flush();
   }

   foreach($children as $child){

       //If the related user is already in DB
       $parent = $this->userRepo->findOneBy(array('username' => $child->getRelatedMemberEmail(), 'club' => $this->club));

       if ($parent !== false){

           //Check if someone related to related user already has the same last name and first name. If it is the case we can guess that this user is already created
           $testName = $this->userRepo->findByParentAndName($child->getFirstName(), $child->getLastName(), $parent, $this->club);

           if(!$testName){
                $child->setParent($parent);
                $this->em->persist($child);
                $nbAdded++;
           }
           else
                $nbSkipped++;
       }

       //Else in case the related user is neither file nor in database we create a fake one that will be able to update his profile later.
       else{

            $newParent = clone $child;
            $newParent->setUsername($child->getRelatedMemberEmail());
            $newParent->setEmail($child->getRelatedMemberEmail());
            $newParent->setFirstName('Unknown');

            $this->em->persist($newParent);
            $child->setParent($newParent);
            $this->em->persist($child);

            $nbAdded += 2;
            $this->em->flush();
        }
    }
}

It's not my whole service because I don't think the remaining would help here but if you need more information ask me.

What do you mean, it overloads your server? It doesn't sound that complicated. — jeroen
– jeroen, Commented Mar 1, 2017 at 15:32
Fast or do some work on the datas before importing. Pick one — Dimi
– Dimi, Commented Mar 1, 2017 at 15:33
it takes too much time to execute and slow my server. I edited — S. Dylan
– S. Dylan, Commented Mar 1, 2017 at 15:34
What is too much? Are you trying to do this using a browser? If you are, you should do it from the command line. And then at a time when the server isn't doing much. — jeroen
– jeroen, Commented Mar 1, 2017 at 15:35

Tim · Accepted Answer · 2017-03-01 20:06:00Z

1

While I do not heave the means to quantitatively determine the bottlenecks in your program, I can suggest a couple of guidelines that will likely significantly increase its performance.

Minimize the number of database commits you are making. A lot happens when you write to the database. Is it possible to commit only once at the end?
Minimize the number of database reads you are making. Similar to the previous point, a lot happens when you read from the database.

If after considering the above points you still have issues, determine what SQL the ORM is actually generating and executing. ORMs work great until efficiency becomes a problem and more care needs to go into ensuring optimal queries are being generated. At this point, becoming more familiar with the ORM and SQL would be beneficial.

You don't seem to be working with too much data, but if you were, MySQL alone supports reading CSV files.

The LOAD DATA INFILE statement reads rows from a text file into a table at a very high speed. https://dev.mysql.com/doc/refman/5.7/en/load-data.html

You may be able to access this MySQL specific feature through your ORM, but if not, you would need to write some plain SQL to utilize it. Since you need to modify the data you are reading from the CSV, you would likely be able to do this very, very quickly by following these steps:

Use LOAD DATA INFILE to read the CSV into a temporary table.
Manipulate the data in the temporary table and other tables as required.
SELECT the data from the temporary table into your destination table.

answered Mar 1, 2017 at 20:06

Tim

87510 silver badges20 bronze badges

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

1 Comment

S. Dylan Over a year ago

Thank you for your time. My problem to minimize numbers of commits is that in order to persist my children I need parent to already be persisted. But maybe getting all users by club first and work with two arrays, this one and the one from the file, would be a great improvement. Don't why I didn't think of it before, seems obvious now :) . And I've read articles about LOAD DATA INFILE but as I have to work on datas first I forgot it. But I love the idea of using it with a temporary table and work datas here. I'll look this way. Thank you for your help !

serek · Accepted Answer · 2021-08-23 20:45:49Z

0

I know that it is very old topic, but some time ago I created a bundle, which can help import entities from csv to database. So maybe if someone will see this topic, it will be helpful for him.

https://github.com/jgrygierek/BatchEntityImportBundle https://github.com/jgrygierek/SonataBatchEntityImportBundle

answered Aug 23, 2021 at 20:45

serek

11311 bronze badges

Collectives™ on Stack Overflow

How can I import csv file to MySQL database more efficiently with PHP?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related