How to remove or avoid inserting column headers when importing a separated value file into a database using PHP?

Question

I have semicolon-separated .dat file and I want to read that file and store its contents into a database.

Structure of .dat file:

PARTYID;PARTYCODE;CONNECTION
256;319;234
879;435;135

SQL to import the .dat file:

     $sql_qry = "INSERT INTO DatTable (PARTYID,PARTYCODE,CONNECTIONID) 
                 VALUES ('$data[0]','$data[1]','$data[2]')";
                $stmt = $this->connection->prepare($sql_qry);
                $stmt->execute();
                $this->checkForErrors($stmt);

Now if I run the script than Db structure looks like:

  PARTYID PARTYCODE CONNECTION
------------------------------
1 PARTYID PARTYCODE CONNECTION
2 256     319       234
3 879     435       135

Obviously, I don't need the column headers (the PARTYID PARTYCODE CONNECTION row) in the table... So how should I remove them?

Interesting Answer:

Just Insert fgetcsv($fp, 1000, ","); at the first line and while loop will start from second line.

Answer 1

You have two solutions :

• You can either not use the first line of your .dat file
  • I suppose you are reading it line by line...
  • I so, just don't use the first line (you can use a variable as a counter to know if you are on the first line or not)
• Or you could test if the current line contains only integers/numbers before inserting the data to the database.

Here is a portion of code that could serve as a starting point, if you choose the second solution :

if (in_numeric($data[0]) && is_numeric($data[1]) && is_numeric($data[2])) {
    $sql_qry = "INSERT INTO DatTable (DF_PARTY_ID,DF_PARTY_CODE,DF_CONNECTION_ID) 
                 VALUES ('$data[0]','$data[1]','$data[2]')";
    $stmt = $this->connection->prepare($sql_qry);
    $stmt->execute();
    $this->checkForErrors($stmt);
}

Also, note that you are using prepare and execute, which seem to indicate you are trying to use prepared statements.

When using prepared statements, you should not do like you are doing ; you should :

• One and only one time : prepare the statement, using placeholders for the data
• For each line, bind the values
  • and execute the statement

You should not :

• Prepare the statement for each line
• Inject your data into the SQL query, instead of using placeholders.

Which means your code should look a bit like this (not tested, so you might have to change a few things) :

// First, prepare the statement, using placeholders
$query = "INSERT INTO DatTable (DF_PARTY_ID,DF_PARTY_CODE,DF_CONNECTION_ID) 
          VALUES (:party_id, :party_code, :connection_id)";
$stmt = $this->connection->prepare($query);

if (in_numeric($data[0]) && is_numeric($data[1]) && is_numeric($data[2])) {
    // Then, for each line : bind the parameters
    $stmt->bindValue(':party_id', $data[0], PDO::PARAM_INT);
    $stmt->bindValue(':party_code', $data[1], PDO::PARAM_INT);
    $stmt->bindValue(':connection_id', $data[2], PDO::PARAM_INT);

    // And execute the statement
    $stmt->execute();

    $this->checkForErrors($stmt);
}

Answer 2

Enforce inserting only on the right datatype.

if(is_numeric($data[0])){ ... your code ... }

More correctly, you should be enforcing that the columns don't allow insertion of bad data at the database level, so your listed columns should be of integer type since they're ids, not of whatever type they are currently (varchar, perhaps). And you could then handle errors appropriately, e.g. by ignoring and discarding inappropriate data.

Answer 3

This is a common task done daily by DBAs. It can be done in mysql with the LOAD command. No need to use PHP.

http://dev.mysql.com/doc/refman/5.1/en/load-data.html

LOAD DATA LOCAL INFILE 'file.dat' 
INTO TABLE table_name
FIELDS TERMINATED BY ';' 
LINES TERMINATED BY '\n' 
(PARTYID, PARTYCODE, CONNECTIONID)
IGNORE 1 LINES;