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 <[EMAIL PROTECTED]> >To unsubscribe, e-mail ><[EMAIL PROTECTED]> >Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > --------------------------------------------------------------------- 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 <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php