reading a file line by line in a STORED PROCEDURE

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

reading a file line by line in a STORED PROCEDURE

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-02-20 : 01:57:12

i want to read a file line by line in a STORED PROCEDURE
and makes changes on some of the text beacuse of hebrew problem
doe any 1 can help
thnaksi n advance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2006-02-20 : 07:06:39

I suppose you could use bcp utility, and then use -F (first row parametre) and -b (batch size parametre), to build the bcp dynamically, and read the file 1 row at a time. i.e. build bcp with @Iteration that increments, and use that for the -F parametre, and set the -b 1, which should read 1 line at a time, I THINK.... Alternatively, you could use -L last row, and set it = -F +1...

You can do the same with Bulk Insert, I believe...

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2006-02-21 : 04:59:32

Ok, since there've been no more takers on this, I tested it out.

This is a proof-of-concept - I don't think this is a good way to approach file handling, but I did it to see if it is possible.


--Test file Contents:
/*
TestFileLine 1
TestFileLine 2
TestFileLine 3
TestFileLine 4
TestFileLine 5
TestFileLine 6
TestFileLine 7
TestFileLine 8
*/
-- SQL Code:
set nocount on

declare	@IncrementCounter	int,
	@BulkInsertSQL		varchar(1000),
	@Error			int,
	@RowCount		int,
	@FileLocation		varchar(500)

create table ##TestLineByLineInsert
(	FileLine	varchar(8000))

select 	@FileLocation = 'c:\TestFile.txt'
select	@Error = @@Error,@RowCount = @@RowCount
select 	@IncrementCounter = 0
while	(@Error = 0) and (@RowCount <> 0)
	begin
	--you may want to delete the rows so only your line is in this temp table
	delete from ##TestLineByLineInsert
	set @IncrementCounter = @IncrementCounter +1
	select 	@BulkInsertSQL = 	'Bulk insert ##TestLineByLineInsert from '''+@FileLocation+''' with (FirstRow = '
					+str(@IncrementCounter)+', LastRow = '+str(@IncrementCounter)+')'
	exec	(@BulkInsertSQL)
	select	@Error = @@Error,@RowCount = @@RowCount
	select FileLine from ##TestLineByLineInsert
	--do you line manipulation and processing here etc.
end
--RESULTS
FileLine        
TestFileLine 1

FileLine        
TestFileLine 2

FileLine        
TestFileLine 3

FileLine        
TestFileLine 4

FileLine        
TestFileLine 5

FileLine        
TestFileLine 6

FileLine        
TestFileLine 7

FileLine        
TestFileLine 8

FileLine

... interesting to prove it could be done, but still think you should do file manipluation from a coding language like C#, VB etc.

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-02-21 : 05:48:21

i wil lthis code a shot!

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources