CSV使用PHP / MySQL上传 [英] CSV Upload with PHP/MySQL
问题描述
我一直试图得到一个csv文件上传到我的数据库现在1周，我已经阅读umpteen教程，不知道我做错了，这是简单的代码，我太愚蠢的得到正确。任何帮助将是金！ ：）
if（isset（$ _ FILES ['file']））{
$ csv_file = $ _FILES ['file'] ['name'];
$ s
$ sql =<<< eof
LOAD DATA INFILE'$ csv_file'
INTO TABLE test_csv
终止'|'选项' '
LINES TERMINATED BY'\\\
'
（name，house，po）
eof;
$ result = $ dbh-> query（$ sql）;
}
echo $ csv_file'已成功加载';
？>
<！DOCTYPE html>
< html>
< head>
< title> CSV到MySQL通过PHP< / title> ;
< / head>
< body>
< form enctype =multipart / form-datamethod =POST>
& filetype =file>
< input type =submitvalue =Upload>
< / form>
< / body>
< / html>
下面是一个例子。
test.csv
名字，姓氏，年龄
Latheesan，Kanes，26
Adam，Smith，30
test.php
<？php
// Mini Config
$ csv_file ='test.csv';
$ delimiter ='，';
$ enclosure ='';
$ skip_first_row = true;
$ import_chunk = 250;
//解析CSV和生成导入查询
$ import_queries = array（）;
$ first_row_skipped = false;
if（（$ handle = fopen（$ csv_file，r））！== FALSE）{
while（（$ data = fgetcsv（$ handle，1000，$ delimiter，））！== FALSE）{
if（$ skip_first_row&！$ first_row_skipped）{
$ first_row_skipped = true;
继续;
}
列表（$ firstname，$ lastname，$ age）= $ data;
$ import_queries [] =INSERT INTO myTable（firstname，lastname，age）VALUES firstname'，'$ lastname'，$ age）;;
}
fclose（$ handle）;
}
//如果任何数据被解析，
if（sizeof（$ import_queries））
{
foreach（array_chunk（$ import_queries，$ import_chunk）as $ queries）
{
$ dbh-> query （implode（''，$ queries））;
}
}
？>
解析的查询将如下所示（如果您 print_r ）：
Array
（
[0] => INSERT INTO myTable（firstname，lastname，age）VALUES（'Latheesan'，'Kanes'，26）;
[1] => INSERT INTO myTable（firstname，lastname，age）VALUES（'Adam'，'Smith'，30）;
）
实际导入db有两个选项：
创建一个导入sql查询的集合并在批处理（array_chunk）中执行它 - 这意味着减少对您的数据库的查询。但是你可以看到，我不检查CSV中的值 - 即我相信我的数据源，不逃避任何东西 - 有点危险...你执行查询，一旦你通过转义值构建它 - 小缺点是它将在csv ....每行执行一个查询。
希望这有帮助。
I have been trying to get a csv file uploaded into my database now for 1 week, i have read umpteen tutorials and have no idea what i am doing wrong, this is the simple code that I am too stupid to get right. Any help will be golden! :)
if(isset($_FILES['file'])){
$csv_file = $_FILES['file']['name'];
$sql = <<<eof
LOAD DATA INFILE '$csv_file'
INTO TABLE test_csv
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(name,house,po)
eof;
$result = $dbh->query($sql);
}
echo $csv_file .' has successfully been loaded';
?>
<!DOCTYPE html>
<html>
<head>
<title>CSV to MySQL Via PHP</title>
</head>
<body>
<form enctype="multipart/form-data" method="POST">
<input name="file" type="file">
<input type="submit" value="Upload">
</form>
</body>
</html>
When ever I had to import a CSV into database table, i've always written my own csv parser / importer. It's quite simple.
Here's an example.
test.csv
Firstname,Lastname,Age
"Latheesan","Kanes",26
"Adam","Smith",30
test.php
<?php
// Mini Config
$csv_file = 'test.csv';
$delimiter = ',';
$enclosure = '"';
$skip_first_row = true;
$import_chunk = 250;
// Parse CSV & Build Import Query
$import_queries = array();
$first_row_skipped = false;
if (($handle = fopen($csv_file, "r")) !== FALSE) {
while (($data = fgetcsv($handle, 1000, $delimiter, )) !== FALSE) {
if ($skip_first_row && !$first_row_skipped) {
$first_row_skipped = true;
continue;
}
list($firstname, $lastname, $age) = $data;
$import_queries[] = "INSERT INTO myTable (firstname, lastname, age) VALUES ('$firstname', '$lastname', $age);";
}
fclose($handle);
}
// Proceed if any data got parsed
if (sizeof($import_queries))
{
foreach(array_chunk($import_queries, $import_chunk) as $queries)
{
$dbh->query(implode(' ', $queries));
}
}
?>
The parsed queries will look like this (if you print_r it):
Array
(
[0] => INSERT INTO myTable (firstname, lastname, age) VALUES ('Latheesan', 'Kanes', 26);
[1] => INSERT INTO myTable (firstname, lastname, age) VALUES ('Adam', 'Smith', 30);
)
You have two option for the actual importing into the db:
Build a collection of import sql query and execute it in a batch (array_chunk) - this means less queries against your db. However as you can see, im not checking the values from the CSV - i.e. i trust my data source and not escaping anything - a bit dangerous...
You execute the query, as soon as you built it with escaping the values - small drawback is that it will execute one query per row in csv....
Hope this helps.
这篇关于CSV使用PHP / MySQL上传的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
