Bulk Insert Concordance Formatted DAT File with FMT Format File Error

Question

I'm using SQL Server 2017. I'm trying to BULK INSERT into SQL Server a number of .DAT files that are delimited with a Pilcrow (¶) for columns and a Lower Case Thorn (þ) for text (this is a common delimiter format among e-discovery platforms). Here is an example of the data files:

My understanding is that I need a format file to do this. I've created the following FMT file (note I have a "dummy" line that adds a preceding text delimiter):

I then run the following SQL statement:

BULK INSERT [dbo].[tblERRORS]   
   FROM 'C:\Users\myName\Documents\Errors\ErrorFile.dat'  
   WITH (FirstRow = 2, FORMATFILE = 'C:\Users\myName\Documents\Errors\Errors.fmt');  
GO

I get the following errors:

Msg 4832, Level 16, State 1, Line 201
Bulk load: An unexpected end of file was encountered in the data file.

Msg 7399, Level 16, State 1, Line 201
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.

Msg 7330, Level 16, State 2, Line 201
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

NOTE: I've also tried changing the last line in the FMT file to "þ\r\n" which doesn't work.

Any suggestions would be appreciated.

Thank you.

@vitaut: Not sure why this got a downvote? Would you provide some clarification? Thx. — Craig, May 18 '20 at 05:17
dont view the file in notepad. You need to use a better text editor/viewer. As I stated in my solution below, "this assumes the last two bytes of each line really are \r\n". Can you view the file with someting like ultraedit or Notepad++? Something that will show the hidden characters (might need to enable view of them with options). — jamie, May 19 '20 at 18:27
Adding "þ" to your line terminator did "work". You really do need that. But there is something else wrong as well. — jamie, May 19 '20 at 18:28

score 0 · Answer 1 · answered May 17 '20 at 15:45

It seems pretty difficult to load the file with those specific characters as delimiters using format file. You can try loading the file using the following method (require some extra work):

drop table if exists  [dbo].[tblERRORS]   

CREATE TABLE [dbo].[tblERRORS]   
(dummy nvarchar(255), DOCID nvarchar(255), dummy2 nvarchar(255), PARENT_DOCID nvarchar(255), dummy3 nvarchar(255), ATTACH_DOCID nvarchar(255), dummy4 nvarchar(255), BEGINBATES nvarchar(255), dummy5 nvarchar(255), ENDBATES nvarchar(255), dummy6 nvarchar(255))

BULK INSERT D20.[dbo].[tblERRORS] FROM 'C:\Users\myName\Documents\Errors\ErrorFile.dat' 
WITH(FIRSTROW = 2, ROWTERMINATOR = '\n',
fieldterminator= 'Ã¾'
);  
GO

SELECT * FROM DBO.TBLERRORS

Result:

Thanks for this. Yeah, I get a lot of these file types. I guess I need a quick way to get these files in to SQL without having to import each file through SSMS - figured BULK INSERT would be one way to do this. — Craig, May 18 '20 at 05:16
@Craig Yeah, you can loop through the files (using [xp_dirtree](https://stackoverflow.com/questions/11559846/how-to-list-files-inside-a-folder-with-sql-server)) and load those files in one go. — sacse, May 18 '20 at 08:49

score 0 · Answer 2 · answered May 19 '20 at 16:29

You just need to add to your line terminator.. the last field terminator. Your line is not terminated by the two bytes "\r\n"... It is terminated by three bytes... "Lower Case Thorn (þ)\r\n"

This assumes the last two bytes of each line really is \r\n The error you are getting is common with incorrect line terminators.

Bulk Insert Concordance Formatted DAT File with FMT Format File Error

2 Answers2