Post reply

Check if File exists

  • March 31, 2009 at 9:29 am

    #136208

    Good morning-

    I am trying to check to see if data exists in a text file using SQL Server. If data exists, I wish to import the file, if no data then I will import a different file. Seems like a pretty basic question, just probably slipping the mind. Any help you can provide is much appreciated!

    Thanks.

    George

  • March 31, 2009 at 9:32 am

    #969214

    Google returned these

    http://www.sqldba.org/articles/36-check-if-file-exists-in-sql-server.aspx

    I would suggest the last one xp_fileexists

    -- last check file exists using xp_fileexists

    DECLARE @Path varchar(128) ,

    @FileName varchar(128)

    SET @Path = 'C:\'

    SET @FileName = 'FILE_NAME.EXT'

    DECLARE @i int

    DECLARE @File varchar(1000)

    SET @File = @Path + @FileName

    EXEC master..xp_fileexist @File, @i out

    IF @i = 1

    PRINT 'file exists'

    ELSE

    PRINT 'file does not exists'

    SQLServerNewbieMCITP: Database Administrator SQL Server 2005

  • March 31, 2009 at 9:38 am

    #969218

    That checks to see if the file itself exists, I need to check to see if there is actual data in the file.

    Thanks for your quick response.

  • March 31, 2009 at 9:42 am

    #969224

    Hi George

    So what is the specific problem?

    * Do you want to know how to check the size of a file?

    * Do you want to know how to get the content of a file?

    * Do you want to know how to parse (e.g. xml a file)?

    Greets

    Flo

  • March 31, 2009 at 9:52 am

    #969234

    I want to bulk insert file 1 if file 2 does not contain data. I want to bulk insert file 2 if file 1 contains no data. The text files themselves exist daily, but do not always contain actual data daily.

  • March 31, 2009 at 10:04 am

    #969246

    You can either use xp_cmdshell to read the information into a table:

    execute xp_cmdshell 'dir C:\Temp\*.txt'

    ... after parsing the information from input you can use OPENROWSET to read the data.

    Or use COM to create a FSO (don't know really how to handle Active-X in SQL, it's not clean in my opinion)

    Or (my suggestion):

    You should consider to use either SSIS to handle the files or any client programming language.

    Greets

    Flo

  • March 31, 2009 at 12:04 pm

    #969333

    Thanks, I'll do my best to figure this out from here.

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

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