php - MySQL "LOAD DATA INFILE" 空数据处理
问题描述
我正在使用下面的 php 脚本,但无法弄清楚它为什么不起作用。我确实得到了它在终端中没有错误处理的回声,但表仍然是空的。导致这种情况的代码是否有问题。我确认它也在目录中找到文件。希望缺少一些简单的东西。
<?php
try {
$inputfile1 = '////MainDirectory/SubDiretory/data.csv';
$table1 = 'database.testTable1';
$time1 = microtime(true);
require_once("configFile.php"); //this changes the below as now this is the reference point
$mysql_host = DB_HOST;
$mysql_database = DB_NAME;
$mysql_username = DB_USER;
$mysql_password = DB_PASS;
$db = new PDO("mysql:host=$mysql_host; dbname=$mysql_database", $mysql_username, $mysql_password);
// set the PDO error mode to exception
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$db->exec("SET CHARACTER SET utf8"); // Sets encoding UTF-8
} catch (Exception $e) {
die("Unable to connect: " . $e->getMessage());
}
try {
// Return errors
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// Begin transaction
$db->beginTransaction();
if(file_exists($inputfile1)) {
$Found = 'found';
// Query D2 Load Temp table
$tempA2 = $db->prepare("LOAD DATA INFILE :inputfile1
INTO TABLE $table1
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '\"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(column1, column2, column3);");
$tempA2->bindParam(':inputfile1', $inputfile1);
$tempA2 -> execute();
}
} catch (Exception $e) {
// If transaction fail, use checkpoint and rollback
$db->rollBack();
echo "Conversion failed: " . $e->getMessage().'<br />';
file_put_contents('PDOErrors.txt', $e->getMessage(), FILE_APPEND);
}
//time of script
//sleep(1);
$timeC = microtime(true) - $_SERVER["REQUEST_TIME_FLOAT"];
echo "Full Process Time: {$timeC} $Found";
?>
CSV 格式:
"HeaderData1","HeaderData2","HeaderData3","HeaderData4","HeaderData5"
"DataA","DataB","DataC"
"Data2","Data2","Data2"
MySQL 表格式
id INT PK with AI and NN
column1 Varchar
column2 Varchar
column3 Varchar
感谢您的任何帮助
解决方案
推荐阅读
- jquery - 从 jQuery UI 对话框调用的引导模式:超出最大调用堆栈大小
- wordpress - 相关产品部分未显示缩略图和价格
- javascript - 从 JSON 中过滤特定元素
- php - 如何通过wordpress中的简码在单页上显示所有推荐?
- laravel - 如何在 laravel 队列中捕获超时异常
- javascript - 如何知道存储在内存中的数组引用值?
- java - 如何处理套接字断开和心跳消息?
- html - 如何处理发布请愿书中的多个文件以恢复它们并在视图中显示它们?
- python - 如何在具有两个条件的熊猫数据框中添加新列?
- android - 为什么 'shouldShowRequestPermissionRationale' 与 Activity 相关?