Ignore CSV header before inserting into the database

Asked

Viewed 85 times

0

I created a method to upload clients via csv files. It is working perfectly the registration, however I need to ignore the first line, the header of the file, I am looking for but still not getting a solution. If anyone can suggest an idea.

  public function uploadDados(array $dados) {
       $this->dados = $dados;
       if(!empty($this->dados)){
          if(is_uploaded_file($_FILES['file']['tmp_name'])){
            $handle = fopen($_FILES['file']['tmp_name'], "r");
             while(($data = fgetcsv($handle, 1000, ";")) !== FALSE ){
                 if($this->verificaDados($data[0])) {
                    $query = $this->conn->prepare(" INSERT INTO tb_clients (id, codigo_cliente, nome_cliente, tentativas, data_venda, prioridade, data_limite, produto, status_id, timed_expirado) VALUES (null, :codigo_cliente, :nome_cliente, :tentativas, :data_venda, :prioridade, :data_limite, :produto, :status_id, :timed_expirado)");
                    $limite = $this->countDays($data[3]);
                    $query->bindParam(':codigo_cliente', $data[0]);
                    $query->bindParam(':nome_cliente', $data[1]);
                    $query->bindParam(':tentativas', $data[2]);
                    $query->bindParam(':data_venda', $data[3]);
                    $query->bindParam(':prioridade', $data[4]);
                    $query->bindParam(':data_limite', $limite);
                    $query->bindParam(':produto', $data[6]);
                    $query->bindParam(':status_id', $data[7]);
                    $query->bindParam(':timed_expirado', $data[8]);
                    $query->execute();
                    $this->resultado = TRUE;
                  } 
             }
            fclose($handle);
         }
      }
    }
php

  • In which line of your code you start going through the file line by line?

  • $this->verified($date[0]) // Checks if the client does not exist in the database and returns true

  • And what the verificaDados does currently if, from what he commented, is not checking the data?

  • checked Data makes a select in the database before entering a new client if the number of affected rows is greater than or equal to 1 then returns FALSE. Returning false does not insert the record that already exists in the database.

  • I already say that this is a bad practice, affecting not only the performance but generating a race condition problem. Instead, seek to add the column as a single index in the database and handle errors during insertion.

1 answer

1


You can simply check with true and false:

 $primeiro = true;


 while(($data = fgetcsv($handle, 1000, ";")) !== FALSE ){
     if($this->verificaDados($data[0])) {

        //Se true (primeira vez) executa o "continue;" que pula para a proxima linha do CSV
        //Se false não entra no IF, após o primeiro loop irá setar como false
        if ($primeiro) {
            $primeiro = false;
            continue;
        }

        $query = $this->conn->prepare(" INSERT INTO tb_clients (id, codigo_cliente, nome_cliente, tentativas, data_venda, prioridade, data_limite, produto, status_id, timed_expirado) VALUES (null, :codigo_cliente, :nome_cliente, :tentativas, :data_venda, :prioridade, :data_limite, :produto, :status_id, :timed_expirado)");
        $limite = $this->countDays($data[3]);
        $query->bindParam(':codigo_cliente', $data[0]);
        $query->bindParam(':nome_cliente', $data[1]);
        $query->bindParam(':tentativas', $data[2]);
        $query->bindParam(':data_venda', $data[3]);
        $query->bindParam(':prioridade', $data[4]);
        $query->bindParam(':data_limite', $limite);
        $query->bindParam(':produto', $data[6]);
        $query->bindParam(':status_id', $data[7]);
        $query->bindParam(':timed_expirado', $data[8]);
        $query->execute();
        $this->resultado = TRUE;
      } 
 }

You can also check if the client code is in a valid format within the function itself verificaDados(), for example if it only accepts numbers then when the header is probably not numerical, something like:

private verificaDados($codigo) {
    if (ctype_digit(trim($codigo)) === false) {
        return false;
    }

    ... código que verifica se já existe no banco de dados ...
}

And this way I wouldn’t even need this part:

if ($primeiro) {
    $primeiro = false;
    continue;
}

Note: the function ctype_digit checks if they are numbers only, is similar to the is_numeric, but does not accept dot format or negative numbers, which is ideal for your "Ids", assuming you use numbers on them.

  • Perfect, it worked ! Thankful @Guilherme Nascimento

Browser other questions tagged php

You are not signed in. Login or sign up in order to post.