CREATE procedure ImportFiles
@FilePath 		varchar(1000) = 'c:\Transfer\' ,
@ArchivePath		varchar(1000) = 'c:\Transfer\Archive\' ,
@FileNameMask		varchar(1000) = '*.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(40), a varchar(40), b varchar(40), c varchar(40), d varchar(40), e varchar(40), f varchar(40), g varchar(40), h varchar(40), i varchar(40), j varchar(40), 
			k varchar(40), l varchar(40), m varchar(40), n varchar(40), o varchar(40))
	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= ''\r'''
		select 	@cmd = @cmd +	',ROWTERMINATOR = ''['')'
                        
		truncate table ##Import
		
		-- import the data
		exec (@cmd)
                        	print @cmd	
                          

		-- remove filename just imported
		delete	#Dir where s = @FileName
		
		exec @MergeProc @filename
		
		
		
		-- Archive the file
		select @cmd = 'move ' + @FilePath + @FileName + ' ' + @ArchivePath + @FileName
		exec master..xp_cmdshell @cmd
	end
	drop table ##Import
	drop table #Dir
GO

CREATE PROCEDURE FileChecker
@itemsalestatus varchar (20) output
as

DECLARE @exists as varchar(300)
DECLARE @result int
DECLARE @rowc as int
DECLARE @path as varchar(300)
DECLARE @sql as varchar(300)
DECLARE @icount as int

select @exists = '"' + 'DIR /B \\server\filename'+ '.'+'def'+'"'
EXEC @result = master..xp_cmdshell @exists , no_output
	-- if it does then bulk insert it into the tmpisaleimport table
	
	IF (@result = 0)
       		begin
			select @path =''''+ '\\server\filename'+'.'+'def'+''''
			Set @sql='BULK INSERT [tmpisaleimport]FROM '+@path		
			Exec(@sql)
	 		Set @rowc = @@rowcount
			IF @rowc = 0         
	   		Begin
            				-- this occurs when there is a 0kb file although it still physically exists
				INSERT INTO file_noexist_table (StoreNumber, [Description])
	    			VALUES ('103','File does exist but has no data')
           		                End
	 		ELSE		
	     		Begin
	       			--increment the counter variable if it does exist	
               				SELECT @icount = @icount + 1
				INSERT dbo.itemSalesImportStoreSuccess (StoreNumber)
				VALUES ('103')	     			
			End

		end
              ELSE
       -- if it doesn't plug it into the the error table which shows all failed store imports
       		begin
			INSERT INTO itemsales_stores_input_error(StoreNumber, [Description])
			VALUES ('103','File does not exist')
		end

IF @icount = 1 
begin
--the file exists
set @itemsalestatus = 'CompleteIsaleNormal'
end