Bulk Insert from Flat File Using Transact-SQL

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)

You might also like...

Comments

M M Harinath

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“I have always wished for my computer to be as easy to use as my telephone; my wish has come true because I can no longer figure out how to use my telephone” - Bjarne Stroustrup