The problem with bulk insert, the end character Ascii is 0D 0A

qizhenduo

How to bulk insert the ms sql server

kxiaper

I don't understand what the landlord asked
just for reference:
BULK INSERT dbname..tablename
FROM'd:\a.txt'
WITH (
    FIELDTERMINATOR =',', - separator
    ROWTERMINATOR = '\n' - line break
)

qizhenduo

Is ROWTERMINATOR ="????" - line break
This place (???) is not a newline character, but the terminator is not visible in the text at all, but the Ascii of this newline position is 0D 0A in the hex of ultraEdit
This file was exported from infomix by the unix operating system. You can see the line endings like "$" with vi, but you can't see this line endings in the Windows Notepad

Heroes please post

Never care about scores

bian_zxbb_

I also encountered this kind of problem today, so I can find it later, so I will reply here.
If the data comes from a text file from Informix (my data is), the newline character inside is /n, but /n is still treated as /r/n when using Bulk Insert, so use ROWTERMINATOR ='/n directly 'Is not possible, then it can be used indirectly, the encoding of the newline character is char(10), so,

1. In the program, it can be changed to (I use delphi, others are similar):'Bulk Inser ... ROWTERMINATOR =''' + char(10) +''''.

2. In addition, some people who checked on the Internet used the fmt template, which is fine, but it is better to go to bcp directly.
3. Finally, I still have a question. I want to change this to a stored procedure. If I do this, it will save a lot of work, but I didn't succeed. I hope someone can check it out. My idea is as follows:
CREATE PROCEDURE BulkInsert AS
DECLARE @lf CHAR(1)
DECLARE @cmd CHAR(255)
SET @RowTerm = CHAR(10)
SET @cmd ='bulk insert MyDB.dbo.MyTB FROM''C:\20170705.txt'' WITH(FIELDTERMINATOR =''|'',ROWTERMINATOR =``'+ @RowTerm +''')';
exec @cmd
GO
That CHAR(10) can’t be passed as a parameter, it can only be put in a stored procedure, but unfortunately it still can’t make it through this way. I hope someone can handle this.

一个人过

DECLARE @bulk_cmd varchar(1000);
SET @bulk_cmd ='BULK INSERT CallLog.TelstkTemp01 FROM''D:\Program\litz\data\History.txt'' with (FIELDTERMINATOR =``\t'', ROWTERMINATOR ='''+CHAR(10)+''')';
EXEC(@bulk_cmd);


that's it...