Bulk insert inserting 0 rows I am trying to use bulk insert to insert data from a csv file into a sql server table, but it is returning 0 rows.

This is the bulk insert statement I am using

BULK INSERT dbo.DEA_Availability from 'C:\SQLPOWERDOC\CSV\DEA_Availability.csv' 
with (
FIRSTROW=2,
FIELDTERMINATOR=',', 
ROWTERMINATOR='\r\n',
ERRORFILE = 'C:\SQLPOWERDOC\CSV\DEA_Availability_ERROR.log',
DATAFILETYPE='char',
keepnulls
)


The table definition is:

CREATE TABLE [dbo].[DEA_AVAILABILITY](
    [Server_Name] [varchar](max) NULL,
    [Database_Name] [varchar](max) NULL,
    [Priority] [varchar](max) NULL,
    [Description] [varchar](max) NULL,
    [Details] [varchar](max) NULL,
    [URL] [varchar](max) NULL,
    [IMPORT_DATE] [datetime2](7) NULL
) ON [PRIMARY]
GO


This is the contents of the CSV file

Server Name,Database Name,Priority,Description,Details,URL
HOSTNAME,,None,Cluster Node,This is a node in a cluster.,http://msdn.microsoft.com/en-us/library/ms189134(v=sql.100).aspx

I have verified that each line of the CSV file does end with \r\n
To get this working:


Change the ROWTERMINATOR to '\n'. I'm not sure why '\r\n' isn't
working, but '\n' works with your example data.
Remove KEEPNULLS. NULL values in the CSV for a nullable column will still be brought through as NULL to the database, but this
prevents the NULL for IMPORT_DATE coming through.
Change IMPORT_DATE to have a DEFAULT value of GETDATE() but leave it as nullable (otherwise the import fails)
Add IMPORT_DATE as a column in the CSV
Add a new line after your last data row to ensure the final field is terminated in a way that SQL can detect when importing.


After these changes, the data imports correctly. NOTE: If you want IMPORT_DATE to come through as NULL instead of using the current date, skip step 3.