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?

Community · Accepted Answer · 2017-05-23 11:53:53Z

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, 2017 at 11:53

CommunityBot

11 silver badge

answered Mar 3, 2017 at 16:34

Anil

3,7522 gold badges27 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

XXIV Over a year ago

I'm not sure how to do this. Are you saying there is a \r in my CSV file?

Anil Over a year ago

yes seems to be, use a text editor and enable special char display to review and correct it.

XXIV Over a year ago

Thank you. The link was helpful and the other user helped me finalize my query.

Menachem Bazian · Accepted Answer · 2017-03-03 16:52:53Z

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 3, 2017 at 16:52

Menachem Bazian

4272 gold badges6 silver badges19 bronze badges

3 Comments

XXIV Over a year ago

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?

Menachem Bazian Over a year ago

I think they all do. Windows text files generally have a \r\n.

XXIV Over a year ago

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".

Collectives™ on Stack Overflow

Problems putting CSV file into MySQL

2 Answers 2

3 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related