mysql - 将csv文件加载到mysql数据库中

Question

我正在尝试将一个大的 CSV 文件加载到 mysql 中，但无法找出失败的原因。

我的 CSV 文件如下所示:

_id,"event","unit","created","r1","r2","r3","r4","space_id","owner_id","name","display_name","users__email"
565ce313819709476d7eaf0e,"create",3066,"2015-12-01T00:00:19.604Z","563f592dd6f47ae719be8b38","3","13","7","55ecdd4ea970e6665f7e3911","55e6e3f0a856461404a60fc1","household","household","foo.bar@ace.com"
565ce350819709476d7eaf0f,"complete",3067,"2015-12-01T00:01:19.988Z","21","","","","55e6df3ba856461404a5fdc9","55e6e3f0a856461404a60fc1","Ace","Base","foo.bar@ace.com"
565ce350819709476d7eaf0f,"delete",3067,"2015-12-01T00:01:19.988Z","21","","","","55e6df3ba856461404a5fdc9","55e6e3f0a856461404a60fc1","Ace","Base","foo.bar@ace.com"
565ce350819709476d7eaf0f,"update",3067,"2015-12-01T00:01:19.988Z","21","","","","55e6df3ba856461404a5fdc9","55e6e3f0a856461404a60fc1","Ace","Base","foo.bar@ace.com"

我将文件加载到 mysql 的代码是这样的:

CREATE DATABASE IF NOT EXISTS analys;

USE analys;

CREATE TABLE IF NOT EXISTS event_log (
  _id CHAR(24) NOT NULL,
  event_log VARCHAR(255),
  unit CHAR(4),
  created VARCHAR(255),
  r1 VARCHAR(255),
  r2 VARCHAR(255),
  r3 VARCHAR(255),
  r4 VARCHAR(255),
  space_id VARCHAR(255),
  owner_id VARCHAR(255),
  name VARCHAR(255),
  display_name VARCHAR(255),
  users__email VARCHAR(255),
  PRIMARY KEY (_id)
)

LOAD DATA INFILE 'audits.export.csv' 
INTO TABLE event_log
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n\r'
IGNORE 1 ROWS;

一切都很好，包括查询，但我在每一列(只有一行)中都得到NULL。

这是操作输出:

22:31:21    LOAD DATA INFILE 'audits.export.csv'  INTO TABLE event_log FIELDS TERMINATED BY ','  ENCLOSED BY '"' LINES TERMINATED BY '\n\r' IGNORE 1 ROWS   0 row(s) affected Records: 0  Deleted: 0  Skipped: 0  Warnings: 0   0.156 sec

我尝试调整表和加载查询，但不起作用

我使用的是 Windows 7，使用 Mysql 5.6 和 Workbench。我在这里听说过 GUI 解决方案或 Excel 连接器 ( Excel Connector )，但我更喜欢以编程方式执行此操作，因为我需要重用代码。

有什么帮助吗？我无法通过 Stackoverflow 上的类似帖子解决问题。

Answer 1

这似乎不是有效的换行符:

LINES TERMINATED BY '\n\r'

更改为:

LINES TERMINATED BY '\r\n'

或者仅其中之一(可以是单个 \n 或 \r，具体取决于系统或创建 csv 文件的软件)。