sqlsql-serverbulkinsertsec

SQL Server Bulk Insert Text File SEC Data

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' 
  );

SEC Tag Data

Solution

  • 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: Notepad++ Tab Delimited Screenshot

    SQL Server Screenshot of Bulk Insert: SQL Server Screenshot of Bulk Insert