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?

score 1 · Answer 1 · edited May 23 '17 at 11:53

1

It seems there is \r between

"Lou"
        "77777"

and not \n

Use text editor to correct this.

Found a related so post

edited May 23 '17 at 11:53

Community

1
1

answered Mar 03 '17 at 16:34

Anil

3,722
2
24
49

I'm not sure how to do this. Are you saying there is a \r in my CSV file? – XXIV Mar 03 '17 at 16:36
yes seems to be, use a text editor and enable special char display to review and correct it. – Anil Mar 03 '17 at 16:37
Thank you. The link was helpful and the other user helped me finalize my query. – XXIV Mar 03 '17 at 17:17

score 0 · Accepted Answer · answered Mar 03 '17 at 16:52

0

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.

answered Mar 03 '17 at 16:52

Menachem Bazian

397
2
5
18

That's what I'm gathering from the post that the above user posted. I'm going to try and implement that method. I'm creating the CSV files in excel and then saving them as a CSV file. There are multiple formats for CSV in excel. Which version uses the /r/n? – XXIV Mar 03 '17 at 16:57
I think they all do. Windows text files generally have a \r\n. – Menachem Bazian Mar 03 '17 at 17:02
It works. Thanks. But for some odd reason, when I save my excel file (no matter what CSV format) it puts extra quotations around my fields. It wasn't doing that earlier. Do you by chance know why it would be doing this? So it would appear as """Bill"""" instead of "Bill". – XXIV Mar 03 '17 at 17:16

Problems putting CSV file into MySQL

2 Answers2