使用 ROWTERMINATOR 批量插入 txt 错误

发布于 2024-11-04 01:08:30 字数 440 浏览 5 评论 0原文

有一个 txt 文件，必须将其传递给 sql

批量插入

BULK INSERT table
FROM '\ \ 01cends5 \ TestBulk \ a.txt'
WITH (
DATAFILETYPE = 'char'
FIELDTERMINATOR = '|'
ROWTERMINATOR = '\ n ',
FIRSTROW = 1,
LASTROW = 15
)

但它不会作为最后一行 ROWTERMINATOR 并探测所有内容并执行不工作
{CR} {LF}{LF}{CR}\ n\ r\ r \ n\ n \ r

我的 txt 格式是：

0 | 20276708598 | 119302 | 119302 201101 | 201101 000000 | 000000

原文

Have a txt file and have to pass it to sql

A bulk insert

BULK INSERT table
FROM '\ \ 01cends5 \ TestBulk \ a.txt'
WITH (
DATAFILETYPE = 'char'
FIELDTERMINATOR = '|'
ROWTERMINATOR = '\ n ',
FIRSTROW = 1,
LASTROW = 15
)

But it do not take as a final line ROWTERMINATOR and probe everything and does not work
{CR} {LF}{LF}{CR}\ n\ r\ r \ n\ n \ r

My txt format is:

0 | 20276708598 | 119302 | 201101 | 000000 | 000000

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

氛圍 2024-11-11 01:08:30

看起来 '\r' 转换为 0x0A 有问题，至少在我的例子中是这样。

http://dbaspot.com/sqlserver-programming/463913 -bulk-insert-rowterminator-failing.html#post1509360 演示如何使用 exec 执行此操作并使用 char() 函数指定 rowterminator。这对我有用。粘贴上面链接中的示例代码：

DECLARE @bulk_cmd varchar(1000)
SET @bulk_cmd = 'BULK INSERT [GRC].[dbo].[UP_040109]
FROM ''C:\TEMP\up\524.d.0''
WITH (ROWTERMINATOR = '''+CHAR(10)+''',FIELDTERMINATOR = ''\t'')'
EXEC(@bulk_cmd)

It looks like something is wrong with '\r' translation to 0x0A, at least in my case.

http://dbaspot.com/sqlserver-programming/463913-bulk-insert-rowterminator-failing.html#post1509360 demonstrates how to do it with exec and specifying of rowterminator with char() function. It worked for me. Paste of sample code from the link above:

DECLARE @bulk_cmd varchar(1000)
SET @bulk_cmd = 'BULK INSERT [GRC].[dbo].[UP_040109]
FROM ''C:\TEMP\up\524.d.0''
WITH (ROWTERMINATOR = '''+CHAR(10)+''',FIELDTERMINATOR = ''\t'')'
EXEC(@bulk_cmd)

回复收藏 0 原文

~没有更多了~