上传CSV文件并使用Laravel将其导入数据库 [英] Upload CSV file and import it to database using Laravel

问题描述

我有这种方法可以上传CSV格式的文件，但是现在我想知道如何将其上传到数据库的列中.

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.

我的方法:

 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 ?

推荐答案

这应该为您工作，它使用PDO + mySql的"LOAD DATA"方法

this one should work for you, it uses PDO + mySql's "LOAD DATA" approach

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' );            

     }
}

编辑

有一点要注意，根据您在注释中报告的错误，这可能是某些权限问题(操作系统错误代码13:权限被拒绝)

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)

请参阅: http://dev.mysql.com/doc/refman/5.1/en/load-data.html

"出于安全原因，在读取服务器上的文本文件时， 文件必须位于数据库目录中或可读 所有.另外，要在服务器文件上使用LOAD DATA INFILE，您必须具有 FILE特权.请参见第5.7.3节由...提供的特权 MySQL".

"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"."

如mySql bug跟踪器( http://bugs.mysql.com/bug.php?id= 31670 )，似乎您需要对csv文件路径中的所有文件夹具有特定的权限:

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:

我认为，infile的所有父目录都需要世界可读 以及目录和infile ...

All parent directories of the infile need world-readable I think aswell as just the directory and infile...

因此，对于此处的infile:/tmp/imports/site1/data.file

So for an infile here: /tmp/imports/site1/data.file

您需要在这些上为其他"提供r + x(我认为是755工作了) 目录:/tmp/tmp/imports

you would need (I think, 755 worked) r+x for 'other' on these directories: /tmp /tmp/imports

以及主要的两个:/tmp/imports/site1 /tmp/imports/site1/data.file

as well as the main two: /tmp/imports/site1 /tmp/imports/site1/data.file

总结:
要解决"sqlstate hy000一般错误13无法获取统计信息..."的问题，您必须将上载的文件移动到具有适当权限的位置(因此不必使用当前使用的文件)，尝试使用类似"/tmp"的方法/进口".

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".

这篇关于上传CSV文件并使用Laravel将其导入数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！