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 ?

Worth noting that 'files' => true is required as a form attribute (e.g. {{ Form::open(array('class' => 'create-user', 'role' => 'form', 'files' => true)) }}) — u01jmg3
– u01jmg3, Commented Aug 2, 2016 at 15:05

Gadoma · Accepted Answer · 2014-01-11 14:41:55Z

8

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

edited Jan 11, 2014 at 14:41

answered Jan 11, 2014 at 13:51

Gadoma

6,5751 gold badge33 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

the_unforgiven_II Over a year ago

I'm getting SQLSTATE[HY000]: General error: 13 Can't get stat of then the path to my upload

the_unforgiven_II Over a year ago

Working locally, and i have set all folders relating to this to 755 so this should work then

Gadoma Over a year ago

great, if so +1 and accept answer so others can easily find it in future. cheers.

Gadoma Over a year ago

check out another minor edit to the initial code - it should work now.

Captain Hypertext Over a year ago

My problem with this is that it provides minimal to no validation of the data you are dumping into the database, which would be very bad if this function were open to general user input. It's really only for one-off imports by trusted user(s) who know what they're doing. But the OP didn't specify either way, so I'll leave it at that.

Petraeus · Accepted Answer · 2015-01-13 13:41:47Z

7

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.

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.

my 2c

answered Jan 13, 2015 at 13:41

Petraeus

1541 silver badge3 bronze badges

Collectives™ on Stack Overflow

Upload CSV file and import it to database using Laravel

2 Answers 2

EDIT

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

EDIT

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related