BULK INSERT - Format File (skip column)

« 1|2|3 »

In my previous post I've shown how to bulk insert a csv file through T-SQL.

In that example I've used the same structure to both csv file and destination table. In this post I'll show you how to bulk insert a file wich struture differs from destination table.

For this example, I'll take the same destination table:

CREATE TABLE [dbo].[Users]
(
	[UserID] int NULL,
	[FirstName] [varchar](10) NULL,
	[LastName] [varchar](10) NULL,
	[Country] [varchar](20) NULL
) ON [PRIMARY]

From the original file, I've removed column UserID as shown below:

When running original script I get the error below:

TRUNCATE TABLE Users;
GO
BULK INSERT Users
   FROM 'C:\UsersFile.csv'
   WITH (
	  FIRSTROW = 2,
      FIELDTERMINATOR = ','
   )
   ;
SELECT * FROM Users

Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 1 (UserID).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 3, column 1 (UserID).
Msg 4832, Level 16, State 1, Line 1
Bulk load: An unexpected end of file was encountered in the data file.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

This is because one of the following topic is true and a format file is needed:

• The same data file is used as a source for multiple tables that have different schemas;

• The data file has a different number of fields that the target table has columns;

• The column order is different for the data file and table;

• The terminating characters or prefix lengths differ among the columns of the data file;

To achieve my goal, I need to create a format file. Below, a sample of default format file is shown. There are 4 diferent format files, as mentioned in BOL Creating a Format File.

So, my format file looks like this:

And I had to modify my script to:

TRUNCATE TABLE Users;
GO
BULK INSERT Users
   FROM 'C:\UsersFile.csv'
   WITH (
	  FIRSTROW = 2,
      FIELDTERMINATOR = ',',
      FORMATFILE = 'C:\TableUsers_SkipColumn.fmt'
   );
SELECT * FROM Users