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

    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.

  • 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 https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ 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

  • 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.

  • This was removed by the editor as SPAM

  • 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.

    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 https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

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

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