php 上传 CSV 文件并使用 Laravel 将其导入数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21063008/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Upload CSV file and import it to database using Laravel
提问by the_unforgiven_II
I have this method that uploads a file of a CSV format, but now i want to know how to upload it into columns in my database.
我有这种上传 CSV 格式文件的方法，但现在我想知道如何将其上传到我的数据库中的列中。
My method:
我的方法：
public function postUpload ()
{
if (Input::hasFile('file')){
$file = Input::file('file');
$name = time() . '-' . $file->getClientOriginalName();
// Moves file to folder on server
$file->move(public_path() . '/uploads/CSV', $name);
return 'Ok'; // return for testing
}
}
So my question would be within this method how can i can put it into the database ?
所以我的问题是在这个方法中我怎样才能把它放到数据库中？
回答by Gadoma
this one should work for you, it uses PDO + mySql's "LOAD DATA" approach
这个应该适合你，它使用 PDO + mySql 的“加载数据”方法
private function _import_csv($path, $filename)
{
$csv = $path . $filename;
//ofcourse you have to modify that with proper table and field names
$query = sprintf("LOAD DATA local INFILE '%s' INTO TABLE your_table FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' ESCAPED BY '\"' LINES TERMINATED BY '\n' IGNORE 0 LINES (`filed_one`, `field_two`, `field_three`)", addslashes($csv));
return DB::connection()->getpdo()->exec($query);
}
So combined with your code, it could be something like the below
因此结合您的代码，它可能类似于以下内容
public function postUpload ()
{
if (Input::hasFile('file')){
$file = Input::file('file');
$name = time() . '-' . $file->getClientOriginalName();
//check out the edit content on bottom of my answer for details on $storage
$storage = '/some/world/readible/dir';
$path = $storage . '/uploads/CSV';
// Moves file to folder on server
$file->move($path, $name);
// Import the moved file to DB and return OK if there were rows affected
return ( $this->_import_csv($path, $name) ? 'OK' : 'No rows affected' );
}
}
EDIT
编辑
One thing to be noted, as per the error you report in comments which is probably some permissions issue (OS error code 13: Permission denied)
需要注意的一件事，根据您在评论中报告的错误，这可能是一些权限问题（操作系统错误代码 13：权限被拒绝）
Please see: http://dev.mysql.com/doc/refman/5.1/en/load-data.html
请参阅：http: //dev.mysql.com/doc/refman/5.1/en/load-data.html
"For security reasons, when reading text files located on the server, the files must either reside in the database directory or be readable by all. Also, to use LOAD DATA INFILE on server files, you must have the FILE privilege. See Section 5.7.3, “Privileges Provided by MySQL”."
“出于安全原因，读取位于服务器上的文本文件时，文件必须位于数据库目录中或可供所有人读取。此外，要在服务器文件上使用 LOAD DATA INFILE，您必须具有 FILE 权限。请参阅第 5.7 节.3，“MySQL 提供的权限”。
As reported on mySql bug tracker (http://bugs.mysql.com/bug.php?id=31670) it seems that you need particular permission for all the folders in the csv file path:
正如 mySql 错误跟踪器 ( http://bugs.mysql.com/bug.php?id=31670)所报告的，您似乎需要对 csv 文件路径中的所有文件夹具有特定权限：
All parent directories of the infile need world-readable I think aswell as just the directory and infile...
So for an infile here: /tmp/imports/site1/data.file
you would need (I think, 755 worked) r+x for 'other' on these directories: /tmp /tmp/imports
as well as the main two: /tmp/imports/site1 /tmp/imports/site1/data.file
infile 的所有父目录都需要世界可读，我认为以及目录和 infile ......
所以对于这里的 infile：/tmp/imports/site1/data.file
您需要（我认为，755 工作）r+x 用于这些目录上的“其他”：/tmp /tmp/imports
以及主要的两个：/tmp/imports/site1 /tmp/imports/site1/data.file
To sum up:
To solve the "sqlstate hy000 general error 13 can't get stat of..." issue you have to move the uploaded file to a location with proper permissions (so not neccessarily the current one you are using) try something like "/tmp/import".
总结：
要解决“sqlstate hy000 一般错误 13 无法获取...的统计信息”问题，您必须将上传的文件移动到具有适当权限的位置（因此不一定是您正在使用的当前位置）尝试一些操作比如“/tmp/import”。
回答by Petraeus
While load data infile is the quickest way, I prefer to use a lib like https://github.com/ddeboer/data-importor https://github.com/goodby/csvfor 2 reasons.
虽然加载数据 infile 是最快的方法，但出于两个原因，我更喜欢使用像https://github.com/ddeboer/data-import或 https://github.com/goodby/csv这样的库。
It is extensible, what if your data source changes to excel files or a mongo db or some other method?
It is mallable, if you need to convert dates, or strings or numbers you can do it conditionally which cannot be done with a batch command.
它是可扩展的，如果您的数据源更改为 excel 文件或 mongo db 或其他方法怎么办？
它是可扩展的，如果您需要转换日期、字符串或数字，您可以有条件地进行，而批处理命令无法完成。
my 2c
我的 2c
