Bulk insert inserting 0 rows

Question

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

Answer 1

To get this working:

1. Change the ROWTERMINATOR to '\n'. I'm not sure why '\r\n' isn't working, but '\n' works with your example data.
2. 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.
3. Change IMPORT_DATE to have a DEFAULT value of GETDATE() but leave it as nullable (otherwise the import fails)
4. Add IMPORT_DATE as a column in the CSV
5. 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.

Answer 2

I was finally able to get back to this process and get it working. I was able to get it working by doing the following

1. Create a format file for the table
2. Modify the format file to have it ignore the import_date column
3. Remove the KEEPNULLS from my command
4. I did add a default value of GETDATE() to the import_date field, I don't think this was require, but it made sense to have and is a better design

I believe the format file was required, because it wasn't feasible to add the import_Date field to every CSV file. The table/csv in question was just one of a group of 84 files/tables that are generated multiple times a day, and I don't have control over the format that the files are generated in.