importing any text file found in a directory 3

[fgeorge]

i have several text files in a directory that all begin with the words batch_registration..

the ending part of the file names is what differs as the date and time is appended to it..


how can i get sql server 2000 to search through this directory and import the files into a temporary table in a stored procedure?

 

[sunila7]

Hi,

Try this code..

I created a copy of authors table in the pubs database called NewAuhors and then created 2 Tab delimited text files with data from the NewAuhors table using DTS Import/Export Wizard,and saved them in C:\Import directory, and then deleted all data in NewAuhors Table, and executed the code below.. All data from 2 text files r imported back into the table after execution of the code.

DELETE FROM NEWAuhors
drop table #t
create table #t(o varchar(8000))

insert into #T
EXEC master..xp_cmdshell 'dir c:\Import\*.txt' -- GET ALL Txt file from the directory

delete from #t where (o not like '%.txt' or o is NULL) -- Remove all Unwanted rows

Declare @filename varchar(100)
Declare @SQL varchar(5000)

Declare fileCursor Cursor for select ltrim(reverse(Substring(reverse(o),1,Charindex(' ',reverse(o))))) from #T
OPEN fileCursor -- Open Cursor to get all the file names from the directory

FETCH NEXT FROM fileCursor Into @filename

While @@fetch_status = 0
Begin
SET @SQL = 'BULK INSERT pubs.dbo.[NewAuhors]
FROM ''c:\import\' + @filename + ''' WITH ( FIELDTERMINATOR = ''\t'', ROWTERMINATOR = ''\n'')'
--print @SQL
exec(@SQL) -- Insert into the Table
FETCH NEXT FROM fileCursor Into @filename
End
CLose fileCursor
Deallocate fileCursor
select * from NewAuhors -- Check to see whether values r entered or not

This is the Sample Text file I used to import data, file name was: BatchRegistration01012003.txt

172-32-1176 White Johnson 408 496-7223 10932 Bigge Rd. Menlo Park CA 94025
213-46-8915 Green Marjorie 415 986-7020 309 63rd St. #411 Oakland CA 94618

Note: One assumption I am making in this is that file names dont have space in them for eg: "batch registration 01022003.txt"

I hope this is what u r looking for...

Sunil

 

[fgeorge]

that was brilliant sunila7!!
thanks..

 

[eja2000]

can this be achieved using DTS?