At 17:36 +0000 1/6/02, David Ayliffe wrote:
>I have a file (data.txt) which contains the data:
>
>ELITEô85.0000ôQô4 DaysôLilleshallôChris
>IMPô75.0000ôQôTues&FriôLilleshallôSue
>LEFTô0.0000ôôôLeftô
>PRESCô24.0000ôTôOne DayôWrekinôArleen
>RECô27.0000ôQôTuesôWrekinôTony
>
>I use the "LOAD DATA" DDL statement:
>
>LOAD DATA LOCAL INFILE "data.txt" INTO TABLE squad FIELDS TERMINATED BY
>"ô" LINES TERMINATED BY "\n";
>
>But when I "select * from squad" MySQL returns:
>
>mysql> select * from squad;
>+----------+-------+------+----------+------------+---------+
>| Squad_id | Fee | Per | Days | Location | Coach |
>+----------+-------+------+----------+------------+---------+
> |LITE | 85.00 | Q | 4 Days | Lilleshall | Chris
> | | 75.00 | Q | Tues&Fri | Lilleshall | Sue
> | | 0.00 | | | Left |
> |PRESC | 24.00 | T | One Day | Wrekin | Arleen
>| REC | 27.00 | Q | Tues | Wrekin | Tony |
>+----------+-------+------+----------+------------+---------+
>5 rows in set (0.00 sec)
>
>Notice how the table (more specifically the data) is malformmed even
>thought the data in the file is visibly correct. Is there anything I
>can do to make sure that the data in the file is imported successfully.
>Also I use the field delimiter ô (ALT+147) because the data MAY contain
>your more standard delimiter "," (comma).
When I see this, it's typically because I've gotten carriage returns (\r)
into the file.
I'd take a look at the file with a utility that shows every character
(like a hex dumper or something similar) to see what's really in your
file.
