Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4032
  • Last Modified:

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!


0
TheUndecider
Asked:
TheUndecider
  • 4
1 Solution
 
QPRCommented:
Are you sure that
SET @Var= 'C:\MyFolder\*.txt'
does not work?
0
 
QPRCommented:
sorry I should have tested before posting - u r right doesn't work :(
0
 
QPRCommented:
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
0
 
William ElliottSr Tech GuruCommented:
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

0
 
QPRCommented:
An improvement on my previous post which will output what you originally asked for...

declare @i as integer
exec @i = master..xp_cmdshell 'dir c:\*.txt /b'
if @i <> null
begin
print 'File does exist'
end
else
begin
print 'file does not exist'
end
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now