php - 从用双引号字段括起来的 cvs 加载数据 INFILE

Question

所以，我明白了。只是尝试通过 PHP PDO 驱动程序将 csv 文件插入 MySQL:

<?php
$databasehost = "localhost";
$databasename = "db";
$databasetable = "table";
$socketPath = "/home/mysql/mysql.sock";
$databaseusername="user";
$databasepassword = "pass";
$fieldseparator = ",";
$fieldenclosed = '"';
$lineseparator = "\r\n";
$csvfile = "file.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;unix_socket=$socketPath",
        $databaseusername, $databasepassword,
        array(
            PDO::MYSQL_ATTR_LOCAL_INFILE => true,
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
        )
    );
} 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).", ENCLOSED BY ". $pdo->quote($fieldenclosed)."
    LINES TERMINATED BY ".$pdo->quote($lineseparator)." IGNORE 1 LINES;");

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

?>

csv 文件是这样的(逗号分隔并用双引号括起来)。

"X410","","4114068500","000010","04/15/2014","04/16/2015"
"X410","","4220521243","000030","04/08/2014","04/08/2015"
"X410","","4130003659","000010","04/02/2014","04/05/2014"
"X410","","4220524277","000010","04/08/2014","04/08/2015"
"X410","","4114038136","000010","04/07/2014","04/07/2015"
"X410","","4130003594","000110","03/14/2014","03/14/2015"
"X410","","4130003675","000010","04/04/2014","04/04/2015"
"X410","","4130003623","000010","03/12/2014","03/12/2015"
"X410","","4130003679","000010","04/09/2014","04/09/2015"
"X410","","4130003679","000020","04/09/2014","04/09/2015"

ENCLOSED BY 部分给我带来了麻烦，我已经完成了作业并尝试了 $fieldenheld = '\"\"', $fieldenclosure = "\"\""，有或没有 $pdo->quote($fieldenheld) 以及我在其他地方想到或发现的任何其他奇怪的 concat 内容具有类似问题的主题。

MySQL 抛出此错误:

PHP Fatal error:  Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n' IGNORE 1 LINES' at line 2' in /root/csvUpload.php:32

我在 CentOS 6.4 服务器上运行 MySQL 5.1。

有什么想法吗？

Answer 1

ENCLOSED BY 之前不应有逗号。应该是:

$fieldenclosed = '"';

只是一个单引号 - 它是应该位于字段开头和结尾的字符。

您还输错了变量名称，没有结束字符串并连接调用 $pdo->quote。

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