Bulk Insert From a Text File

I need to bulk insert a long, ugly CSV text file delimited by pipes.

This is how I did it:

BULK INSERT
[TestServer].[StagingSchema].[StagingTable]
FROM 'C:\WeeklyRate.csv'
WITH
( FIRSTROW = 2,
MAXERRORS = 0,
FIELDTERMINATOR = '|',
ROWTERMINATOR = '\n'
)

FIRSTROW simply tells SQL that the first row in the file is a header row and to start the import on the next row.

MAXERRORS is the number of errors I'm willing to allow before the process fails.

The FIELDTERMINATOR is whatever character the file uses to seperate fields. In my case, a pipe. If it were a tab I would put, '\t'.

The ROWTERMINATOR tells SQL where the end of the row is. It's usually a carriage return, '\n'. But you might find you have a trailing character at the end of the row. In that case, just stack the value to include the left over character. If I had a trailing pipe, my ROWTERMINATOR statement would be: FIELDTERMINATOR = '\|\n'