0

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

  • 2
    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
0

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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.