Bulk Insert how to skip column using format file in SQL Server 2017

Question

I have a scenario were in a table has 5 columns, but data (CSV) file has only 2 columns of data. Because the 1st column is an Identity column, 4th & 5th have default constraints. So these columns don't need value from CSV. Please note CSV will always have all values (rows and columns in double quotes)

I tried to skip the 1st column by referring to this link: https://docs.microsoft.com/en-us/sql/relational-databases/import-export/use-a-format-file-to-skip-a-table-column-sql-server?view=sql-server-2017 but seems I am missing something, because on executing bulk insert command I get an

Cannot bulk load CSV file. Invalid terminator is specified for source column number 2 in the format file "C:\MyData\Demo1_Format.fmt". All source column terminators must be the same except the last one when CSV format is specified. Only row terminator should be different

My code:

CREATE table dbo.test1
(
    [UniqueID] [BIGINT] IDENTITY(1,1),
    [Id] CHAR(1) NOT NULL,
    [Name] [VARCHAR](50) NOT NULL,
    [IsDelete] [TINYINT] NULL DEFAULT 0,
    [Rootpid] VARCHAR(25) NULL 
         CONSTRAINT defVal_RootPid_TEST1 DEFAULT '20190110035929_x9zh5'
);

Demo1_Format.fmt

14.0
3
1 SQLCHAR 0 0 "\"" 0 "" ""
2 SQLCHAR 0 9999 "\",\"" 2 Id ""
3 SQLCHAR 0 9999 "\"\r\n" 3 Name ""

Demo1.csv

"Id","Name"
"1","James"
"2","Scott"

T-SQL:

BULK INSERT dbo.test1 from 'C:\MyData\Demo1.csv'
WITH 
    (FORMAT = 'CSV', 
     FIRSTROW = 2, 
     FORMATFILE = 'C:\MyData\Demo1_Format.fmt')

Update when I remove Format = 'CSV' parameter and keep format file as it is then it works. But I need the format parameter because it has several benefits like handling double quotes, line break, special characters issues within data. So can't I use both, format file to skip columns and format='CSV' parameters for handling data issues?

You mean in format file right? It still gives the same error. Even if value is 0 / 1 — Vikas J
Perhaps give that row the same column terminator? Even if ignored. — TT.
This is what I Kept now 1 SQLCHAR 0 0 "\",\"" 0 "" " " now error is Cannot obtain the required interface ("IID_IColumnsInfo") from OLE DB provider "BULK" for linked server "(null)". — Vikas J
Try use the hexadecimal ASCII value for double quote? So "0x22,0x22" or "0x220x2C0x22". — TT.

level3looper level3looper · Accepted Answer · 2019-01-20T17:06:45

Can you try this format file: I think the new Format=CSV option does some thinking that the Format File no longer needs to do...

14.0
2
1       SQLCHAR             0       1       ","      2     Id           SQL_Latin1_General_CP1_CI_AS
2       SQLCHAR             0       50      "\r\n"   3     Name         SQL_Latin1_General_CP1_CI_AS

Bulk Insert how to skip column using format file in SQL Server 2017

1 Answers