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

You need to check file **row terminator**. Notepad++, or similar software could be handy. — Yitzhak Khabinsky, Sep 01 '22 at 21:07
`char` seems like an odd choice of data type for some that is 5,000 characters. I'd the value really always exactly 5,000 characters long? — Thom A, Sep 01 '22 at 21:10

score 1 · Accepted Answer · answered Sep 01 '22 at 22:07

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:

score 0 · Answer 2 · answered Sep 01 '22 at 22:02

0

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

answered Sep 01 '22 at 22:02

TenkMan

27
4

That makes sense that my answer and your answer would both work as 0x0A is the hex equivalent of \n. 0x0A is equal to decimal 10 (ten). This is equal to the ASCII code for LF (Line Feed), which is a newline on many systems. But both answers are the same and from what I can tell the mistake is trying to use both carriage return and a line feed. In Notepadd++... in order to locate the END OF LINE you must use \r\n – Code Novice Sep 01 '22 at 22:18

SQL Server Bulk Insert Text File SEC Data

2 Answers2