Quantitative finance collector
C++ Matlab VBA/Excel Java Mathematica R/Splus Net Code Site Other
Oct 27

Any Good Way To Import A Large CSV File Into MySql

Posted by abiao at 15:52 | Code » Other | Comments(3) | Reads(9935)
I am a newbie on MySql, have googled 2 hours but without a convincing answer, could you please recommend a good way to import a large CSV file into MySql? say a file of 6GB with 40 million rows? no matter through a client software or simple command line.

Cheers,
Biao


PS: Nick, thanks a lot for your reply, I have tried your way & it took me 1 hour and 16 minutes to import my 40 million lines CSV into MySQL on my humble laptop. That's great. My next task then is to check the performance of RMySQL package.
LOAD DATA INFILE 'data.csv' INTO TABLE tbl_name
  FIELDS TERMINATED BY ',' ENCLOSED BY '"'
  LINES TERMINATED BY '\r\n'
  IGNORE 1 LINES;

mysql large csv load performance


Tags: ,
I always use load data infile. From the link below: LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table;This will load the file in data.txt (use csv) into the table my_table in database db2. There are other options such as "fields terminated by" "set columns = " but if you just need to import, the above will work fine. Note about indexes: If you are using a MyISAM table, it can possibly go faster if you disable indexes on the table before loading, then enable after loading:Alter table ... disable keys;alter table ... enable keys;http://dev.mysql.com/doc/refman/5.1/en/load-data.html
Thanks, Nick.
Pages: 1/1 First page 1 Final page
Add a comment
Emots
Enable HTML
Enable UBB
Enable Emots
Hidden
Remember
Nickname   Password   Optional
Site URI   Email   [Register]