best way to import 500MB csv file into mysql database?

Question

I have a 500MB csv file that needs to be imported into my mysql database.

I've written a PHP script where I can upload the csv file to and it analyses the fields and does the actual importing. but it can only handle small files of max. 5MB. So that's a 100 files and actually pretty slow(uploading)

Is there another way? I have to repeat this process every month because the data in the file changes every month it's about 12 000 000 lines.

Solution 1

If you know that the file contains consistently-formed CSV (there is no standard, so you'll likely want to test extensively before importing over any production tables) you can skip PHP altogether and use MySQL's LOAD DATA INFILE statement after uploading the data to the server.

(If you don't have immediate access to the mysql console on the server, this could also be accomplished with a very simple PHP script or with phpMyAdmin)

Solution 2

The best way to do this would be:

• using a tool like [SQLyog] which has a free "community" version you can download here.
  You might find useful SQLyog FAQ about importing CSV data

• import it [via SSH][4] (you can transfer database dump using an FTP and then just execute
  mysql -p -u username database_name < file.sql

Comments

[mars]

I tried running this in the sql tab of phpmyadmin LOAD DATA INFILE "/public_html/csv/bigfile.csv" INTO TABLE my_table FIELDS TERMINATED BY ';' LINES TERMINATED BY '\n' but got an error saying access denied, what do you mean when you say I can do it with phpmyadmin? I tried the import option there but it only allows 50MB and timeouts when using files bigger than 5mb

[Jonx]

I was talking about using phpMyAdmin in place of the MySQL console (as you were doing). The "access denied" error could be related to file or database permissions - could you post the full error message?

[mars]

ok I tried the doing it trough php instead of phpmyadmin and it worked as I could use a relative path to the file... and its really really fast... thanks!!