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.
