Leer archivo como tabla - uftReadfileAsTable

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



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