CSV到数据库与php [英] CSV to database with php
问题描述
我有一个问题,将数据从.csv文件导入到ms access数据库。
I have a little issue with importing data from .csv file to "ms access" database.
<form action="index.php" method="post" enctype="multipart/form-data">
<input type="file" name="csv"/>
<input type="submit" name="submit"/>
</form>
<?php
if (isset($_POST['submit'])) {
$i=0;
require "connection.php";
if (is_uploaded_file($_FILES['csv']['tmp_name'])) {
echo "<h3>" . "File ". $_FILES['csv']['name'] ." uploaded successfully." . "</h3>";
}
//Import uploaded file to Database
$handle = fopen($_FILES['csv']['tmp_name'], "r");
$import=$db->prepare("INSERT INTO adherence(
dateandtime,
lastname,
paidtime,
approvedtime,
notadhering) VALUES(
?,?,?,?,?)");
while (($data = fgetcsv($handle, 1000, "\t", "'")) !== FALSE) {
if($i>0) {
$data = str_replace('"', '', $data);
$myDate = date("Y/m/d",strtotime(str_replace('/','-',$data[0])));
$import->bindParam(1, $myDate, PDO::PARAM_STR);
$import->bindParam(2, $data[1], PDO::PARAM_STR);
$import->bindParam(3, $data[2], PDO::PARAM_STR);
$import->bindParam(4, $data[3], PDO::PARAM_STR);
$import->bindParam(5, $data[4], PDO::PARAM_STR);
$import->execute();
}
$i++;
}
/*$removal=$db->prepare("delete FROM adherence WHERE approvedtime = '0' OR notadhering IS NULL");
$removal->execute();*/
fclose($handle);
echo 'IMPORTED' ;
}
CSV档案:
导入结果:
我没有线索,为什么我的文件没有被导入,什么是这个随机日期。请协助!
I have no clue, why my file is not being imported, and what is this random date. Please assist!
EDIT:
Var_dump:
编辑2
<form action="index.php" method="post" enctype="multipart/form-data">
<input type="file" name="csv"/>
<input type="submit" name="submit"/>
</form>
<?php
if (isset($_POST['submit'])) {
$i=0;
require "connection.php";
if (is_uploaded_file($_FILES['csv']['tmp_name'])) {
echo "<h3>" . "File ". $_FILES['csv']['name'] ." uploaded successfully." . "</h3>";
}
//Import uploaded file to Database
$handle = fopen($_FILES['csv']['tmp_name'], "r");
$import=$db->prepare("INSERT INTO adherence(
dateandtime,
lastname,
paidtime,
approvedtime,
notadhering) VALUES(
?,?,?,?,?)");
while (($data = fgetcsv($handle, 1000, "\t", "'")) !== FALSE) {
if($i>0) {
$data = str_replace('"', '', $data);
$myDate = date("Y-m-d H:i",strtotime($data[0]));
$import->bindParam(1, $myDate, PDO::PARAM_STR);
$import->bindParam(2, $data[1], PDO::PARAM_STR);
$import->bindParam(3, $data[2], PDO::PARAM_STR);
$import->bindParam(4, $data[3], PDO::PARAM_STR);
$import->bindParam(5, $data[4], PDO::PARAM_STR);
$import->execute();
var_dump($data);
}
$i++;
}
/*$removal=$db->prepare("delete FROM adherence WHERE approvedtime = '0' OR notadhering IS NULL");
$removal->execute();*/
fclose($handle);
echo 'IMPORTED' ;
}
EDIT 3
if (isset($_POST['submit'])) {
$i=0;
require "connection.php";
if (is_uploaded_file($_FILES['csv']['tmp_name'])) {
echo "<h3>" . "File ". $_FILES['csv']['name'] ." uploaded successfully." . "</h3>";
}
//Import uploaded file to Database
$handle = fopen($_FILES['csv']['tmp_name'], "r");
$import=$db->prepare("INSERT INTO adherence(
dateandtime,
lastname,
paidtime,
approvedtime,
notadhering)
VALUES(:dateandtime, :lastname, :paidtime, :approvedtime, :notadhering)");
while (($data = fgetcsv($handle, 1000, "\t", "'")) !== FALSE) {
if($i>0) {
$myDate = date("Y/m/d",strtotime(str_replace('/','-',$data[0])));
$import->execute(array(':dateandtime'=> $myDate, ':lastname'=> $data[1], ':paidtime'=> $data[2], ':approvedtime'=> $data[3], ':notadhering'=> $data[4]));
}
$i++;
}
/*$removal=$db->prepare("delete FROM adherence WHERE approvedtime = '0' OR notadhering IS NULL");
$removal->execute();*/
fclose($handle);
echo 'IMPORTED' ;
}
EDIT 4 FULL CODE
EDIT 4 FULL CODE
<form action="index.php" method="post" enctype="multipart/form-data">
<input type="file" name="csv"/>
<input type="submit" name="submit"/>
</form>
<?php
if (isset($_POST['submit'])) {
$i=0;
require "connection.php";
if (is_uploaded_file($_FILES['csv']['tmp_name'])) {
echo "<h3>" . "File ". $_FILES['csv']['name'] ." uploaded successfully." . "</h3>";
}
//Import uploaded file to Database
$handle = fopen($_FILES['csv']['tmp_name'], "r");
try {
$import= $db->prepare("INSERT INTO adherence(
dateandtime,
lastname,
paidtime,
approvedtime,
notadhering) VALUES(
?,?,?,?,?)");
$i = 0;
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
if($i > 0) {
$data = str_replace('"', '', $data);
$import->bindParam(1, $data[0], PDO::PARAM_STR);
$import->bindParam(2, $data[1], PDO::PARAM_STR);
$import->bindParam(3, $data[2], PDO::PARAM_STR);
$import->bindParam(4, $data[3], PDO::PARAM_STR);
$import->bindParam(5, $data[4], PDO::PARAM_STR);
$import->execute();
}
$i++;
}
}
catch(PDOException $e) {
echo $e->getMessage()."\n";
}}
连接正在工作,它是.MDB
Connection is working. It is .MDB
推荐答案
您的根本问题是您不要指定逗号分隔符 fgetcsv()
函数。结果,所有的数据被推入一个列 date [0]
/ JET引擎无法转换为第一列的日期/时间,全部为零,这在
Your fundamental issue is you do not specify the comma delimiter in the fgetcsv()
function. As a result, all the data pushed into one column, date[0]
and the Access ACE/JET engine unable to convert to date/time of the first column rendered all to zero which in unix time begins at 1/1/1970.
另外,请考虑使用 try / catch
捕获PDO异常。
Also, consider using try/catch
to catch PDO exceptions.
try {
$import= $dbh->prepare("INSERT INTO adherence(
dateandtime,
lastname,
paidtime,
approvedtime,
notadhering) VALUES(
?,?,?,?,?)");
$i = 0;
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
if($i > 0) {
$data = str_replace('"', '', $data);
$import->bindParam(1, $data[0], PDO::PARAM_STR);
$import->bindParam(2, $data[1], PDO::PARAM_STR);
$import->bindParam(3, $data[2], PDO::PARAM_STR);
$import->bindParam(4, $data[3], PDO::PARAM_STR);
$import->bindParam(5, $data[4], PDO::PARAM_STR);
$import->execute();
}
$i++;
}
}
catch(PDOException $e) {
echo $e->getMessage()."\n";
}
对于日期,MS Access日期/时间字段遵循当前CPU的语言时钟,例如 MM / DD / YYYY
(US)或 DD / MM / YYYY
(UK)等。它不遵循 YYYY-MM-DD
或其他RDMS的日期/时间格式。所以没有必要转换,因为你的csv已经对齐到Access'格式。正在说,您可能会收到警告:
As for dates, MS Access date/time field follows the current CPU's language clock such as MM/DD/YYYY
(US) or DD/MM/YYYY
(UK), etc. It does not follow the date/time format of other RDMS's of YYYY-MM-DD
or such variants. So there is no need to convert since your csv aligned already to Access' format. With that being said, you may receive a warning:
SQLSTATE[22018]: Invalid character value for cast specification: -3030
但是,根据我的测试,数据库更新了所有行。通常,你会连接#
周围的日期字符串,我试图无法使用你的代码,但可能在你的绑定参数过程,字符串类型不理想地包装主题标签。
However, according to my tests, the database updated all rows correctly. Normally, you would concatenate the #
around date strings which I tried to no avail with your code but possibly in your bind parameters process, string types do not wrap ideally with the hashtags.
这篇关于CSV到数据库与php的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!