5
votes

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.

2 Answers 2

Reset to default
4
votes

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)

3
  • 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
    – mars
    May 28, 2011 at 10:26
  • 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?
    – danlefree
    May 28, 2011 at 11:25
  • 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!!
    – mars
    May 28, 2011 at 12:25
1
vote

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.