Import PHP CSV file

Asked

Viewed 836 times

1

I have a CSV file with contacts. I want to import these contacts into a sqlite database. How can I read the file and insert it into the database?

php sqlite csv

1 answer

1


You can use the function fgetcsv.

Follows an example of use, assuming a table CONTATO(Nome, Funcao, Morada) and a line in the CSV of the type costa;Programador;Lisboa;:

<?php
if ($db = sqlite_open("c:/teste.db", 0666, $error)) {
   echo "Banco de dados aberto...";
} else {
   die ($error);
}

$row = 1;
if (($handle = fopen("teste.csv", "r")) !== FALSE) {
    while (($data = fgetcsv($handle, 1000, ";")) !== FALSE) {
        $num = count($data);
        echo "<p> $num campos na linha $row: <br /></p>\n";
        $row++;
        $query = "INSERT INTO CONTATO (Nome, Funcao, Morada) VALUES(";
        for ($c=0; $c < $num; $c++) {
            echo $data[$c] . "<br />\n";
            // Inserindo os dados no banco.
            $query .= "'" . mysql_real_escape_string($data[$c]) . "'";
            if ($c+1 < $num) {
               $query .= ",";
            }
        }
        $query .= ")";
        sqlite_query($db, $query);
    }
    fclose($handle);
}
?>

  • I have a problem with the table. From Sqlite3::exec(): near "Affaires", this will be due to having special characters in the csv file, such as words "d'Affaires", d'exploitation?

  • Possibly. Can make a test with words without quotation marks?

  • Yes I did a test and the problem really comes from the quotes. How can I solve this?

  • You can use the function mysql_real_escape_string(), that treats words with quotes. I edited the answer to use the function.

  • Keep making the same mistake. And how can I separate by different columns in the table (Name, contact, address, etc), for example for this contact in csv: example;costa;Programmer;00000000;;Lisbon;CP;Portugal;;;;

  • Each index of the variable $data is a line field in CSV. I edited the answer with an example.

  • Remembering that the separator in the fields in my example is like ,. If in your file is as ;, you must change in function fgetcsv(). For example: fgetcsv($handle, 1000, ";")

  • And to change line? at the end of each contact, the last characters are ;;;;

  • And if a field in the CSV file is empty or missing. How can I fix this?

  • If you have the number of fields set you can change the variable $num by the number of fields per line. Hence it ignores the latter ;;;. When the empty field, you can test whether the variable $data[$c] is empty before inserting into the bank.

  • I used if($data[$c]='){$query .= " " ;} for if you have nothing, write blank. How can I exchange the variable for the field num?

  • On the line where $num = count($data); can put $num = 5; if they are 5 fields.

  • I have 13 fields, changed $num=13; and of the many errors, Undefined offset: 1,Undefined offset: 2, ...

Show 8 more comments

Browser other questions tagged php sqlite csv

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