Posts

Showing posts from April 8, 2012

Reading lines from a file using SQL Server

Create the ReadFileAsTable stored procedure in your test database, and try it out with something like:   Select line from   Dbo . uftReadfileAsTable ( 'MyPath' , 'MyFileName' ) where line not like '#%' --where line doesnt begin with a hash 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. This is a method I use for reading web logs and gathering usage statistics. It is also useful where the data feed has to be validated before one can parse it into the final SQL data format. ----------- [dbo].[ReadfileAsTable] ------------- Create FUNCTION [dbo].[ReadfileAsTable] ( @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),