Looping through the files in the folder using SQL Server 2005 stored procedure

Can you please tell how to loop through a folder having .txt files and get the latest file and add the content into the table using SQL Server 2005 stored procedure??

Tha开发者_如何学Cnks in Advance.

SateeshChandra.

I would suggest that this is a better job for SQL CLR or external tools like a C# command line app. You can do this in various ways within SQL but they're inherently insecure and potentially problematic. My approach is usually xp_cmdshell if CLR or external tools are not an option. It needs to be enabled first:

EXEC sp_configure 'show adv', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'xp_cmdshell', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'show adv', 0;
GO
RECONFIGURE WITH OVERRIDE;
GO

Then you can do something like this:

SET NOCOUNT ON;

DECLARE
    @folder NVARCHAR(2048),
    @cmd NVARCHAR(MAX);

SET @folder = N'C:\path\';

SET @cmd = N'dir ' + @folder + '*.txt';

CREATE TABLE #x(n NVARCHAR(2048));

INSERT #x EXEC [master].dbo.xp_cmdshell @cmd;

DECLARE @filename NVARCHAR(2048);

;WITH x(n) AS (SELECT n FROM #x WHERE ISDATE(LEFT(n, 20)) = 1)
    SELECT TOP 1 @filename = n FROM x
    ORDER BY CONVERT(DATETIME, LEFT(n, 20)) DESC;

SET @cmd = N'type ' + @folder + SUBSTRING(@filename,
        LEN(@filename) - CHARINDEX(' ', REVERSE(@filename)) + 2,
        2048);

CREATE TABLE #y(n NVARCHAR(MAX));

INSERT #y EXEC [master].dbo.xp_cmdshell

-- no idea what "add the content into the table" means
-- but you can work with this:
SELECT n FROM #y;

DROP TABLE #x, #y;

Note 1: The width of the date information in the #x.n column is going to vary depending on your regional settings / locale. You may need to experiment.

Note 2: The determination of the file name assumes that your file names do not have spaces. If they do, then at least one line above will need to be revisited.

There is a handy udf located in this blog post which you could probably adapt. I'm going to post the contents of the script and usage incase the site below dies:

Create FUNCTION [dbo].[uftReadfileAsTable]
(
@Path VARCHAR(255),
@Filename VARCHAR(100)
)
RETURNS 
@File TABLE
(
[LineNo] int identity(1,1), 
line varchar(8000)) 

AS
BEGIN

DECLARE  @objFileSystem int
        ,@objTextStream int,
        @objErrorObject int,
        @strErrorMessage Varchar(1000),
        @Command varchar(1000),
        @hr int,
        @String VARCHAR(8000),
        @YesOrNo INT

select @strErrorMessage='opening the File System Object'
EXECUTE @hr = sp_OACreate  'Scripting.FileSystemObject' , @objFileSystem OUT


if @HR=0 Select @objErrorObject=@objFileSystem, @strErrorMessage='Opening file "'+@path+'\'+@filename+'"',@command=@path+'\'+@filename

if @HR=0 execute @hr = sp_OAMethod   @objFileSystem  , 'OpenTextFile'
    , @objTextStream OUT, @command,1,false,0--for reading, FormatASCII

WHILE @hr=0
    BEGIN
    if @HR=0 Select @objErrorObject=@objTextStream, 
        @strErrorMessage='finding out if there is more to read in "'+@filename+'"'
    if @HR=0 execute @hr = sp_OAGetProperty @objTextStream, 'AtEndOfStream', @YesOrNo OUTPUT

    IF @YesOrNo<>0  break
    if @HR=0 Select @objErrorObject=@objTextStream, 
        @strErrorMessage='reading from the output file "'+@filename+'"'
    if @HR=0 execute @hr = sp_OAMethod  @objTextStream, 'Readline', @String OUTPUT
    INSERT INTO @file(line) SELECT @String
    END

if @HR=0 Select @objErrorObject=@objTextStream, 
    @strErrorMessage='closing the output file "'+@filename+'"'
if @HR=0 execute @hr = sp_OAMethod  @objTextStream, 'Close'


if @hr<>0
    begin
    Declare 
        @Source varchar(255),
        @Description Varchar(255),
        @Helpfile Varchar(255),
        @HelpID int

    EXECUTE sp_OAGetErrorInfo  @objErrorObject, 
        @source output,@Description output,@Helpfile output,@HelpID output
    Select @strErrorMessage='Error whilst '
            +coalesce(@strErrorMessage,'doing something')
            +', '+coalesce(@Description,'')
    insert into @File(line) select @strErrorMessage
    end
EXECUTE  sp_OADestroy @objTextStream
    -- Fill the table variable with the rows for your result set

    RETURN 
END

Usage:

Select line from
 Dbo.uftReadfileAsTable('MyPath','MyFileName')
where line not like '#%'

Just fill in an existing file name and path to the file you wish to read, instead of 'MyPath' and 'MyFileName', and away you go.

(Note: I've included the original source because I've been downvoted because a link to a solution for another question died : ( )

My first thought was that this is an ideal candidate for SSIS - except that SSIS has a fairly steep learning curve if you are new to it

TSQL Solution - a couple of excellent articles to get you started:

http://www.mssqltips.com/tip.asp?tip=1263

http://www.simple-talk.com/sql/t-sql-programming/reading-and-writing-files-in-sql-server-using-t-sql/

SSIS Solution: - one article to get you started:

http://www.sqlis.com/post/Looping-over-files-with-the-Foreach-Loop.aspx

I know that this is a very old post but I found that the solution in the following link worked perfectly for me: http://www.databaseskill.com/2219220/