Check if File Exists with Dynamic Name

Question

Post reply

Check if File Exists with Dynamic Name

AMCiti

SSC-Addicted

Points: 496
More actions
February 23, 2021 at 3:24 pm
Go to Answer
#3849070

Dear Group:
I need to check if a file exists on our server, and if it does, then execute code.
I know I can use the following code to do this:
DECLARE @fileExists int
EXEC master..xp_fileexist '\\server\feed.txt', @fileexists OUT
IF @fileExists = 1 -- The file exists
PRINT 'File Exists' -- Do what you want here
ELSE
PRINT 'File DOES NOT EXIST'
My issue though is that the filename (file.txt) is not standard like this, and changes everyday with the date appended to the end. For instance, today's file looks like this: feed_23022021
I found that I cannot use a wildcard search with the xp_fileexist command such as: EXEC master..xp_fileexist '\\server\feed_*.txt', @fileexists OUT
We will only ever have one file in this directory at a time, so how would one go about checking if the file exists and if it does, then process it?
Any help is appreciated.
The Dixie Flatline

SSC Guru

Points: 53354
More actions
February 23, 2021 at 4:45 pm
Answer
#3849123
If the file name always starts with the string "Feed_" you can build the file name in a variable and pass it instead of a constant.
```
DECLARE @fileExists int
DECLARE @FileAndPath varchar(50) = '\\server\feed_~.txt'

SET @FileAndPath = replace(@FileAndPath,'~',replace(convert(varchar(10),getdate(),103),'/',''))

print @FileAndPath

EXEC master..xp_fileexist @FileAndPath, @fileexists OUT
```
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 3, 2021 at 2:52 pm

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

Use XP_DIRTREE instead?

Check here for some ideas.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

MaryWard Grasshopper Points: 15 More actions · Answer 2

Thanks for the update and quick reply. I'll be sure to keep an eye on this thread. Looking for the same issue. Bumped into your thread. Thanks for creating it. Looking forward for solution.

Pasco Connect Login

This reply was modified 3 years, 8 months ago by MaryWard.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

MaryWard wrote:

Thanks for the update and quick reply. I'll be sure to keep an eye on this thread. Looking for the same issue. Bumped into your thread. Thanks for creating it. Looking forward for solution.

You've seen two possible solutions posted. Please explain what is wrong with them.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin