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

T-SQL
1
2
3
4
5
BULK INSERT SomeTable
   FROM 'D:JunkdrawImportMe.txt'
   WITH (FIELDTERMINATOR = 't',
         FIRSTROW =2,
         ROWTERMINATOR = 'n')
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

T-SQL
1
2
3
4
5
BULK INSERT SomeTable
   FROM 'D:JunkdrawImportMe.txt'
   WITH (FIELDTERMINATOR = 't',
         FIRSTROW =2,
         ROWTERMINATOR = 'r')
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?

T-SQL
1
2
3
4
5
BULK INSERT SomeTable
   FROM 'D:JunkdrawImportMe.txt'
   WITH (FIELDTERMINATOR = 't',
         FIRSTROW =2,
         ROWTERMINATOR = 'l')
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

T-SQL
1
2
3
4
5
BULK INSERT SomeTable
   FROM 'D:JunkdrawImportMe.txt'
   WITH (FIELDTERMINATOR = 't',
         FIRSTROW =2,
         ROWTERMINATOR = CHAR(10) )
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

T-SQL
1
2
3
4
5
6
7
DECLARE @cmd varchar(1000)
SET @cmd = 'BULK INSERT SomeTable
FROM ''D:JunkdrawImportMe.txt''
WITH (      FIELDTERMINATOR = ''t'',
            FIRSTROW =2,
            ROWTERMINATOR = '''+CHAR(10)+''')'
EXEC(@cmd)
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