BULK INSERT does not fail when file contains commas instead of semicolons when FIRSTROW > 1

Question

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.

Table:

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

Format file:

12.0
3
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:

1,first,0
2,second,2

Bulk insert:

DECLARE @cmd VARCHAR(4000);
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;

Answer 1

Change the command From

, FIELDTERMINATOR = '';''

to

, FIELDTERMINATOR = '',''

This should do the trick

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

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

Answer 2

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.