Reading Files From A Directory
-
Im not sure if this is possible but...
From Sql Server (DTS or SP) can we read a text file sitting on your C drive.
The name of the file can change everyday except for part of the file name.
Ex:
Day 1 file: abc2005_04_10.txt
Day 2 file: abc2005_04_11.txt
-
maybe http://www.sqldts.com has the answer.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
You can use BULK INSERT in T-SQL to load the file into a temp table. There are plenty of good threads here on BULK INSERT.
There is no "i" in team, but idiot has two. -
I would try dynamic sql... like the following... you should probably add another command to check for file existence, so you can handle failure correctly, but this works
>Im not sure if this is possible but...
>From Sql Server (DTS or SP) can we read a text file sitting on your C >drive.
>The name of the file can change everyday except for part of the file >name.
>Ex:
>Day 1 file: abc2005_04_10.txt
>Day 2 file: abc2005_04_11.txt
declare @dateDay varchar(2),
@dateMonth varchar(2),
@dateYear varchar(4),
@dateFileName varchar(100),
@sqlCmd varchar(255)
if datepart(dd,current_timestamp)<10
set @dateDay= '0' + cast(datepart(dd,current_timestamp) as varchar(2))
else
set @dateDay= cast(datepart(dd,current_timestamp) as varchar(2))
if datepart(mm,current_timestamp)<10
set @dateMonth = '0' + cast(datepart(mm,current_timestamp) as varchar(2))
else
set @dateMonth = cast(datepart(dd,current_timestamp) as varchar(2))
set @dateYear = cast(datepart(yyyy,current_timestamp) as varchar(4))
set @dateFileName = 'C:\directoryName\abc' + @dateYear + '_' + @dateMonth + '_' + @dateDay + '.txt'
--print @dateFileName
set @sqlCmd = 'bcp Testing.dbo.abcIn in ' + @dateFileName + ' /t /c /T '
exec master..xp_cmdshell @sqlCmd
-
thats even better. thanks dude.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply