Check if File Exists with Dynamic Name

  • 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



    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.

  • Use XP_DIRTREE instead?

    Check here for some ideas.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See for details of how to post T-SQL code-related questions.

  • 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

Viewing 3 posts - 1 through 3 (of 3 total)

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