BULK INSERT does not fail when file contains commas instead of semicolons when FIRSTROW > 1 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       &quot;;&quot;    1     &quot;id&quot;             &quot;&quot;
2       SQLCHAR              0       10      &quot;;&quot;    2     &quot;title&quot;          SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR              0       4       &quot;\r\n&quot;     3     &quot;val&quot;            &quot;&quot;


Data file:
1,first,0
2,second,2


Bulk insert:
DECLARE @cmd VARCHAR(4000);
SET @cmd = 'sqlcmd -b -S &lt;servername&gt; -Q &quot;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 );&quot;';
EXEC xp_cmdshell @cmd;
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 = '&quot;',
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',  --CSV field delimiter
    ROWTERMINATOR = '\n',   --Use to shift the control to next row
    TABLOCK
)

As the ROWTERMINATOR  goes &lt;ou should try it or switch to \r\n