1

I have a UTF-8 file with a ¬ field delimiter and [CR][LF] row terminator. The SQL Version is Microsoft SQL Server 2014 (SP2-CU7) (KB4032541) - 12.0.5556.0 (X64)

I am using bulk insert to load the txt tile in a table as below:

DECLARE @bulk_cmd varchar(1000)

SET @bulk_cmd = 'BULK INSERT [test].pcb_Load
FROM ''T:\pcb_Workspace_2\Outputs\pcb\Load\pcb_MS_TEST_2.txt''
WITH (FIELDTERMINATOR = ''¬'', ROWTERMINATOR = '''+CHAR(10)+''',FIRSTROW=2)'

EXEC (@bulk_cmd)

SELECT * FROM test.pcb_load

I tried to use code page 65001, Row terminator \r,\n,\r\n,0x0a but nothing works and I get the following error.

Msg 4866, Level 16, State 8, Line 1
The bulk load failed. The column is too long in the data file for row 1, column 1. Verify that the field terminator and row terminator are specified correctly.

Msg 7301, Level 16, State 2, Line 1
Cannot obtain the required interface ("IID_IColumnsInfo") from OLE DB provider "BULK" for linked server "(null)".

So after removing codepage and using CHAR(10) (i.e. above script) my file does load but leaves a hyphen at the end of every string, and in null columns as well.

Output looks like this

Col1    Col2    Col3        Col4
-       0-      pcb1412-    DD-
-       0-      pcb2134-    GFD-
-       0-      pcb324-     DDX-
-       0-      pcb1234-    DDR-

The data is correct, however the hyphen is not in the file.

bad_coder
  • 11,289
  • 20
  • 44
  • 72
BIQuad
  • 15
  • 4

1 Answers1

0

Change your ROWTERMINATOR to the hex value '0x0a' for LF and CR just to be explicit, though that isn't the source of your real problem. I think you are going to have to convert your file to UTF-16. I know earlier versions of SQL Server didn't support UTF-8 and I don't think 2012 does either but could be wrong.

S3S
  • 24,809
  • 5
  • 26
  • 45
  • 1
    I had tried it with hex value but no luck. You are correct though, it seems it would come down to converting the file UTF-16. – BIQuad Sep 27 '17 at 14:25