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)