fgetcsv with MySQL : weird error

Question

I'm trying to import a relatively big csv file into my database (7500+ lines, 43 columns) and i'm running into some strange error.

$file = fopen(APPLICATION_PATH."/../docs/data.csv", "r");

     while (($emapData = fgetcsv($file, 100000, ";")) !== FALSE)
     {


        $dataTable->addline($emapData[0] , .. , emapdata[42]);

     }
     fclose($file);

When I'm doing it this way, my file is loaded two times into my database (15k+ lines).

However, if I remove the auto-increment propriety from the primary key and I add it manually (I also change my addLine method):

$file = fopen(APPLICATION_PATH."/../docs/data.csv", "r");
     $id=0;
     while (($emapData = fgetcsv($file, 100000, ";")) !== FALSE)
     {

        $dataTable->addline($id,$emapData[0] , .. , emapdata[42]);
        $id++;
     }
     fclose($file);

Sometimes it works sometimes it throws an integrity error (primary key allready existing).

In both cases, I really don't understand what's going on.

EDIT :

my addLines method (quite long sorry) :

1st case

public function addLine($idtopo, $oms, $voie_oms, $och_otu, $etat_och_otu, $fameqpta_och_otu, $fameqptb_och_otu, $voie_och_otu, $odu4, $etat_odu4, $fameqpta_odu4, $fameqptb_odu4, $voie_odu4, $odu3, $etat_odu3, $fameqpta_odu3, $fameqptb_odu3, $voie_odu3, $odu2, $etat_odu2, $fameqpta_odu2, $fameqptb_odu2, $voie_odu2, $odu1, $etat_odu1, $fameqpta_odu1, $fameqptb_odu1, $voie_odu1, $cat_supporte, $supporte, $etat_supporte, $produit, $utilisation, $com1_och_otn, $com2_och_otn, $com1_odu2, $com2_odu2, $com1_odu1, $com2_odu1, $com1_supporte, $com2_supporte, $srlg){

    $db= Zend_Db_Table::getDefaultAdapter();

    $sql="INSERT INTO `data`  (`idtopo`, `oms`, `voie_oms`, `och_otu`, `etat_och_otu`, `fameqpta_och_otu`, `fameqptb_och_otu`, `voie_och_otu`, `odu4`, `etat_odu4`, `fameqpta_odu4`, `fameqptb_odu4`, `voie_odu4`, `odu3`, `etat_odu3`, `fameqpta_odu3`, `fameqptb_odu3`, `voie_odu3`, `odu2`, `etat_odu2`, `fameqpta_odu2`, `fameqptb_odu2`, `voie_odu2`, `odu1`, `etat_odu1`, `fameqpta_odu1`, `fameqptb_odu1`, `voie_odu1`, `cat_supporte`, `supporte`, `etat_supporte`, `produit`, `utilisation`, `com1_och_otn`, `com2_och_otn`, `com1_odu2`, `com2_odu2`, `com1_odu1`, `com2_odu1`, `com1_supporte`, `com2_supporte`, `srlg`) values (           
                                                          '".$idtopo."',
                                                          '".$oms."',
                                                          '".$voie_oms."',
                                                          '".$och_otu."',
                                                          '".$etat_och_otu."',
                                                          '".$fameqpta_och_otu."',
                                                          '".$fameqptb_och_otu."',
                                                          '".$voie_och_otu."',
                                                          '".$odu4."',
                                                          '".$etat_odu4."',
                                                          '".$fameqpta_odu4."',
                                                          '".$fameqptb_odu4."',
                                                          '".$voie_odu4."',
                                                          '".$odu3."',
                                                          '".$etat_odu3."',
                                                          '".$fameqpta_odu3."',
                                                          '".$fameqptb_odu3."',
                                                          '".$voie_odu3."',
                                                          '".$odu2."',
                                                          '".$etat_odu2."',
                                                          '".$fameqpta_odu2."',
                                                          '".$fameqptb_odu2."',
                                                          '".$voie_odu2."',
                                                          '".$odu1."',
                                                          '".$etat_odu1."',
                                                          '".$fameqpta_odu1."',
                                                          '".$fameqptb_odu1."',
                                                          '".$voie_odu1."',
                                                          '".$cat_supporte."',
                                                          '".$supporte."',
                                                          '".$etat_supporte."',
                                                          '".$produit."',
                                                          '".$utilisation."',
                                                          '".$com1_och_otn."',
                                                          '".$com2_och_otn."',
                                                          '".$com1_odu2."',
                                                          '".$com2_odu2."',
                                                          '".$com1_odu1."',
                                                          '".$com2_odu1."',
                                                          '".$com1_supporte."',
                                                          '".$com2_supporte."',
                                                          '".$srlg."'
                                                        )";


  $db->exec($sql);
  $db->closeConnection();


}

2nd case

