Bulk Insert from Flat File Using Transact-SQL

Bulk Insert from Flat File Using Transact-SQL

Author M M Harinath

 

In a typical IT environment, it is often necessary to import flat files to SQL Server tables.
Sometimes it is necessary to format flat file according to our table structure using delimiters.
In this Code Snippet, I am going to discuss how to import Flat files to SQL Server.

Step 1

Create a Destination table [Customer_Sample] using the following schema.

 

Use the following script to create this table:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Customers_Sample]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Customers_Sample]
GO

CREATE TABLE [dbo].[Customers_Sample] (
[CustomerID] [nchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CompanyName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ContactName] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ContactTitle] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO 

The following picture is the screen shot of Source Data file (Flat File)

Step 2

Create Schema File using Notepad.

Schema File Details:

8.0
4
1	SQLCHAR	0	5	""		1	CustomerID	SQL_Latin1_General_Cp437_BIN
2	SQLCHAR	0	40	""		2	CompanyName	SQL_Latin1_General_Cp437_BIN
3	SQLCHAR	0	30	""		3	ContactName	SQL_Latin1_General_Cp437_BIN
4	SQLCHAR	0	30	"/n"	4	ContactTitle	SQL_Latin1_General_Cp437_BIN

Save this file with “.FMT” Extension (for example: bcp.fmt)

The structure of the Schema File is explained in the following image.

 

Step 3

Create the following Store Procedure [Text_File_Bulk_Import] to import Text file data to Destination Table [Customer_Sample]

CREATE PROC Text_File_Bulk_Import
@SourceFilePath varchar(50),
@FormatFilePath varchar(50),
@RowNumber int
as
SET ANSI_WARNINGS OFF
DECLARE @str_command nvarchar(150)
SET @str_command = 'BULK INSERT [Customer_Sample] FROM ''' + @SourceFilePath 
+ ''' WITH (formatfile = ''' + @FormatFilePath + ''', firstrow =' + cast(@RowNumber as nvarchar) + ')'
EXEC SP_EXECUTESQL @str_command

Execute the Store Procedure [Text_File_Bulk_Import]

Text_File_Bulk_Import 'c:/Flat_file.txt','c:/bcp.fmt', 3

• 'c:/flat_file.txt' – Source Text File Name
• 'c:/ bcp.fmt' - Schema File Name
• 3 - First Row Number (Actual Data starts from 4th row only)

 

 

Comments

Read Comments | Post Comment

• Re: [5357] Bulk Insert from Flat File Using Transact-SQL

  Posted by daviddesantiago on 29 Jun 2006

   

  Hello,

  
  

  I am getting the same error message:
  Server: Msg 4839, Level 16, State 1, Line 1
  Cannot perform bulk insert. Invalid collation name for source column 4 in format file 'c:/bcp....

• Re: [5357] Bulk Insert from Flat File Using Transact-SQL

  Posted by Ayub on 27 Mar 2006

  I got this error . 'Cannot perform bulk insert. Invalid collation name for source column 4 in format file 'c:/bcp.fmt'.' I copied as it was on the post. Any ideas?
  
  

• Good One

  Posted by inferano on 10 Feb 2006

  Good piece of knowledge imparting code.
  Got to learn something new.
  

• Good work!!!

  Posted by itssara on 07 Feb 2006

  Hari,
  
  Thanx for posting this valuable code snippet. It was really useful to me. I learnt a new topic today. Once again thanx.
  
  Regards
  Saravanan