# 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

• And C:\SQLPOWERDOC\CSV\DEA_Availability_ERROR.log is empty? – Max Vernon Jun 24 at 19:52
• Your column names have underscores which I don't see in the 1st line of the csv. – ypercubeᵀᴹ Jun 24 at 19:54
• The error log is empty. the column names in the CSV file don't make the column names in the tables, but is that required? I thought the bulk insert would skip the first line, because of 'FIRSTROW=2' – Patrick Jun 24 at 20:28
• I suggest you change ROWTERMINATOR='\r\n' to ROWTERMINATOR='\n' or ROWTERMINATOR = '0x0a'. You have an extra column in table definition and you did not declare any default for import_date. I am not sure KEEPNULLS will handle this column. – SqlWorldWide Jun 25 at 2:41
• If your data file just having one line of header and one line of data, do you have an endline of data line? – Dat Nguyen Jun 26 at 6:57

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.

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.