Check if File exists

Question

Post reply

Check if File exists

George-186664

Say Hey Kid

Points: 700
More actions
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

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

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

Jerry Hung SSChampion Points: 12968 More actions · Answer 1

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

George-186664 Say Hey Kid Points: 700 More actions · Answer 2

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.

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 3

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

George-186664 Say Hey Kid Points: 700 More actions · Answer 4

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.

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 5

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

George-186664 Say Hey Kid Points: 700 More actions · Answer 6

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

Check if File exists

Cookies on SQLServerCentral