Upload CSV file and import it to database using Laravel

Question

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.

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 ?

Answer 1

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

     }
}

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)

Please see: 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”."

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:

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

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

Answer 2

While load data infile is the quickest way, I prefer to use a lib like https://github.com/ddeboer/data-import or https://github.com/goodby/csv for 2 reasons.

1. It is extensible, what if your data source changes to excel files or a mongo db or some other method?

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

my 2c