Results 1 to 2 of 2

Thread: Loading data

  1. #1
    Join Date
    May 2013
    Posts
    33

    Unanswered: Loading data

    Hi all...

    I want to load data from the text file. In the text file, there is a column named remark which contains comma(,) in the value....As i m new to Mysql, i dont know how to load data to the table with comma(,) in thecolumn value...So pls suggest me to load data into the table.

    This is my text file named event.txt....

    Code:
    Fluffy	,1995-05-15   ,litter 		,4 kittens, 3 female, 1 male
    Buffy   ,1993-06-23   ,litter 		,5 puppies, 2 female, 3 male
    Buffy   ,1994-06-19   ,litter 		,3 puppies, 3 female
    Chirpy  ,1999-03-21   ,vet 		,needed beak straightened
    Slim    ,1997-08-03   ,vet 		,broken rib
    Bowser  ,1991-10-12   ,kennel		,
    Fang    ,1991-10-12   ,kennel		,
    Fang    ,1998-08-28   ,birthday 	,Gave him a new chew toy
    Claws   ,1998-03-17   ,birthday 	,Gave him a new flea collar
    Whistler,1998-12-09   ,birthday 	,First birthday

    There i have 4 columns named name,date,type,remark...In the remark column i have comma(,) in the values...So when i load i m getting datas loaded in different manner. See below:

    Code:
    mysql> load data infile 'E:\sss.txt' into table event
        -> fields terminated by ',';
    Query OK, 10 rows affected, 6 warnings (0.05 sec)
    Records: 10  Deleted: 0  Skipped: 0  Warnings: 6
    
    mysql> select * from event;
    +--------------------+------------+-----------------+------------------------------+
    | name               | date       | type            | remark                       |
    +--------------------+------------+-----------------+------------------------------+
    | Fluffy             | 1995-05-15 |  litter         |  4 kittens                   |
    | Buffy              | 1993-06-23 |  litter         |  5 puppies                   |
    | Buffy              | 1994-06-19 | litter          |  3 puppies                   |
    | Chirpy 1999-03-21  | 0000-00-00 |  needed beak st | NULL                         |
                     |   | 1997-08-03 |  vet            |  broken rib
                                |0-12 |  kennel         |
                                |0-12 |  kennel         |
        |g               | 1998-08-28 |  birthday       |  Gave him a new chew toy
     |Claws              | 1998-03-17 |  birthday       |  Gave him a new flea collar
    | Whistler           | 1998-12-09 |  birthday       |  First birthday              |
    +--------------------+------------+-----------------+------------------------------+
    10 rows in set (0.00 sec)
    help me to load datas into the table.
    Last edited by satheeshsharma; 07-25-13 at 03:18.

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    There are 2 things you need to be careful about:

    1. Remove spaces between the quotes
    2. Add quotes around the strings that might contain commas:

    For example:

    Fluffy ,1995-05-15,litter,"4 kittens, 3 female, 1 male"

    Your load statement should include OPTIONALLY ENCLOSED BY '"'
    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
  •