Extract data from Textfile
- I am using Sqlserver 6.5 .Problem : T have usually to work with text file . So I receive data in Text File , then I extract the data to fill my data baseThe Question : is there a solution to Open FileText with sql server (example in Stored Procedure ) and read it line per line to exxtract the data .Can you give Me some examples PleaseThanks
-
I believe your best method would be to load the text file into a staging table with bcp. When you ahve the file loaded in that table, you can then do SQL on the data. When satisfied, scrubbed and cleaned, that data can be brought into the production table.
See BOL (Books on Line) for more info on bcp.exe
/Kenneth
-
Here is A way, not the best way of course to read in a text file line by line and return it as a rowset:
CREATE PROCEDURE dbo.AgentBatchLoadStatementFile_Read
(
@sFileName VarChar(255)
)
AS
SET NOCOUNT OFF
DECLARE @objFSys INT
DECLARE @objFile INT
DECLARE @sFileNameOld VarChar(255)
DECLARE @bAtEndOfFile INT
DECLARE @LineNumber INT
DECLARE @LineText VarChar(1000)
DECLARE @FileExists BIT
DECLARE @FileContents TABLE (
LineNumber INT NOT NULL,
LineText VarChar(1000)
)
-- test if the file exists
EXEC sp_OACreate 'Scripting.FileSystemObject', @objFSys OUT
EXEC sp_OAMethod @objFSys, 'FileExists', @FileExists OUT, @sFileName
IF @FileExists = 0
BEGIN
INSERT INTO @FileContents
( LineNumber,
LineText
 
VALUES
( 2,
'Error: File ' + @sFileName + ' does not exist.'
 
END
ELSE
BEGIN
-- open file file object
EXEC sp_OAMethod @objFSys, 'OpenTextFile', @objFile OUT, @sFileName, 1
EXEC sp_OAMethod @objFile, 'AtEndOfStream', @bAtEndOfFile OUT
-- this will return the next line number to insert into the variable table
SELECT @LineNumber = ISNULL(MAX(LineNumber), 0) + 1 FROM @FileContents
-- read all lines of statement and insert contents to table as rows
WHILE @bAtEndOfFile = 0
BEGIN
-- read next line of statement from file
EXEC sp_OAMethod @objFile, 'ReadLine', @LineText OUT
-- insert line text into table
INSERT INTO @FileContents
( LineNumber,
LineText
 
VALUES
( @LineNumber,
@LineText
 
-- increment line number for next insert
SELECT @LineNumber = @LineNumber + 1
-- test if end of file has been reached
EXEC sp_OAMethod @objFile, 'AtEndOfStream', @bAtEndOfFile OUT
END
END
-- cleanup objects
EXEC sp_OADestroy @objFSys
EXEC sp_OADestroy @objFile
END
--- Return the File to Display, or Error message text
SELECT
LineNumber,
LineText
FROM
@FileContents
ORDER BY
LineNumber
GO
-Mike Gercevich
-
Use DTS!
-
Well, if you absolutely must read the file row-by-row (why do you want to do that?) then DTS will provide that for you if you also do some sort of transform from the file to your table. I suppose then that DTS would be easiest tool of choice.
A note on the sp_OA* method posted earlier:
As you say, it's not the 'best' way to load a file
There are some constructs in there ( MAX(id) ) that doesn't scale well, so if the file is large, then it will be slower and slower.. Also, the end result from all that COM juggling is the same as..
create table loadTable ( lineNumber int identity(1,1) not null, lineText varchar(1000) not null )
bcp myFile in loadTable (all rows inserted and linenumber generated automagically)
select * from loadTable (returns the resultset)
Yeah, no errorhandling in the pseudocode, but it's much shorter and also takes benefit of existing bcp.exe rather than risking running sp_OA* obects inprocess (which always is a risk jeopardizing the server)
/Kenneth
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply