How can I read a text file with T-SQL, parse it and get some data?

[passs]

Hello everybody!

Does anybody know how can I using T-SQL open a text file for future parsing, getting some infoprmation, for example as a long string, parsing will be on me, for further insering into tables?

Any ideas?
Thank you!
Alex

 

[ks2000]

Hi

Create DTS package, With ActiveXScript Task to parse the data and Data Transformation Task to load the text file.



Good Luck
Gopala Krishna Kakani

 

[passs]

Hey, thanks,
but there is another way I found a few minutes ago:
declare @command varchar(250),
@filename varchar(250)

select @filename = '\\server\foo.txt'
select @command = 'TYPE ' + @filename

create table #tResults (line_id int identity, line_text varchar(800))

insert into #tResults
exec master..xp_cmdshell @command

select * from #tResults order by line_id

drop table #tResults

read row by row!
But thank you!
Alex

 

[SQLDenis]

look up BULK INSERT in Books On Line

BULK INSERT YourDB.dbo.YourTable
FROM '\\server\foo.txt'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '|\n'
)


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[mrdenny]

There are three native methods to insert data from a text file into SQL. BCP, Bulk Insert and DTS.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com