mySQL Server function LOAD DATA INFILE producing odd output-CodePudding

I am trying to load a .csv file into mySQL Server. I have to do this in the terminal. My process for importing the .csv is as follows:

mysql> CREATE TABLE Dealers ( Dealer_ID INT PRIMARY KEY, Dealer_Name VARCHAR(50));
mysql> LOAD DATA INFILE '/dealer_df.csv' INTO TABLE Dealers FIELDS TERMINATED BY ',' IGNORE 1 ROWS (Dealer_ID, Dealer_Name);

Data:

1,melodytoyota
2,toyotasunnyvale
3,toyota101
4,sftoyota
5,autonationtoyotahayward
6,hondaofstevenscreek
7,autonationhondafremont
8,sfhonda
9,southbayhonda
10,citytoyota
11,fremonttoyota
12,pierceytoyota
13,larryhopkinshonda
14,andersonhonda
15,honda-rc
16,capitolhonda
17,putnamtoyota

output: (This formatting may look wrong, but you're seeing the output that I see. This is also what it looks like in the terminal.)

mysql> SELECT * FROM Dealers;
 ----------- -------------------------- 
| Dealer_ID | Dealer_Name              |
 ----------- -------------------------- 
|         1 | melodytoyota
           |
         |2 | toyotasunnyvale
               |yota101
                |oyota
 |        5 | autonationtoyotahayward
     |    6 | hondaofstevenscreek
  |       7 | autonationhondafremont
                 |nda
           || southbayhonda
              |itytoyota
           || fremonttoyota
           || pierceytoyota
       | 13 | larryhopkinshonda
           || andersonhonda
                |da-rc
            | capitolhonda
            | putnamtoyota
 ----------- -------------------------- 
17 rows in set (0.00 sec)

I tried performing the exact same sets on a different table, with different data, and it worked just fine.

I am looking for a table like this:

mysql> SELECT * FROM Dealers;
 ----------- -------------------------- 
| Dealer_ID | Dealer_Name              |
 ----------- -------------------------- 
|          1| melodytoyota             |
|          2| toyotyasunnyvale         |
... etc ...
|         16| capitolhonda             |
|         17| putnamtoyota             |
 ----------- --------------------------

Output of

mysql> SELECT Dealer_ID, hex(Dealer_Name) FROM Dealers;

Here are a few lines of the output. Weird Formatting is mine, table looks clean in the terminal.

|         1 | 6D656C6F6479746F796F74610A0D
|         2 | 746F796F746173756E6E7976616C650D
|         3 | 746F796F74613130310D
|         4 | 7366746F796F74610D
|         5 | 6175746F6E6174696F6E746F796F7461686179776172640D
|         6 | 686F6E64616F6673746576656E73637265656B0D

So, I'm looking at line 3, and it seems like all the text data is there, but it's not displaying properly. What the heck is going on here? I'm pretty new to SQL, so I'm a little out of my depth here.

CodePudding user response：

Your comma separate value file has inconsistent line breaks:

While 1st line is terminated by 0A0D (\n\r), subsequent lines are terminated by 0D (\r).

Remove 0A from the first line of your csv file, additionally append LINES TERMINATED BY '\r' to your LOAD DATA INFILE sql statement.