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),
     @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

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