LOAD DATA LOCAL inffile不拾取CSV文件中的所有值

Question

我试图通过命令行加载一个CSV文件到MySQL，但很多值只是没有出现。以下是我的查询:

LOAD DATA LOCAL INFILE '/Users/zachsmith/Dropbox/1_computer_business/2014_MSc/assignments/databases/CSCresults_no_headers.CSV' INTO TABLE Temp FIELDS TERMINATED BY ',' ENCLOSED BY '"' (Year, StudentID, Degree, CourseCode, CourseDescrip, Session, Code, Percent, Symbol, Gender, SAstatus, ForeignCountry);

和添加的内容看起来像这样:

mysql> select * from Temp
    -> ;
+------+-----------+--------+-------------+--------------------------------+--------------+------+---------+--------+--------+----------+-----------------------------+
| Year | StudentID | Degree | CourseCode  | CourseDescrip                  | Session      | Code | Percent | Symbol | Gender | SAstatus | ForeignCountry              |
+------+-----------+--------+-------------+--------------------------------+--------------+------+---------+--------+--------+----------+-----------------------------+
                   | EB022  | CSC1015F    | Computer Science 1015          | Semester One | UGRD |      83 | 1      | M      | F        | Tanzania
                   | EB022  | CSC1016S    | Computer Science 1B            | Semester Two | UGRD |      60 | 2-     | M      | F        | Tanzania
                   | SB006  | CSC1010H    | Compass I -CompScAcadAssProg   | Full Year    | UGRD |      48 | SF     | M      | F        | Tanzania
                   | SB006  | CSC1015F    | Computer Science 1015          | Semester One | UGRD |      26 | F      | M      | F        | Tanzania
                      |001  | CSC6000W    | PhD in Computer Science        | Full Year    | DOCT |       0 |        | M      | F        | Sudan
                   | SB006  | CSC1010H    | Compass I -CompScAcadAssProg   | Full Year    | UGRD |      56 | 3      | M      | F        | Tanzania
                   | SB006  | CSC1015F    | Computer Science 1015          | Semester One | UGRD |      41 | F      | M      | F        | Tanzania
                           || CSC1010H    | Compass I -CompScAcadAssProg   | Full Year    | UGRD |      64 | SP     | M      | C        | 
                           || CSC3002F    | Computer Science 302           | Semester One | UGRD |      70 | 2+     | M      | C        | 
                           || CSC3003S    | Computer Science 303           | Semester Two | UGRD |      70 | 2+     | M      | C        | 
                           || CSC3002F    | Comput

很多东西都被剪掉了——比如日期和学生号(这是假数据)，甚至第5行一半的学位代码……

所有字段都是varchar(30)，因为我只是将所有内容视为字符串。为什么会这样?

我看了Excel，它把所有

Answer 1

尝试用文本编辑器打开csv文件。查看字段是否真的是TERMINATED BY ',' ENCLOSED BY '"'，并且每一列对应于Temp表中的相同字段。请记住，如果CSV字段中没有任何值，则仍应将其用双引号""括起来。