Load Data Command MySQL

melgra70 · #1 (**permalink**) 03-01-11, 06:50

Can anyone help me out with a LOAD DATA command for MySQL? I'm a student, working in the command prompt line; have created a database, created tables and now need to load data from text files, one per table, specifically using the LOAD DATA command.

it-iss.com · #2 (**permalink**) 03-01-11, 07:22

What have you tried so far?

melgra70 · #3 (**permalink**) 03-01-11, 15:37

This is what I got from a website.
I'm using Windows 7, so the terminated statement is what they said I had to use.
C:\users\imelda\desktop\horsefiles\entry.txt' into table entry lines terminated by '\r\n\'

guelphdad · #4 (**permalink**) 03-01-11, 20:52

are you actually leaving the
LOAD DATA INFILE
part out of your statement when you run it or did you just leave it off when you posted here?

should be:

Code:

LOAD DATA INFILE
'C:\users\imelda\desktop\horsefiles\entry.txt'
INTO TABLE
entry
LINES TERMINATED BY '\r\n'

you should also specify what your FIELDS are separated by as well so it knows where one column ends and the next begins.

melgra70 · #5 (**permalink**) 03-01-11, 21:26

Thanks so much for this.
I'll look at the field separators shortly (I was under the impression from my notes that because my fields are tab separated, this was a default and didn't need to be defined).

I've run your code and came up with an Error code 2. What it's doing is adding the following to the beginning of the file address:
\wamp\bin\mysql\mysql5.5.8\data\... then my defined file name.

So I assumed this means that it wants the data file stored in the mysql directory. I moved the "entry" text file to this directory, so the address of the file is now
C:\wamp\bin\mysql\mysql5.5.8\data\entry.txt

then I reran the code, edited to read as follows:

LOAD DATA INFILE
'C:\wamp\bin\mysql\mysql5.5.8\data\entry.txt'
INTO TABLE
entry
LINES TERMINATED BY '\r\n'

This still produced an error,
"ERROR 29 (MY000): File 'c:wamp\bin\mysql\mysql5.5.8\data\waminmysqlmysql5 .5.8dataentry.txt' not found (Errcode: 22)"

Why is it doing that?
Would you like me to attach a screen dump of the mysql console?

guelphdad · #6 (**permalink**) 03-01-11, 21:58

yes true if tab is default. i usually save mine as comma (CSV) so always need.

you don't need to do a screen dump. You can store the file anywhere on your computer.
I'm wondering if you don't have FILE permissions inside mysql with that particular user.

are you running this as root user or a regular user? Is mysql installed on your own computer and you are trying this out there or are you using a shared host with mysql installed? Many shared hosts don't allow file privileges. If it is your own computer and you are running other than the mysql root user, switch to that user and try.

melgra70 · #7 (**permalink**) 03-02-11, 01:06

Thanks for this and for your help.

I have a laptop and a PC. On the laptop MySQL is installed separately from WAMP and this is working fine, so thanks for the clarification.

On the PC, MySQL is installed through WAMP and I think this is where the problem lies with the file address. I'm happy to work with the laptop for the purposes of this exercise.

Thanks again.

it-iss.com · #8 (**permalink**) 03-02-11, 03:34

It seems you are missing grant FILE privileges for your user to be able to upload the data. You need to issue with root access:

Code:

GRANT FILE on *.* TO {username};

Or alternatively

Quote:

UPDATE mysql.user SET File_priv = 'Y' WHERE User = '{username}';
FLUSH PRIVILEGES;

melgra70 · #9 (**permalink**) 03-06-11, 23:19

I've been able to use the LOAD DATA command successfully but I have two separate problems:

When I CREATE TABLE how do I express the data type for a primary key which is a combination of two foreign keys? Example: this seems to be incorrect:

create table entry
(
event_id int not null references event(event_id) primary key,
horse_id int not null references horse(horse_id) primary key,
place char(2)
) type=InnoDB;

Also, how do I denote a data type for an attribute which needs to accept '0' as a value for the primary key?
This is the error I got when I loaded data to a table where the first record contains '0' in the primary key field (first attribute). I must use the data as presented, so I can't change this zero value and I'm assuming the load process has changed the zero to 1.
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'.

healdem · #10 (**permalink**) 03-07-11, 02:16

or try something like HediSQL or MySQL Workbench

it-iss.com · #11 (**permalink**) 03-07-11, 03:54

Try this for creating the primary key:

Code:

CREATE TABLE entry
  (
     event_id INT NOT NULL REFERENCES event(event_id),
     horse_id INT NOT NULL REFERENCES horse(horse_id),
     place    CHAR(2),
     PRIMARY KEY (event_id, horse_id)
  )
TYPE=INNODB;

In this case the primary key is made up of both the event_id and the horse_id. The combination must be unique and of course NOT NULL. If you still have data errors come back to us.

melgra70 · #12 (**permalink**) 03-07-11, 06:08

That's perfect - thank you! I still need a solution for my second question, please:

How do I denote a data type for an attribute which needs to accept '0' as a value for the primary key?
This is the error I got when I loaded data to a table where the first record in the table contains '0' in the primary key field (first attribute). I must use the data as presented, so I can't change this zero value and I'm assuming the load process has changed the zero to '1'.
The data type I've allocated to the field is: trainer_id int not null auto_increment primary key
The resultant error is:
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'.

Thank you.

it-iss.com · #13 (**permalink**) 03-07-11, 06:31

I am not following what you are saying. I take is this is from a separate table? The auto_increment automatically populates the field that is associated with it. This is regardless of whether you specify a value or not:

Code:

mysql> create table t1(id int auto_increment, name varchar(10), primary key (id));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t1 (id, name) values (0, 'test1');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 (id, name) values (1, 'test2');
ERROR 1062 (23000): Duplicate entry '1' for key 1
mysql> select * from t1;
+----+-------+
| id | name  |
+----+-------+
|  1 | test1 | 
+----+-------+
1 row in set (0.00 sec)

melgra70 · #14 (**permalink**) 03-07-11, 14:59

Yes, sorry - I am required to use the LOAD DATA command to load the data for this table from a .txt file.

LOAD DATA INFILE
'trainer.txt'
INTO TABLE
trainer
lines terminated by '\r\n';

it-iss.com · #15 (**permalink**) 03-07-11, 15:43

I seem to be missing some information here or just not following what your issue is?

Can you provide us with the table layout with the auto_increment and primary key definition. I am confused with your statement that the first record contains '0' in the primary key field (but earlier we showed two fields for primary key). Where is this '0' coming from? I suppose it is coming from the .txt file. Are there more than one rows in this file with duplicate entries for the primary key?

Internet Services

Web Development

Digital Marketing

Consumer Tech