Cruz Cruz - 1 year ago 327
SQL Question

Symfony : Doctrine data fixture : how to handle large csv file?

I am trying to insert (in a mySQL database) datas from a "large" CSV file (3Mo / 37000 lines / 7 columns) using doctrine data fixtures.

The process is very slow and at this time I could not succeed (may be I had to wait a little bit more).

I suppose that doctrine data fixtures are not intended to manage such amount of datas ? Maybe the solution should be to import directly my csv into database ?

Any idea of how to proceed ?

Here is the code :


namespace FBN\GuideBundle\DataFixtures\ORM;

use Doctrine\Common\DataFixtures\AbstractFixture;
use Doctrine\Common\DataFixtures\OrderedFixtureInterface;
use Doctrine\Common\Persistence\ObjectManager;
use FBN\GuideBundle\Entity\CoordinatesFRCity as CoordFRCity;

class CoordinatesFRCity extends AbstractFixture implements OrderedFixtureInterface
public function load(ObjectManager $manager)
$csv = fopen(dirname(__FILE__).'/Resources/Coordinates/CoordinatesFRCity.csv', 'r');

$i = 0;

while (!feof($csv)) {
$line = fgetcsv($csv);

$coordinatesfrcity[$i] = new CoordFRCity();


$this->addReference('coordinatesfrcity-'.$i, $coordinatesfrcity[$i]);

$i = $i + 1;



public function getOrder()
return 1;

Answer Source

Two rules to follow when you create big batch imports like this:

  • Disable SQL Logging: ($manager->getConnection()->getConfiguration()->setSQLLogger(null);) to avoid huge memory loss.

  • Flush and clear frequently instead of only once at the end. I suggest you add if ($i % 25 = 0) { $manager->flush(); $manager->clear() } inside your loop, to flush every 25 INSERTs.

EDIT: One last thing I forgot: don't keep your entities inside variables when you don't need them anymore. Here, in your loop, you only need the current entity that is being processed, so don't store previous entity in a $coordinatesfrcity array. This might lead you to memory overflow if you keep doing that.