Sql-server – Bulk insert inserting 0 rows

bulk-insertsql server

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

Best Answer

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.

Related Question