Results 1 to 13 of 13

Thread: LOAD DATA into Mysql

  1. #1
    Join Date
    Jul 2003
    Posts
    421

    LOAD DATA into Mysql

    Hi all,
    I am new in mysql , I want to load file into table , but always got error. wha tis wrong??


    File 'c\pet.txt' not found (Errcode: 2)
    mysql> LOAD DATA LOCAL INFILE '~/pets.txt' INTO TABLE pet;
    ERROR:
    File '~\pets.txt' not found (Errcode: 2)
    mysql>
    Thank you
    ________
    SIXTH-GENERATION TAURUS
    Last edited by sql; 03-06-2011 at 01:42 AM.

  2. #2
    Join Date
    Apr 2005
    Location
    florida
    Posts
    89
    Give it a try with the physical path like..
    LOAD DATA LOCAL INFILE 'c:/pets.txt' INTO TABLE pet;

    Adding this line would be helpfull
    FIELDS TERMINATED BY ',' ENCLOSED BY '"'

    (in this case, we have : "val1","val2",...)

  3. #3
    Join Date
    Feb 2003
    Posts
    1,048
    Actually: c:\pets.txt is the properly formatted path

  4. #4
    Join Date
    Apr 2005
    Location
    florida
    Posts
    89
    Thank's Rawhide, but...
    I don't know if it is a bug from MYSQL, the properly format path won't work.
    If I m wong please, let me know.

  5. #5
    Join Date
    Feb 2003
    Posts
    1,048
    Same error?

    Are you sure that you have the file name and path correct?

  6. #6
    Join Date
    Jul 2003
    Posts
    421
    Hi all,
    Thank you for the reply. I got error like


    mysql> show tables;
    +---------------------+
    | Tables_in_menagerie |
    +---------------------+
    | pet |
    +---------------------+
    1 row in set (0.00 sec)

    mysql> LOAD DATA LOCAL INFILE 'c:\pet.txt' into table pet;
    ERROR:
    File 'cet.txt' not found (Errcode: 2)
    mysql> LOAD DATA LOCAL INFILE 'c:\pet.txt' into table pet; lines TERMINATED BY
    '\r\n';
    ERROR:
    File 'cet.txt' not found (Errcode: 2)

  7. #7
    Join Date
    Jul 2003
    Posts
    421
    Hi all;
    I got it at Win xp should be

    mysql> LOAD DATA LOCAL INFILE "C:/pet.txt" into table pet;
    Query OK, 2 rows affected (0.00 sec)
    Records: 2 Deleted: 0 Skipped: 0 Warnings: 12

    but I am not sure about the form in text file , my txt file is Whistler, Gwen, bird, \N, 1997-12-09, \N
    Fluffy, Harold, cat, f, 1993-02-04, \N
    but after I load them into databae I got

    mysql> select* from pet;
    +----------------------+-------+---------+------+-------+-------+
    | name | owner | species | sex | birth | death |
    +----------------------+-------+---------+------+-------+-------+
    | Whistler, Gwen, bi | NULL | NULL | NULL | NULL | NULL |
    | Fluffy, Harold, ca | NULL | NULL | NULL | NULL | NULL |
    +----------------------+-------+---------+------+-------+-------+
    what form I should use in my text file??
    Thank you
    ________
    OG KUSH PICTURES
    Last edited by sql; 03-06-2011 at 01:42 AM.

  8. #8
    Join Date
    Apr 2005
    Location
    florida
    Posts
    89
    Could you send the pet's description? (the table)

  9. #9
    Join Date
    Feb 2003
    Posts
    1,048
    Like OracleDB said above, you need to include: FIELDS TERMINATED BY ','

  10. #10
    Join Date
    Jul 2003
    Posts
    421
    Hi all,
    the only line is correct is direct insert into table , the recode load from txt file is miss, why??
    mysql> DESCRIBE pet;
    +---------+-------------+------+-----+---------+-------+
    | Field | Type | Null | Key | Default | Extra |
    +---------+-------------+------+-----+---------+-------+
    | name | varchar(20) | YES | | NULL | |
    | owner | varchar(20) | YES | | NULL | |
    | species | varchar(20) | YES | | NULL | |
    | sex | char(1) | YES | | NULL | |
    | birth | date | YES | | NULL | |
    | death | date | YES | | NULL | |
    +---------+-------------+------+-----+---------+-------+
    6 rows in set (0.02 sec)

    mysql> select* from pet;
    +----------------------+-------+---------+------+------------+-------+
    | name | owner | species | sex | birth | death |
    +----------------------+-------+---------+------+------------+-------+
    | Whistler, Gwen, bi | NULL | NULL | NULL | NULL | NULL |
    | Fluffy, Harold, ca | NULL | NULL | NULL | NULL | NULL |
    | Puffball | Diane | hamster | f | 1999-03-30 | NULL |
    | Whistler, Gwen, bi | NULL | NULL | NULL | NULL | NULL |
    | Fluffy, Harold, ca | NULL | NULL | NULL | NULL | NULL |
    | Whistler, Gwen, bi | NULL | NULL | NULL | NULL | NULL |
    | Fluffy, Harold, ca | NULL | NULL | NULL | NULL | NULL |
    | Whistler, Gwen, bi | NULL | NULL | NULL | NULL | NULL |
    | Fluffy, Harold, ca | NULL | NULL | NULL | NULL | NULL |
    | Whistler, Gwen, bi | NULL | NULL | NULL | NULL | NULL |
    | Fluffy, Harold, ca | NULL | NULL | NULL | NULL | NULL |
    +----------------------+-------+---------+------+------------+-------+
    11 rows in set (0.03 sec)
    ________
    Bmw e39
    Last edited by sql; 03-06-2011 at 01:42 AM.

  11. #11
    Join Date
    Apr 2005
    Location
    florida
    Posts
    89
    You need to
    1)modify the column SEX, because in the "pet.txt" the sex field contains 2-char valus (/N)
    2) complete the command LOAD DATA... with the line(FIELDS TERMINATED BY ',').

    Give it a try with:

    alter table pet modify sex char(2);
    LOAD DATA LOCAL INFILE "C:/pet.txt" into table pet
    FIELDS TERMINATED BY ',';

  12. #12
    Join Date
    Jul 2003
    Posts
    421
    Hi friends,
    Thank you, it work now. by the way can we load file into MS sql ?
    Thank you
    ________
    Vaporizer
    Last edited by sql; 03-06-2011 at 01:42 AM.

  13. #13
    Join Date
    Feb 2003
    Posts
    1,048
    Sure can, you can use BCP (command line utility), DTS (graphical interface utility), or create an ODBC connection to the file and insert it with a query. The simplest way would be to right click on the pets table in Enterprise Manager, select All Tasks, click on Import, and follow the DTS wizard.

Posting Permissions

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