Take the 2-minute tour ×
Webmasters Stack Exchange is a question and answer site for pro webmasters. It's 100% free, no registration required.

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

I've made a php file where i can upload the csv file and it analyses the fields n stuff and does the actual importing. but it can only handle small fiels 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 :D

share|improve this question
add comment

2 Answers 2

up vote 4 down vote accepted

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)

share|improve this answer
    
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 '11 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 '11 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 '11 at 12:25
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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