Bulk Insert how to skip column using format file

• vikasjagadale8

  Say Hey Kid

  Points: 704

  More actions

  January 20, 2019 at 4:17 am

  #382600

  I have a scenario were in a table has 5 columns, but data (CSV) file has 2 columns of data. Because the 1st column is an Identity column, 4th & 5th are having default constraint. 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 error: 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.

   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?

  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'
);

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

  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"

• Jeff Moden

  SSC Guru

  Points: 1004619

  More actions

  January 21, 2019 at 11:02 pm

  #2019586

  While you may consider it to be an extra step (and it is), I never import directly into a final table anyway.  I always import into a staging table to do validations and then I'll insert into the final table.

  Now, if you absolutely must insert into the final table directly, just create a view on the table with the columns you want and import into that instead.

  As a bit of a sidebar, it's a real shame that they didn't take this improvement a bit farther.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)