SQL Server Bulk Insert Text File SEC Data

Question

I am trying to do a bulk insert from the SEC text file named tag. A picture is shown below which includes several columns. I have a table that I am trying to insert the data into but it inserts a single row and so somehow I think the delimiters or something are messed up. Here is the DDL for a table In SQL Server:

CREATE TABLE [dbo].[Tag1](
    [tag] [char](1000) NULL,
    [version] [char](5000) NULL,
    [custom] [char](100) NULL,
    [abstract] [char](100) NULL,
    [datatype] [char](500) NULL,
    [iord] [char](22) NULL,
    [crdr] [char](22) NULL,
    [tlabel] [varchar](max) NULL,
    [doc] [varchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

And here is the code I am using to do a bulk insert. It only inserts a single row and I wonder if I haven't correctly specified the delimiter.

BULK INSERT dbo.Tag1
FROM 'F:\SEC\FirstQuarter2020\Tag.txt'
WITH 
  (
    FIELDTERMINATOR = '	', 
    ROWTERMINATOR = '
' 
  );

user_6026385 · Answer

The only way I was able to get it to work was to remove the ROWTERMINATOR from the BULK INSERT and leave just the for New Line\Line Feed. Now I don't have your exact file but I was able to replicate my own version. I tested this using csv and a tab delimited version.

BULK INSERT dbo.Tag1
FROM 'C:\STORAGE\Tag.txt'
WITH 
  (
    FIRSTROW = 2, --First row is header
    FIELDTERMINATOR = '	', 
    ROWTERMINATOR = '
'
  );

SELECT *
FROM dbo.Tag1

In Notepad++ I do see that there is actually a ... you can see this in Notepadd++ as CR LF. But for some reason the ROWTERMINATOR when using for the Bulk Insert ends up inserting everything on one single line as you said in your post.

Notepad++ Tab Delimited Screenshot:

SQL Server Screenshot of Bulk Insert:

user_14827211 · Answer

Here is what worked! The field terminator needed to be in hex so thank you for pointing me to that!

 BULK INSERT dbo.Tag1
    FROM 'F:\SEC\FirstQuarter2020\Tag.txt'
    WITH 
      (
        FIELDTERMINATOR = '	', 
        ROWTERMINATOR = '0x0a' 
      );

SQL Server Bulk Insert Text File SEC Data

2 Answers