Load Data Local Infile Problem with MYSQL
Not what you're looking for?
See the attached file(s).
A database called "genomicdb" in MYSQL server with two tables: "gene" and "location" is created. 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.
Purchase this Solution
Solution Summary
The expert examines load data for local infile problems with MYSQL.
Solution Preview
The file "location.txt' has total 18962 rows.
In the table "location" definition you set the column "cds_from" as Primary Key.
but in the file there are duplicated based on "cds_from"
Here is the list of duplicate records based on "cds_from"
the below records should be in file only ONE time.
If you want to load all records from the file to the table: location , please remove primary key from the table "Location" and load process will load all records into the table:
table : Location without primary key.
mysql> create table location
-> (chromosome varchar(10) not null,
-> strand varchar(10) not null,
-> cds_from varchar(30) not null ,
-> cds_to varchar(30) not null
-> );
cds_from # times in file:
61700119 2
241526328 3
199996975 2
35760004 2
591632 ...
Purchase this Solution
Free BrainMass Quizzes
Basic UNIX commands
Use this quiz to check your knowledge of a few common UNIX commands. The quiz covers some of the most essential UNIX commands and their basic usage. If you can pass this quiz then you are clearly on your way to becoming an effective UNIX command line user.
Word 2010: Tables
Have you never worked with Tables in Word 2010? Maybe it has been a while since you have used a Table in Word and you need to brush up on your skills. Several keywords and popular options are discussed as you go through this quiz.
C++ Operators
This quiz tests a student's knowledge about C++ operators.
Basic Networking Questions
This quiz consists of some basic networking questions.
Basic Computer Terms
We use many basic terms like bit, pixel in our usual conversations about computers. Are we aware of what these mean? This little quiz is an attempt towards discovering that.