public function addLine($id,$idtopo, $oms, $voie_oms, $och_otu, $etat_och_otu, $fameqpta_och_otu, $fameqptb_och_otu, $voie_och_otu, $odu4, $etat_odu4, $fameqpta_odu4, $fameqptb_odu4, $voie_odu4, $odu3, $etat_odu3, $fameqpta_odu3, $fameqptb_odu3, $voie_odu3, $odu2, $etat_odu2, $fameqpta_odu2, $fameqptb_odu2, $voie_odu2, $odu1, $etat_odu1, $fameqpta_odu1, $fameqptb_odu1, $voie_odu1, $cat_supporte, $supporte, $etat_supporte, $produit, $utilisation, $com1_och_otn, $com2_och_otn, $com1_odu2, $com2_odu2, $com1_odu1, $com2_odu1, $com1_supporte, $com2_supporte, $srlg){

    $db= Zend_Db_Table::getDefaultAdapter();

    $sql="INSERT INTO `data` (`id`, `idtopo`, `oms`, `voie_oms`, `och_otu`, `etat_och_otu`, `fameqpta_och_otu`, `fameqptb_och_otu`, `voie_och_otu`, `odu4`, `etat_odu4`, `fameqpta_odu4`, `fameqptb_odu4`, `voie_odu4`, `odu3`, `etat_odu3`, `fameqpta_odu3`, `fameqptb_odu3`, `voie_odu3`, `odu2`, `etat_odu2`, `fameqpta_odu2`, `fameqptb_odu2`, `voie_odu2`, `odu1`, `etat_odu1`, `fameqpta_odu1`, `fameqptb_odu1`, `voie_odu1`, `cat_supporte`, `supporte`, `etat_supporte`, `produit`, `utilisation`, `com1_och_otn`, `com2_och_otn`, `com1_odu2`, `com2_odu2`, `com1_odu1`, `com2_odu1`, `com1_supporte`, `com2_supporte`, `srlg`) values (             ".$id.",
                                                          '".$idtopo."',
                                                          '".$oms."',
                                                          '".$voie_oms."',
                                                          '".$och_otu."',
                                                          '".$etat_och_otu."',
                                                          '".$fameqpta_och_otu."',
                                                          '".$fameqptb_och_otu."',
                                                          '".$voie_och_otu."',
                                                          '".$odu4."',
                                                          '".$etat_odu4."',
                                                          '".$fameqpta_odu4."',
                                                          '".$fameqptb_odu4."',
                                                          '".$voie_odu4."',
                                                          '".$odu3."',
                                                          '".$etat_odu3."',
                                                          '".$fameqpta_odu3."',
                                                          '".$fameqptb_odu3."',
                                                          '".$voie_odu3."',
                                                          '".$odu2."',
                                                          '".$etat_odu2."',
                                                          '".$fameqpta_odu2."',
                                                          '".$fameqptb_odu2."',
                                                          '".$voie_odu2."',
                                                          '".$odu1."',
                                                          '".$etat_odu1."',
                                                          '".$fameqpta_odu1."',
                                                          '".$fameqptb_odu1."',
                                                          '".$voie_odu1."',
                                                          '".$cat_supporte."',
                                                          '".$supporte."',
                                                          '".$etat_supporte."',
                                                          '".$produit."',
                                                          '".$utilisation."',
                                                          '".$com1_och_otn."',
                                                          '".$com2_och_otn."',
                                                          '".$com1_odu2."',
                                                          '".$com2_odu2."',
                                                          '".$com1_odu1."',
                                                          '".$com2_odu1."',
                                                          '".$com1_supporte."',
                                                          '".$com2_supporte."',
                                                          '".$srlg."'
                                                        )";


  $db->exec($sql);
  $db->closeConnection();


}

Basically I just add the id parameter and I disable the auto-increment option in my table

It seems the error is in your addLine method. So you should let us look at this method in both forms. — VMai, Jul 17 '14 at 14:06
What is your PRIMARY KEY? It contains only the id or other fields too? — peppelauro, Jul 17 '14 at 14:44
I would be tempted to load it into a temporary table first. Check / validate the data in the temporary table. Then load it into the 'real' table. Advantages: 'live' tables updates will not fail due to 'less than good' data. Error reporting has all the available information available. Cost: separate pass of the data and the extra space. — Ryan Vincent, Jul 17 '14 at 17:01
Sorry for trivial question, but do you truncate the table before the running of the procedure? — peppelauro, Jul 22 '14 at 09:27

score 0 · Accepted Answer · answered Jul 24 '14 at 08:30

0

You can try to use the insert method instead of using exec in the addLine function:

$data = array(
'idtopo' => $idtopo
, 'oms' => $oms
, 'voie_oms' => $voie_oms
...
);

$db->insert('data', $data);

answered Jul 24 '14 at 08:30

peppelauro

109
1
4

I do not understand why , but it works like a charm. Thank you very much ! – slayne Jul 24 '14 at 14:38

fgetcsv with MySQL : weird error

1 Answers1