List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:January 10 2002 2:11am
Subject:Re: I'm having trouble with End-Of-Record markers
View as plain text  
At 18:21 +0000 1/9/02, David Ayliffe wrote:
>I'm having trouble bulk-loading data into a table.  I have a file which
>contains:
>
>AYL01ôAYL01-1ôChloeô1979-12-1 0:0:0ôF*END*
>AYL01ôAYL01-2ôMelissaô1985-3-2 0:0:0ôF*END*
>AYL01ôAYL01-3ôDavidô1979-1-12 0:0:0ôM*END*
>COL01ôCOL01-1ôSimeonô1989-12-14 0:0:0ôF*END*
>DAV01ôDAV01-1ôMurialô1990-2-2 0:0:0ôF*END*
>SMI01ôSMI01-1ôKarlaô1989-8-8 0:0:0ôF*END*
>WIL01ôWIL01-1ôRobertô1981-8-11 0:0:0ôM*END*
>WIL01ôWIL01-2ôAndrewô1987-9-9 0:0:0ôM*END*
>WIL02ôWIL02-1ôAbi-galeô1980-1-31 0:0:0ôF*END*
>WIL02ôWIL02-2ôCharlotteô1979-7-7 0:0:0ôF*END*
>WIL02ôWIL02-3ôSusanô1982-8-17 0:0:0ôF*END*
>WIL02ôWIL02-4ôClarissaô1980-12-24 0:0:0ôF*END*
>
>(Straight cut and paste)
>
>I load the data using
>LOAD DATA LOCAL INFILE "data.txt" INTO TABLE gymnast FIELDS TERMINATED
>BY "ô" LINES TERMINATED BY "*END*";
>
>But I always get a malformed table which looks like:

It's not malformed, MySQL is doing what you told it.

Your line terminator is "*END*", which doesn't
include the linefeed or carriage return or whatever's actually at
the end of your lines.

You probably want to specify "*END*\n" or "*END*\r" ?

>
>
>mysql> select * from gymnast;
>+-----------+------------+-----------+---------------------+------+
>| Family_id | Gymnast_id | Name      | Date_Of_Birth       | Sex  |
>+-----------+------------+-----------+---------------------+------+
>| AYL01     | AYL01-1    | Chloe     | 1979-12-01 00:00:00 | F    |
>|
>AYL01   | AYL01-2    | Melissa   | 1985-03-02 00:00:00 | F    |
>|
>AYL01   | AYL01-3    | David     | 1979-01-12 00:00:00 | M    |
>|
>COL01   | COL01-1    | Simeon    | 1989-12-14 00:00:00 | F    |
>|
>DAV01   | DAV01-1    | Murial    | 1990-02-02 00:00:00 | F    |
>|
>SMI01   | SMI01-1    | Karla     | 1989-08-08 00:00:00 | F    |
>|
>WIL01   | WIL01-1    | Robert    | 1981-08-11 00:00:00 | M    |
>|
>WIL01   | WIL01-2    | Andrew    | 1987-09-09 00:00:00 | M    |
>|
>WIL02   | WIL02-1    | Abi-gale  | 1980-01-31 00:00:00 | F    |
>|
>WIL02   | WIL02-2    | Charlotte | 1979-07-07 00:00:00 | F    |
>|
>WIL02   | WIL02-3    | Susan     | 1982-08-17 00:00:00 | F    |
>|
>WIL02   | WIL02-4    | Clarissa  | 1980-12-24 00:00:00 | F    |
>+-----------+------------+-----------+---------------------+------+
>12 rows in set (0.00 sec)
>
>mysql>
>
>
>How can I get the data into the table so it looks and acts like it
>should.

Thread
I'm having trouble with End-Of-Record markersDavid Ayliffe10 Jan
  • Re: I'm having trouble with End-Of-Record markersPaul DuBois10 Jan