Field 1 Corrupt

[grantf]

Hi, i am just starting out with mySQL and i am trying to load data into a new table. By following the guide this is what i have typed.

 

LOAD DATA LOCAL INFILE 'c:\file.csv' INTO TABLE people

FIELD TERMINATED BY ',';

 

i then get this as a responce

 

Query OK, 5 rows affected (0.02 sec)

Records: 5  Deleted: 0  Skipped: 0  Warnings: 0

 

then... when i SELECT * from people;

 

the first field out of 3 fields is missing characters, see below

 

+--------+-----------+------------+

| name  | city      | country    |

+--------+-----------+------------+

| ohn  | sydney    | australia

  | er  | melbourne | america

  |    | adelaide  | england

    |  | perth    | spain

    | e | brisbane  | italy

+--------+-----------+------------+

5 rows in set (0.00 sec)

 

this is a copy of the orignal data

 

john,sydney,australia

peter,melbourne,america

bob,adelaide,england

alex,perth,spain

george,brisbane,italy

 

the source file is very basic, created in excel and saves as csv file, double checked in notepad++

 

Does anyone know what i am doing wrong??

[beebum]

Try adding ...lines terminated by '\r\n'

[grantf]

Hi, not sure if your suggestion would have worked however i got it working by adding a comma at the end of the line, ie...

 

john,sydney,australia -> john,sydney,australia,

 

and now it works perfectly.

 

Thanks!