Friday, September 16, 2011

MySQL: importing table data from text file


You can import table data from text file.

If columns are matched in order to delimited file, then it is very easy, example data:

column1data|column2data|column3data| |column5data
column1data|column2data|column3data|column4data|column5data


Note that the file name should be the exact name of the table you are writing to:


uki: ~ $ mysqlimport --local -u root --fields-terminated-by="|" sears_product_info /Users/uki/Documents/XYZ/table_name.txt 

database_name. table_name: Records: 526733  Deleted: 0  Skipped: 526733  Warnings: 557695


Note that if the record already exists then it will not be imported again (a good feature for me).
If you want to clean the table content then you execute 
DELETE FROM database_name. table_name
The import of 500,000 records may take up to 10 minutes on MacBook Pro.