开发者

bulk insert txt error with ROWTERMINATOR

开发者 https://www.devze.com 2023-03-01 12:37 出处:网络
Have a txt file and have to pass it to sql A bulk insert BULK INSERT table FROM \'\\ \\ 01cends5 \\ TestBulk \\ a.txt\'

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


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

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号