Download the CSV file and import it into the database using Laravel

Question

I have this method that loads a CSV file, but now I want to know how to load 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 will be in this method, how can I put it in the database?

Answer 1

this one should work for you, it uses the PDO + mySql "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); } 

In combination with your code, it could be something like 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 note, according to the error you are reporting in the comments, which is probably related to some permissions (OS 13 error code: disclaimer)

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 be in the database directory or be readable by everyone. In addition, to use LOAD DATA INFILE for server files, you must have the FILE privilege. See Section 5.7. 3 "Privileges granted by MySQL" "

As reported on mySql tracker ( http://bugs.mysql.com/bug.php?id=31670 ), it seems that you need special permission for all folders in the path to the csv file:

All parent directories infile need world-readable I think as well as only directory and infile ...

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

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

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

Summarizing:
In order to solve the "general sqlstate hy000 13 error cannot get stat of ..." you should transfer the downloaded file to the right place with the appropriate permissions (so it’s not necessarily the current one that you are using) try something like "/ tmp / Import".

Answer 2

While loading infile data is the fastest way, I prefer to use lib, for example https://github.com/ddeboer/data-import or https://github.com/goodby/csv for two reasons.

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

• This is possible if you need to convert dates or strings or numbers, you can do it conditionally, which cannot be done using a batch command.

my 2c