uft_ReadFileAsTable Table-Valued Function

by Bill Dowling 28. July 2010 07:17
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

Tags:

SQL Coding Examples

uft_ParseString Table-Valued Function

by Bill Dowling 28. July 2010 07:13
Returns a table with parsed elements (words) in a passed string. USE [DB_Name] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[uft_ParseString] ( @linetoparse VARCHAR(2000), @delimeter CHAR(1) ) RETURNS @elements TABLE ( ElementID INT IDENTITY (1,1), Element VARCHAR(2000) ) AS Begin Declare @LineIn VARCHAR(2000), @Element VARCHAR(2000), @StartPos INT, @Length INT Set @LineIn = @linetoparse While LEN(@LineIn) > 0 Begin Set @StartPos = CHARINDEX(@delimeter, @LineIn) If @StartPos < 0 Set @StartPos = 0 Set @Length = LEN(@LineIn) - @StartPos - 1 If @Length < 0 Set @Length = 0 If @StartPos > 0 Begin Set @Element = SUBSTRING(@LineIn, 1, @StartPos - 1) Set @LineIn = SUBSTRING(@LineIn, @StartPos + 1, LEN(@LineIn) - @StartPos) End Else Begin Set @Element = @LineIn Set @LineIn = '' End Insert Into @elements(Element) Select @Element End Return End

Tags:

SQL Coding Examples

About the author

Bill Dowling has been a VB and .Net programmer for as long as he can remember.

Month List