Help with sp_OAMethod: not returning data when reading file.

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

Import/Export (DTS) and Replication (2000)

Help with sp_OAMethod: not returning data when reading file.

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-08-12 : 07:52:53

writes "Hello,

I have a plain text file called Orders.txt with the following three lines:
Line1, abc
Line2, def
Line3, hij

I'm trying to read that text and put it in a table. I'm using the sp_oamethod call as stated below. The code runs and reports that it was successful, but when I query the table it is empty and even the 'print' command doesn't print anything. My goal is to parse each line, separating each csv value and insert into a field on a table. The code below is just for reading the file and seeing if anything comes out but that's not working. Seems simple enough, but I can't see what I might be missing. Any help is appreciated.

Thanks in advance!
--PhB

declare @objFSys int
declare @objFile int
declare @blnEndOfFile int
declare @strLine varchar(40)

CREATE TABLE #mytesttable (sometext text)
exec sp_OACreate 'Scripting.FileSystemObject', @objFSys out

exec sp_OAMethod @objFSys, 'OpenTextFile', @objFile out, 'C:\orders.txt', 1

exec sp_OAMethod @objFile, 'AtEndOfStream', @blnEndOfFile out

while @blnEndOfFile=0 begin
exec sp_OAMethod @objFile, 'ReadLine', @strLine out

select @strLine
INSERT INTO #mytesttable (sometext) VALUES(@strLine)

print @strLine

exec sp_OAMethod @objFile, 'AtEndOfStream', @blnEndOfFile out
end

exec sp_OADestroy @objFile
exec sp_OADestroy @objFSys

select * from #mytesttable"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-08-12 : 07:53:19

Why not just use bcp or BULK INSERT to import the data? It's a lot easier.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-08-12 : 09:12:37

If the print is not doing anything, maybe this condition is not true when the while loop starts:

while @blnEndOfFile=0

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources