List:General Discussion« Previous MessageNext Message »
From:Gerald Clark Date:January 9 2002 8:38pm
Subject:Re: I'm having trouble with End-Of-Record markers
View as plain text  
Since you told it to end each line with *END* , the carriage return 
after the
*END* is part of the first field of the next line.

Try terminated by "*END*\r"

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:
>
>
>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.
>
>
>Also when I try to EXTRACT the data from the same table using my delphi
>application on the last field (Sex) instead of just getting the value
>'F' I get 'F'#$D  Is this some sort of end-of-record marker?
>
>
>I could really do with some guidance on this one.
>
>Thanks
>DA
>
>
>---------------------------------------------------------------------
>Before posting, please check:
>   http://www.mysql.com/manual.php   (the manual)
>   http://lists.mysql.com/           (the list archive)
>
>To request this thread, e-mail <mysql-thread95877@stripped>
>To unsubscribe, e-mail
> <mysql-unsubscribe-gerald_clark=suppliersystems.com@stripped>
>Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>


Thread
I'm having trouble with End-Of-Record markersDavid Ayliffe9 Jan
  • Re: I'm having trouble with End-Of-Record markersGerald Clark9 Jan