mysql - 将大型 CSV 文件导入 MySQL Workbench

Question

我正在对一个数据集进行数据分析，该数据集大约有。 140,000 条记录。我有 CSV 文件形式的数据集。我正在尝试将数据导入 MySQL 工作台，但它只处理(添加)第一条记录，并给出 1265 行被截断的警告。像这样的事情

“0 行受影响，1 条警告:1265 第 1 行“位置”列的数据被截断 记录:1 已删除:0 已跳过:1 警告:1“

我的实际查询是:

LOAD DATA LOCAL INFILE 'D:/PMK/SFPD_Incidents.csv' INTO TABLE incidents.incidents 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\r\n'
(IncidntNum, Category, Description, DayOfWeek, Date, Time, PdDistrict, Resolution, Address, X, Y, Location);

现在，当我对记录较少的小数据集运行相同的查询时，除了截断警告之外，它完全可以正常工作。

csv 内容如下所示:

141043385,SUSPICIOUS OCC,SUSPICIOUS OCCURRENCE,Thursday,12/11/2014,21:09,SOUTHERN,NONE,800 Block of BRYANT ST,-122.403404791479,37.775420706711,"(37.775420706711, -122.403404791479)"
141043523,NON-CRIMINAL,"AIDED CASE, MENTAL DISTURBED",Thursday,12/11/2014,19:28,SOUTHERN,NONE,800 Block of MARKET ST,-122.407633520742,37.7841893501425,"(37.7841893501425, -122.407633520742)"
141042804,LARCENY/THEFT,GRAND THEFT PICKPOCKET,Thursday,12/11/2014,15:45,TENDERLOIN,NONE,100 Block of OFARRELL ST,-122.407244087032,37.7865647607685,"(37.7865647607685, -122.407244087032)"
141042622,SUSPICIOUS OCC,SUSPICIOUS OCCURRENCE,Thursday,12/11/2014,15:00,TARAVAL,NONE,0 Block of FUENTE AV,-122.477993371794,37.7193838455847,"(37.7193838455847, -122.477993371794)"
141042622,OTHER OFFENSES,FALSE PERSONATION,Thursday,12/11/2014,15:00,TARAVAL,NONE,0 Block of FUENTE AV,-122.477993371794,37.7193838455847,"(37.7193838455847, -122.477993371794)"

非常感谢任何帮助或修复。

Answer 1

您应该尝试验证/验证它。

我在处理大型 CSV 文件时也遇到过问题，但大多数时候这些值都有问题。

因此，可能有一个值会破坏您的导入。