SR Technoqueries Solutions

How to load data into table from a text file ?

January 24, 2011 – 9:44 am

Here we are assuming that we have a  table with given structure:

CREATE TABLE teasttable (
prkey int(11) NOT NULL auto_increment,
names varchar(20),
score int,
timeEnter timestamp(14),
PRIMARY KEY  (prkey)
);

And we have the formatted text file as shown below with the unix “tail”  command:

$ tail /tmp/out.txt
'name1880',94
'name1881',93
'name1882',91
'name1883',93
'name1884',90
'name1885',93
'name1886',93
'name1887',89
'name1888',85
'name1889',88

The  testtable contains the “prkey” and “timeEnter” fields which are not given in the “/tmp/out.txt” file. Therefore, to successfully load the specific fields you need to do the following:

mysql> load data infile ‘/tmp/out.txt’ into table testtable
fields terminated by ‘,’ (name,score);