asked on

SQL Stored Procedure - Find if a .txt file exists using wildcard

Hello,

Does anyone here knows how to check whether if any files with a particular extension exists in a folder?

I'm building a stored procedure (SQL Server 2000) where I'm supposed to find any files with the .txt extension in one of my hard drive folders. Here's the code I'm using:

DECLARE @Var varchar(200)
DECLARE @WildCard varchar (50)
SET @WildCard = 'MyLostFile'

SET @Var= 'C:\MyFolder\' + @wildcard + '.txt'

EXEC master..xp_fileexist @var, @return OUTPUT

IF @return =1
BEGIN
PRINT'File does exist '
END
ELSE
BEGIN
PRINT 'File does not exists '
END
GO

--------------------------

This works like a charm if I provide the actual name of the text file in the wildcard variable. However, I am not supposed to know it. I need the wildcard variable to be a truly wild card like '%' and then I'm supposed to look in my folder for any files with the .txt extension.

If anyone knows how to accomplish this, please share it.

Thanks!

QPR

Are you sure that
SET @Var= 'C:\MyFolder\*.txt'
does not work?

QPR

sorry I should have tested before posting - u r right doesn't work :(

QPR

try this then check to see the value of @i

declare @i as integer
exec @i = master..xp_cmdshell 'dir c:\myfolder\*.txt /b'
print @i

William Elliott

maybe?

Declare @Filename varchar(1000)
Declare @cmd varchar(1000)
Create table #dir (Filename varchar(1000))
Insert #dir
Exec master..xp_cmdshell 'dir /B C:\MyFolder\*.txt'
delete #dir where Filename is null or Filename like '%not found%'
Select @Filename = ''
While @Filename < (select max(Filename) from #dir)
Begin
   Select @Filename = min(Filename) from #dir where Filename > @Filename
   /*do stuff here*/
end
drop table #dir

Open in new window

ASKER CERTIFIED SOLUTION

QPR

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial