php - 使用 PHP 将 CSV 导入 MySQL 时出错

Question

我正在尝试使用此代码将 csv 文件导入到我的 MySQL 数据库中:

... 感谢您解决了这个问题!! ....

我做错了什么？

<小时/>

伙计们，我可以解决这个问题，但现在...我有另一个问题，这是现在的代码:

<?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";

?>

当我尝试运行它时，只在表格上插入一行...

Loaded a total of 1 records from this csv file.

当我的 csv 文件有大约 5000 个条目时，为什么它只得到一行...

Answer 1

发现here

您应该在查询中设置CHARACTER SET UTF8。

示例:

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