批量插入失败，最后一行上出现行终止符

问题描述

我使用以下命令将使用cygwin shell命令编译的CSV导入MS SQL 2014:

I'm importing a CSV compiled using cygwin shell commands into MS SQL 2014 using:

BULK INSERT import
from 'D:\tail.csv'
WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\r', FIRSTROW = 1)
GO

我已经确认每一行都包含一个\ r \ n.如果我在最后一行保留CR/LF，则批量导入将失败，并显示Msg 4832:

I have confirmed that each row contains a \r\n. If I leave a CR/LF on the last row the bulk import fails with Msg 4832:

如果我在最后一个数据行的末尾结束文件，则批量导入成功.

If I end the file at the end of the last data row then the bulk import succeeds.

对于非常大的CSV，解决此问题的一种混乱方法是查找行数，并将LASTROW设置用于BULK INSERT.是否有一种更优雅的方式让MS SQL知道，是的，最后一行已终止，并且不抱怨它或使它失败?

For very large CSVs a kludgy way around this problem is to find the number of rows and use the LASTROW setting for the BULK INSERT. Is there a more graceful way to let MS SQL know that, yes, the last row is terminated and not to complain about it or fail on it?

推荐答案

使用以下内容:

BULK INSERT import
from 'D:\tail.csv'
WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '0x0a', FIRSTROW = 1)
GO

这篇关于批量插入失败，最后一行上出现行终止符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！