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:SQLPOWERDOCCSVDEA_Availability.csv' with ( FIRSTROW=2, FIELDTERMINATOR=',', ROWTERMINATOR='rn', ERRORFILE = 'C:SQLPOWERDOCCSVDEA_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 rn
To get this working:
- Change the ROWTERMINATOR to ‘n’. I’m not sure why ‘rn’ 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.
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
- Create a format file for the table
- Modify the format file to have it ignore the
- Remove the
KEEPNULLSfrom my command
- I did add a default value of
import_datefield, 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.