问题描述
具有 txt
文件,并将其传递给 sql
Have a txt
file and have to pass it to 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
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
我的txt格式为:
0 | 20276708598 | 119302 | 201101 | 000000 | 000000
推荐答案
'\r'转换为0x0A似乎有点问题至少就我而言。
It looks like something is wrong with '\r' translation to 0x0A, at least in my case.
演示了如何使用exec以及如何使用char()函数指定rowterminator。它为我工作。从上面的链接粘贴示例代码:
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)
这篇关于使用ROWTERMINATOR批量插入txt错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!