Problems putting CSV file into MySQL

Question

    Query: 

    LOAD DATA LOCAL INFILE 'actors.csv' 
    INTO TABLE Actors 
    FIELDS TERMINATED BY ','  
    ENCLOSED BY '"' 
    LINES TERMINATED BY '\n' 
    IGNORE 1 LINES 
    (ACTOR_ID, FNAME, LNAME);

CSV File: 
        ACTOR_ID, FNAME, LNAME
        "66666","Billy","Lou"
        "77777","Sally","Lou"
        "88888","Hilly","Lou"

        mysql> describe Actors;
            +----------+-------------+------+-----+---------+-------+
            | Field    | Type        | Null | Key | Default | Extra |
            +----------+-------------+------+-----+---------+-------+
            | ACTOR_ID | char(5)     | NO   | PRI |         |       |
            | FNAME    | varchar(20) | NO   |     | NULL    |       |
            | LNAME    | varchar(20) | NO   |     | NULL    |       |
            +----------+-------------+------+-----+---------+-------+

            > The output after running query:

            | 10047    | Shirley     | Jones         |
            | 10048    | Andre       | Vippolis      |
            | 66666    | Billy       | Lou"
            "77777  |
            | 88888    | Hilly       | "Lou"
                   |
            +----------+-------------+---------------+

I am trying to put a CSV file into my database. I've gotten the query from a MySQL tutorial (except put the values I have in there). When I run the query, My data is not properly inserted. I already have 2 rows inserted (10047, 10048) and then I try to put the data from the CSV file in, but it does not go in properly. It seems that the quotations are not being read properly. But the statement ENCLOSED BY '"' should handle the quotations. What am I doing wrong here?

Answer 1

CSV files frequently have a carriage return/line feed as the line terminator. If the file was generated using Excel, for example, you will almost definitely have that.

A way to correct that is to modify your code as follows:

LOAD DATA LOCAL INFILE 'actors.csv' 
    INTO TABLE Actors 
    FIELDS TERMINATED BY ','  
   ENCLOSED BY '"' 
   LINES TERMINATED BY '\r\n' 
   IGNORE 1 LINES 
   (ACTOR_ID, FNAME, LNAME);

I do most of my CSV importing that way.

Answer 2

It seems there is \r between

"Lou"
        "77777"

and not \n

Use text editor to correct this.

Found a related so post