SQL Server Bulk Insert Text File SEC Data-CodePudding

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 = '\t', 
    ROWTERMINATOR = '\r\n' 
  );

CodePudding user response：

The only way I was able to get it to work was to remove the \r ROWTERMINATOR from the BULK INSERT and leave just the \n 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 = '\t', 
    ROWTERMINATOR = '\n'
  );

SELECT *
FROM dbo.Tag1

In Notepad I do see that there is actually a \r\n... you can see this in Notepadd as CR LF. But for some reason the ROWTERMINATOR when using \r\n 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:

CodePudding user response：

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 = '\t', 
        ROWTERMINATOR = '0x0a' 
      );