I like to use BULK INSERT or bcp as much as possible, this is especially true if all I need to do is dump the file into a table. For more complex things I will go the SSIS route. Files are generated by all kinds of systems these days, these can be Windows, *nix, Mac, Amiga and other systems.

Make sure to use something other than notepad when dealing with these files and you want to look at them. Notepad++ or Editplus have more functionality and are many times faster than notepad. The nice thing about either of these is that you can see control characters.

See how you can see the linefeeds here? Can’t do that in notepad

So let’s say you get a file where the row terminator is a linefeed, how would you specify that as a row terminator in BULK INSERT?

You can try newline

BULK INSERT SomeTable
   FROM 'D:JunkdrawImportMe.txt'
   WITH (FIELDTERMINATOR = 't',
         FIRSTROW =2,
         ROWTERMINATOR = 'n')

Nope, that doesn’t work, you get 0 rows inserted

You can try carriage return

BULK INSERT SomeTable
   FROM 'D:JunkdrawImportMe.txt'
   WITH (FIELDTERMINATOR = 't',
         FIRSTROW =2,
         ROWTERMINATOR = 'r')

Nope, that doesn’t work either, you get 0 rows inserted

What about l for linefeed?

BULK INSERT SomeTable
   FROM 'D:JunkdrawImportMe.txt'
   WITH (FIELDTERMINATOR = 't',
         FIRSTROW =2,
         ROWTERMINATOR = 'l')

You get an error

Msg 4864, Level 16, State 1, Line 1

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 1 (SomeDate).

What about if you try a CHAR(10) which is a linefeed

BULK INSERT SomeTable
   FROM 'D:JunkdrawImportMe.txt'
   WITH (FIELDTERMINATOR = 't',
         FIRSTROW =2,
         ROWTERMINATOR = CHAR(10) )

You get this error

Msg 102, Level 15, State 1, Line 5

Incorrect syntax near ‘CHAR’.

Mmm, what if you embed it from within Dynamic SQL

DECLARE @cmd varchar(1000)
SET @cmd = 'BULK INSERT SomeTable
FROM ''D:JunkdrawImportMe.txt''
WITH (      FIELDTERMINATOR = ''t'',
            FIRSTROW =2,
            ROWTERMINATOR = '''+CHAR(10)+''')'
EXEC(@cmd)

Bingo, that works. Keep this in mind next time you get a file with a linefeed and you are struggling importing that file