List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:January 6 2002 5:57pm
Subject:Re: Strange LOAD DATA problem
View as plain text  
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.
Thread
Strange LOAD DATA problemDavid Ayliffe6 Jan
  • Re: Strange LOAD DATA problemPaul DuBois6 Jan
RE: Strange LOAD DATA problemDavid Ayliffe6 Jan