Level 75
If shared hosting exec isn't allowed sometimes. Depending on host. Import or upload to local, do sql dump, then import to online host. This does not have to be done in laravel use phpmyadmin on host if necessary.
Oct 14, 2015
6
Level 7
LOAD DATA INFILE
I am trying to load a large CSV file into a mySQL database from my Laravel application. I am finding out, however, that this is just timing out even when changing the max_execution_time.
When researching the issue I found out that when importing large files into mySQL we should use LOAD DATA INFILE but I don't see anyway to implement this using the Query Builder. Does anyone have a suggestion for getting LOAD DATA INFILE to work in Laravel?
I see some older posts stating to use $pdo->exec which I attempted but got an error stating PDO::exec(): LOAD DATA LOCAL INFILE forbidden
$pdo = DB::connection()->getPdo();
$pdo->exec("LOAD DATA LOCAL INFILE '".storage_path('app/dbip.csv')."' INTO TABLE dbip_lookup FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\\n'");
Level 7
Not on shared hosting - Currently using Laravel Homestead. I am aware that can import manually using phpmyadmin, or even mysql cli but the point of this specific class is to have it done automatically. This specific csv file has over 1,048,576 lines and is 380 MB. I tried just iterating it with a foreach loop and then writing each line to the database but it would time out. Might just have to re-evaluate this feature.
Level 15
See how I used it in a migration. It includes all the prereqs, etc to get it working:
https://tenerant.com/blog/using-load-data-local-infile-in-a-laravel-migration/
1 like
Level 7
Thank you, sir! This looks to be exactly what I need.
Level 2
Also note, your MYSQL users needs the file privlige to LOAD DATA.
2 likes
Level 15
@codenex Cool - glad it was helpful. And @jaketoolson is absolutely right, your MySQL user does need the file privilege. I'll go back and make a note of that in the post when I get a chance. However, the homestead user is granted all privileges by default. That may not be true for a production user, other vm, shared host, etc. For those on shared hosting, you may be stuck with doing this locally, dumping and importing as @jlrdw notes in his response.
If your ensuing dump file is huge (tens of gigabytes), have a look at this post: https://cmanios.wordpress.com/2013/03/19/import-a-large-sql-dump-file-to-a-mysql-database-from-command-line/ and I would also recommend installing standalone MySQL server (not WAMP/XAMPP or Homestead) locally (at least on Windows).
Please or to participate in this conversation.