CODEWITHSUNDEEP
sql-serversql-server-2005
Sateesh
Sateesh

Reputation: 11

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??

Thanks in Advance.

SateeshChandra.

Upvotes: 1

Views: 8692

Answers (4)

Mark Kram
Mark Kram

Reputation: 5832

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/

Upvotes: 0

Peter Schofield
Peter Schofield

Reputation: 993

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

Upvotes: 0

anon
anon

Reputation:

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.

Upvotes: 3

Ta01
Ta01

Reputation: 31610

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 : ( )

Upvotes: 1

Related Questions