Importing .csv to MySQL

[EndingDesire]

Currently i'm trying to import a bunch of records that are in csv format and have this look to them,

AL3,South East Bedfordshire,England,ENG,51.8479,-0.4474
AL5,South East Bedfordshire,England,ENG,51.8332,-0.3815

When adding the csv file to my database it doesn't seem to interpret the end of the line correctly. I'm using the following settings:
CSV using Load Data
fields terminated by,
Fields enclosed by ,
Fields escaped by \
lines terminated by auto
and the appropriate column names.


After the import the table looks like this,

AL3 South East Bedfordshire England ENG 51.8479 -0.4474
AL5

It seems to take the AL5 from the second record and append it to the last part of the first record, and stops. Can anyone shed light on how I can make this continue throughout the rest of the file?

 

[angelwatt]

Do you know what kind of line endings the CSV file is set for? Editors like TextWrangler will show it is set for Mac/Unix/Windows. This could be impacting this.

 

[EndingDesire]

Text wrangler says,

Line Breaks: Classic Mac (CR)

Is there a keystoke to denote this in mysql import?

 

[angelwatt]

I found this argument that you can try,

--lines-terminated-by="\r"

 

[Darth.Titan]

Leave off the

fields enclosed by ,

line. The fields can't be terminated by AND enclosed by commas after all.

 

[EndingDesire]

Adding a \r to the line terminated by seemed to work. However, now I have some other importing questions.

I downloaded a .csv sheet of alot of information similar to the above

AL3,South East Bedfordshire,England,ENG,51.8479,-0.4474
AL5,South East Bedfordshire,England,ENG,51.8332,-0.3815

However, what if, for instance one of the records is as follows,

AL3,South East Bedfordshire,,ENG,51.8479,-0.4474

The import seems to mess up the ,, and just start to perform very strange actions. Is there a way I can battle this?

 

[BertyBoy]

Yeah, it might.

I normally process my CSVs from third-parties

= to validate each row has the correct number of fields, you don't need to do this.
= to turn them into tab-separated files, .tsv - just so I know if it's been processed or not, you don't need to do this.
= to correct those double commas, inserting a \N so that the import treats it as a NULL. Make sure these columns are NULLABLE. You will need to do this.