Loading Data into MySQL from a Text File
This operation is surprisingly simple to do. Say, for instance, that we have the following MySQL table:
CREATE TABLE employee ( employee_id int not null auto_increment, employee_fname varchar(50) default NULL, employee_lname varchar(50) default NULL, employee_ssn varchar(9) NOT NULL, employee_email varchar(25) default NULL, employee_phone varchar(12) default NULL, PRIMARY KEY (employee_id) ) TYPE=MyISAM;
and we have a tab delimited text file that looks like the following that we want to load into the table:
Smith John sam@nowhere.com 123456789 Doe Jane sally.doe@nowhere.com 122456789 (888)253-8897 West Adam jwest@nowhere.com
You'll notice that the data isn't necessarily uniform, nor in the same order as the table. To import this text file first we note it's location on the filesystem. Let's assume for the purposes of this example that we're working on a Windows machine and the data is stored on d:\employees.txt.
The first thing you need to do is log into the MySQL server. Once you're at the command line you can use the following syntax to load the data:
mysql>LOAD DATA LOCAL INFILE 'd:\\employees.tab' ->INTO TABLE employee ->FIELDS TERMINATED BY "\t" ->LINES TERMINATED BY "\n" ->(employee_lname, employee_fname, employee_ssn, ->employee_phone, employee_email);
Notice the double back slash in the file location. This is only necessary on a Windows machine. If you don't use it Windows will interpret the first backslash as an escape character and look for the file 'd:mployees.tab'. Note that you can change the field delimiter or endline delimiter to any other appropriate value. This should load data into the MySQL table quite quickly and accurately, and fill in any auto incrementing columns as well.
For more information check out any of the following useful links: