Treeless Software SQL example returns a table with each line from the text file written to a row in the table

Returns a table with each line from the text file written to a row in the table.

USE [DB_Name]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[uft_ReadFileAsTable] (
    @Path VARCHAR(255),                      -- Folder containing file
    @Filename VARCHAR(100),                  -- FileName to read
    @MaxLines INT = 0,                       -- Default Maximum number of lines to read
    @FindLine VARCHAR(100) = NULL            -- Find First Occurance of line beginning with this
)
RETURNS @File TABLE (
    [LineNum] INT IDENTITY (1,1), 
    Line VARCHAR(8000)
)
AS
BEGIN
    DECLARE
        @objFileSystem INT,
        @objTextStream INT,
        @objErrorObject INT,
        @strErrorMessage VARCHAR(1000),
        @Cmd VARCHAR(1000),
        @hr INT,
        @String VARCHAR(8000),
        @YesOrNo INT,
        @CountThem int

Set @CountThem = 1
    If @MaxLines = 0 Set @CountThem = 0

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

if @hr = 0
        Begin
            Select @objErrorObject = @objFileSystem
            Select @strErrorMessage = 'Opening file "' + @path + '\' + @filename + '"'
            Select @Cmd = @path + '\' + @filename
            EXECUTE @hr = sp_OAMethod @objFileSystem, 'OpenTextFile', @objTextStream OUTPUT, 
                    @Cmd, 1, false, 0    --for reading, FormatASCII
        End

WHILE @hr = 0
        Begin

If @CountThem = 1
                Begin
                    Select @MaxLines = @MaxLines - 1
                    If @MaxLines < 0 Break
                End

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

IF @YesOrNo <> 0
                break
            if @hr = 0
                Begin
                    Select @objErrorObject = @objTextStream
                    Select @strErrorMessage = 'reading from the output file "' + @filename + '"'
                    EXECUTE @hr = sp_OAMethod  @objTextStream, 'Readline', @String OUTPUT
                    If (@FindLine Is NULL) 
                    Or (@FindLine Is Not NULL And @FindLine = SUBSTRING(@String, 1, LEN(@FindLine)))
                        Begin
                            Insert Into @file(line) Select @String
                            if @FindLine Is Not NULL
                                break
                        End
                End
        End

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

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

Treeless Software

PC and Web Software Development.

Latest News

New VB.Net and SQL Examples

New Website Launched

July 7, 2011

Useful Links

Search