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 Name3
- First Row Number (Actual Data starts from 4th row only)
Comments
-
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