Sample Header Ad - 728x90

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

0 votes
1 answer
1115 views
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  -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;
Asked by owl (310 rep)
Dec 4, 2020, 03:03 PM
Last activity: Jul 13, 2025, 09:05 AM