I created a database called "genomicdb" in MYSQL server with two tables: "gene" and "location". The tables are supposed to be populated with data from .txt files. One of the files, "gene.txt", works fine and populates fine in the table. But the other file, "location.txt", is skipping rows. Below is the code that I am using. I am also attaching the files that I am using to populate the tables.
#Created the tables.
mysql> create table gene
-> (gene_name varchar(20) not null,
-> gene_id varchar(20) not null primary key
-> );
Query OK, 0 rows affected (0.09 sec)
mysql> create table location
-> (chromosome varchar(10) not null,
-> strand varchar(10) not null,
-> cds_from varchar(30) not null primary key,
-> cds_to varchar(30) not null
-> );
Query OK, 0 rows affected (0.11 sec)
# Loaded the data. The second command is the one that is not working.
mysql> LOAD DATA LOCAL INFILE 'C:/Documents and Settings/Owner/Desktop/gene.txt' INTO TABLE gene FIELDS TERMINATED BY 't' LINES TERMINATED BY 'rn' IGNORE 1 LINES (gene_name, gene_id);
Query OK, 18962 rows affected (1.33 sec)
Records: 18962 Deleted: 0 Skipped: 0 Warnings: 0
LOAD DATA LOCAL INFILE 'C:/Documents and Settings/Owner/Desktop/location.txt' INTO TABLE location FIELDS TERMINATED BY 't' LINES TERMINATED BY 'rn' IGNORE 1 LINES (chromosome, strand, cds_from, cds_to);
Query OK, 17244 rows affected (1.33 sec)
Records: 18961 Deleted: 0 Skipped: 1717 Warnings: 0
Attachment:- gene.zip