FileObject within SQL to step through a text file? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

FileObject within SQL to step through a text file?

Author

Topic

jhilb
Starting Member

22 Posts

Posted - 2002-09-24 : 17:33:08

Is there a way to step through the lines of a text file in SQL?

Like use the FileObject within SQL to open a text file and step through the lines?

Anyone have code like that?

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-09-24 : 17:42:29

I'm assuming your are trying to parse a Text file into the database?

Usually for stuff like that, DTS is used. Do some reading on DTS. Depending on how much logic you need to perfor for each line, you may be able to use BULK INSERT to load your data.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-09-24 : 17:53:45

As already suggested BULK INSERT,bcp,DTS would normally be your first port of call for reading a text file, however here's an example using FSO.


create table #test (rowtext varchar(1000) NULL)

DECLARE @fso int 
DECLARE @hr int 
DECLARE @file int
DECLARE @eof varchar(5)
DECLARE @filename varchar(255)  
SET @filename='e:\temp\test1.txt'

EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

EXEC @hr=sp_OAMethod @fso, 'OpenTextFile',@file OUT,@filename,1
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

EXEC @hr=sp_OAGetProperty @file, 'AtEndOfStream',@eof OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @file

WHILE @eof <> 'True'
BEGIN
        insert #test
        EXEC @hr=sp_OAMethod @file, 'ReadLine',NULL
        IF @hr <> 0 EXEC sp_OAGetErrorInfo @file

        EXEC @hr=sp_OAGetProperty @file, 'AtEndOfStream',@eof OUT
        IF @hr <> 0 EXEC sp_OAGetErrorInfo @file
END

EXEC @hr=sp_OADestroy @file
IF @hr <> 0 EXEC sp_OAGetErrorInfo @file

EXEC @hr=sp_OADestroy @fso
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

select * from #test
drop table #test

HTH
Jasper Smith

Tim
Starting Member

392 Posts

Posted - 2002-09-24 : 22:52:33

Or you can create an ODBC DSN for the text file, then hok the DSN to a linked server and access it like other tables.

----
Nancy Davolio: Best looking chick at Northwind 1992-2000

jhilb
Starting Member

22 Posts

Posted - 2002-09-25 : 11:04:12

I am going to try the FileObject and then move on to DTS if I can't get that to work.

Thanks everyone.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources