September 13, 2005 at 1:03 pm
I want to read a text file line by line and search for specific information. After extracting that information I want to insert it in to the table.
Is there is way I can open and read a text file from a stored procedure?
Thanks in advance.
Kavita
September 13, 2005 at 1:06 pm
Swith this around. Insert into a table, then search on it using like or full text search.
September 13, 2005 at 1:12 pm
My text file is too large. I can not import every thing in the table before I search it.
I only need to exctract one word for example 'WORK ORDER NUMBER' preceding with the word 'EXCEPTION' and then write that number in the table.
Kavita
September 13, 2005 at 1:14 pm
Is this a CSV file or something like that???
You could then use dts to import the data to a table.
September 13, 2005 at 1:43 pm
Tough problems may require creative solutions. T-Sql may require you to be a little too creative.
T-Sql is fairly inadequate when it comes to dealing with objects outside of the database.
So if you want to open a file, and trudge thru it w/ t-sql you will have issues.
There are a ton of things U can do and using t-sql should be the last thing. You can create some kind of hybrid- Use t-sql to call some api/Console app, that can find the file, find the value, and pass the value back to your stored procedure using xp_Cmdshell
But of course dts would be the simplest way to do this.
And you can call the dts from a procedure, but again you have to go thru another layer. xp_cmdshell,
http://www.windowsitpro.com/SQLServer/Article/ArticleID/23012/23012.html
September 13, 2005 at 3:14 pm
Assuming that the text file has row delimiters, you can BCP it into a table from a T-SQL procedure, invoking it through 'master..xp_cmdshell'. Something like:
IF OBJECT_ID ('tempdb..tempfile') IS NOT NULL
--THEN
DROP TABLE tempdb..tempfile
--END IF
CREATE TABLE tempdb..tempfile
(detail VARCHAR (8000) NULL)
DECLARE @cmd VARCHAR (8000)
, @term CHAR (1)
SELECT @term = CHAR (2)
SELECT @cmd = 'BCP tempdb.dbo.tempfile IN {filename} -T -c -t' + @term
+ ' -S{server} -b1000'
EXEC master..xp_cmdshell @cmd
September 14, 2005 at 10:24 am
Hi
I do something similar, and I use the file system object to achieve it. Code outline below. May be of some use.
Darren
--FILE SYSTEM OBJECT VARIABLES
DECLARE @FSO int
DECLARE @OLEResult int -- If 0 all is okay otherwise an error has occurred
DECLARE @RecordsExist int
DECLARE @FileID int -- Is a return value that is unique to the specific file opened.
DECLARE @Error_Source varchar(255)
DECLARE @Error_Desc varchar(255)
DECLARE @ForReading int
DECLARE @ForWriting int
DECLARE @ForAppending int
DECLARE @FileLine varchar(2000)
DECLARE @LoopCounter int
--Setting File System Object Options
SET @ForReading = 1
SET @RecordsExist = 0
EXECUTE @OLEResult = sp_OAMethod @FSO, 'OpenTextFile', @FileID OUT, 'C:\test', @ForReading
WHILE @RecordsExist >= 0
BEGIN
--Read Each Line in Text File
EXECUTE @RecordsExist = sp_OAMethod @FileID, 'ReadLine', @FileLine OUTPUT
IF @RecordsExist >= 0
BEGIN
--DO SOMETHING
END --End of @RecordsExist IF
END -- END OF WHILE
EXECUTE @OLEResult = sp_OADestroy @FileID
EXECUTE @OLEResult = sp_OADestroy @FSO
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply