Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Mar 2011
    Location
    Sydney, Australia
    Posts
    58

    Unanswered: Load Data Command MySQL

    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.

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    What have you tried so far?
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Mar 2011
    Location
    Sydney, Australia
    Posts
    58

    Load Data Command used

    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\'

  4. #4
    Join Date
    Mar 2004
    Posts
    480
    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.

  5. #5
    Join Date
    Mar 2011
    Location
    Sydney, Australia
    Posts
    58

    Load Data Command used

    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?

  6. #6
    Join Date
    Mar 2004
    Posts
    480
    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.

  7. #7
    Join Date
    Mar 2011
    Location
    Sydney, Australia
    Posts
    58

    Load Data Command used

    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.

  8. #8
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    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

    UPDATE mysql.user SET File_priv = 'Y' WHERE User = '{username}';
    FLUSH PRIVILEGES;
    Last edited by it-iss.com; 03-02-11 at 04:40.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  9. #9
    Join Date
    Mar 2011
    Location
    Sydney, Australia
    Posts
    58

    Load data into tables

    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'.

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    or try something like HediSQL or MySQL Workbench
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    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.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  12. #12
    Join Date
    Mar 2011
    Location
    Sydney, Australia
    Posts
    58

    Data type error when loading data to table

    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.

  13. #13
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    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)
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  14. #14
    Join Date
    Mar 2011
    Location
    Sydney, Australia
    Posts
    58

    Load Data Command used

    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';

  15. #15
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    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?
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •