Read a long ascii file from 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

Transact-SQL (2000)

Read a long ascii file from Stored Procedure

Author

Topic

realdrouin
Starting Member

4 Posts

Posted - 2004-05-27 : 19:37:43

Hi,

I have to write an SP that will read an ascii file and extract only the lines where the word "Status" is in it.

The file is generaly 30000 lines and 80 characters per line. Usually there is 2 to 3 lines that I need to extract and save.

Any idea how I can do that ?

Thanks
Real Drouin
realdrouin@yahoo.com

jbkayne
Posting Yak Master

100 Posts

Posted - 2004-05-27 : 19:44:59

Check out the BCP utility or BULK INSERT. I'd import directly to a table that autogenerates line numbers via IDENTITY(1,1). (requires a format file) ....

Afterwards you can run a query that pulls the line number off the table. I'd suggest full text search, but using LIKE should also do the trick if performance isn't crucial.

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-05-27 : 22:32:12

How about an ugly solution:

create table #t (dta varchar(255))
insert (#t)
exec master..xp_cmdshell 'type c:\filepath\filename.txt | FIND "Status"'

Or:

SELECT *
INTO my_new_table
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source="c:\";Extended roperties="Text;HDR=No;FMT=FixedLength"')...stats#txt
WHERE Col1 LIKE '%Status%'

I don't think the column name will actually be Col1. I forget what it is by default. Run it without the WHERE
once to find out. (I don't have an instance to test it on right now.)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources