Error Importing CSV to MySQL with PHP

Question

I am trying to use this code to import a csv file into my MySQL database:

... Thanks for solved that question!! ....

What am I doing wrong?

Guys I could solved that problem but now... I have an other problem, this is the code now:

<?php
$databasehost = "localhost"; 
$databasename = "cauctti"; 
$databasetable = "sample"; 
$databaseusername="root"; 
$databasepassword = "toor"; 
$fieldseparator = ";"; 
$lineseparator = "\r|n";
$csvfile = "Reporting.csv";

if(!file_exists($csvfile)) {
    die("File not found. Make sure you specified the correct path.");
}

try {
    $pdo = new PDO("mysql:host=$databasehost;dbname=$databasename", 
        $databaseusername, $databasepassword,
        array(
            PDO::MYSQL_ATTR_LOCAL_INFILE => true,
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"
        )
    );
} catch (PDOException $e) {
    die("database connection failed: ".$e->getMessage());
}

$affectedRows = $pdo->exec("
      LOAD DATA LOCAL INFILE ".$pdo->quote($csvfile)." INTO TABLE `$databasetable`
      FIELDS TERMINATED BY ".$pdo->quote($fieldseparator)."
      LINES TERMINATED BY ".$pdo->quote($lineseparator));

echo "Loaded a total of $affectedRows records from this csv file.\n";

?>

When I try to run it, only insert one row on the table...

Loaded a total of 1 records from this csv file.

Why it only gets one row when my csv file have like 5.000 entries...

Got your answer there I think: http://stackoverflow.com/questions/26256421/sql-load-data-infile-utf8-issue — RR404, Feb 22 '16 at 15:12
Should be something like : LOAD DATA LOCAL INFILE ".$pdo->quote($csvfile)." INTO TABLE `$databasetable` FIELDS TERMINATED BY ".$pdo->quote($fieldseparator)." LINES TERMINATED BY ".$pdo->quote($lineseparator))." CHARACTER SET UTF8"; — Matheno, Feb 22 '16 at 15:26

score 0 · Answer 1 · edited May 23 '17 at 10:28

0

As found here

You should set CHARACTER SET UTF8 in your query.

Example:

LOAD DATA INFILE 'file'
IGNORE INTO TABLE table
CHARACTER SET UTF8
FIELDS TERMINATED BY ';'
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'

edited May 23 '17 at 10:28

Community

1
1

answered Feb 22 '16 at 15:30

Matheno

4,112
6
36
53

score 0 · Answer 2 · answered Feb 22 '16 at 16:08

Have a look here --> Here

<?php
$table    = "tableName";
$fileName = "States.csv";
$ignoreFirstRow = 1;
if (($handle = fopen($fileName, "r")) !== FALSE){
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) 
{
if($ignoreFirstRow != 1){
$sql = "insert into ".$table." values(";
$sql .= '"'.implode('","',$data).'"';
echo "".$sql.');';
$sql = "";
}
$ignoreFirstRow++;
}
fclose($handle);
}
?>

Error Importing CSV to MySQL with PHP

2 Answers2