I encountered an issue where I received a CSV file that supposed to be delimited by semicolons (;) but was delimited by commas (,). Bulk insert called by sqlcmd did not fail but did not insert either. I know that calling xp_cmdshell is not best practice but please don't comment on this.

After investigation I found that it only fails (as expected) when FIRSTROW = 1, but I need a header inside the file.


CREATE TABLE [dbo].[test_table](
    [id] [int] NULL,
    [title] [varchar](10) NULL,
    [val] [int] NULL

Format file:

1       SQLCHAR              0       4       ";"    1     "id"             ""
2       SQLCHAR              0       10      ";"    2     "title"          SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR              0       4       "\r\n"     3     "val"            ""

Data file:


Bulk insert:

SET @cmd = 'sqlcmd -b -S <servername> -Q "set nocount on; set dateformat dmy; bulk insert [test_db].[dbo].[test_insert] from ''C:\temp\test_table.csv'' with ( DATAFILETYPE = ''char'', TABLOCK, MAXERRORS = 1000, FIELDTERMINATOR = '';'', ROWTERMINATOR = ''\r\n'', BATCHSIZE = 100000, FORMATFILE = ''C:\temp\test_table.txt'', FIRSTROW = 2 );"';
EXEC xp_cmdshell @cmd;

2 Answers 2


Change the command From




This should do the trick

BULK INSERT [test_db].[dbo].[test_insert]
FROM 'C:\temp\test_table.csv'
    FORMAT = 'CSV', 
    FIELDQUOTE = '"',
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',  --CSV field delimiter
    ROWTERMINATOR = '\n',   --Use to shift the control to next row

As the ROWTERMINATOR goes <ou should try it or switch to \r\n

  • The file is supposed to have semicolons but by mistake it was received with commas. Expected behavior is that BULK INSERT fails. But it neither failed nor inserted the data. I need to see in error log, that there is file that was not loaded to DB.
    – owl
    Dec 4, 2020 at 15:30
  • try first the bulk inbsert in ssms and see if you get an error. I added a Version that works with me, you may want have to adpt it, when this runs ok, with a small sample like you presented here. then you can switch to your comand
    – nbk
    Dec 4, 2020 at 15:51
  • Tunny thing is, that I want to get an error, because that is how I expect it to behave when incorrect file is provided. Otherwise I lean to consider it a bug in BULK INSERT.
    – owl
    Dec 7, 2020 at 14:49

It seems that it behaves correctly. While there are no field terminators in the file, BULK INSERT considers whole file as data for first field of the header. Then there is nothing left to INSERT.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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