Importing text files with Bulk Insert on MS SQL Server 2000

Importing text files
Author Nigel Rivett

This process will import text files that arrive in a directory into a table.
It will process every file in the directory with the correct filemask and move the file to an archive directory on completion.
It can be used in conjunction with an ftpget SP to import files from a ftp server (see ftp).


/*
Create the directories c:/Transfer/Archive/
Create these text files in c:/transfer/
bcp1.txt
aaammm0120030101
bbbnnn0220030102
cccooo0320030103

bcp2.txt
abcxyz5320030104
defhhh1020030105
cdezzz1120030106
fsajku9920030107

Create the table
create table BCPData
	(
	fld1 varchar(20) ,
	fld2 varchar(20) ,	
	fld3 int ,
	fld4 datetime
	)
Now run the import
exec ImportFiles 'c:/Transfer/' , 'c:/Transfer/Archive/' , 'bcp*.txt', 'MergeBCPData'

You can now move the files back from  the archive directory to the transfer directory and import again.
If this SP call is scheduled then it will import and archive any files that arrive in the transfer directory with the corect file mask.

Enhancements
	The import should be logged to a table in ImportFiles
	The filename should have the datetime appended to it when archived if you wish to be able to import files with the same name
	The MergeData SP should log the number of records imported

*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ImportFiles]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ImportFiles]
GO

create procedure ImportFiles
@FilePath 		varchar(1000) = 'c:/Transfer/' ,
@ArchivePath		varchar(1000) = 'c:/Transfer/Archive/' ,
@FileNameMask		varchar(1000) = 'bcp*.txt' ,
@MergeProc		varchar(128) = 'MergeBCPData'

AS

	set nocount on
	
declare @ImportDate datetime
	select @ImportDate = getdate()
	
declare @FileName 		varchar(1000) ,
	@File	 		varchar(1000)

declare @cmd varchar(2000)
	
	create table ##Import (s varchar(8000))
	create table #Dir (s varchar(8000))
	
	/*****************************************************************/
	-- Import file
	/*****************************************************************/
	select	@cmd = 'dir /B ' + @FilePath + @FileNameMask
	delete #Dir
	insert #Dir exec master..xp_cmdshell @cmd
	
	delete #Dir where s is null or s like '%not found%'
	while exists (select * from #Dir)
	begin
		select 	@FileName = min(s) from #Dir
		select	@File = @FilePath + @FileName
		
		select 	@cmd = 		'bulk insert'
		select 	@cmd = @cmd + 	' ##Import' 
		select 	@cmd = @cmd + 	' from'
		select 	@cmd = @cmd +	' ''' + replace(@File,'"','') + ''''
		select 	@cmd = @cmd +	' with (FIELDTERMINATOR=''|'''
		select 	@cmd = @cmd +	',ROWTERMINATOR = ''' + char(10) + ''')'
		
		truncate table ##Import
		
		-- import the data
		exec (@cmd)
				
		-- remove filename just imported
		delete	#Dir where s = @FileName
		
		exec @MergeProc
		
		
		-- Archive the file
		select @cmd = 'move ' + @FilePath + @FileName + ' ' + @ArchivePath + @FileName
		exec master..xp_cmdshell @cmd
	end
	
	drop table ##Import
	drop table #Dir
go

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

create procedure MergeBCPData

AS
	set nocount on
	
	-- insert data to production table
	insert	BCPData
		(
		fld1 ,
		fld2 ,
		fld3 ,
		fld4
		)
	select	
		fld1	= substring(s,1,3) ,
		fld2	= substring(s,4,3) ,
		fld3	= convert(int,substring(s,7,2)) ,
		fld4	= convert(datetime,substring(s,9,8))
	from	##Import
	
go