TSQL - Get files from Windows Directory and results in table with file paramters

Question

Post reply

TSQL - Get files from Windows Directory and results in table with file paramters

khurramsaifi

SSC Enthusiast

Points: 183
More actions
July 13, 2010 at 4:12 pm

#223774

This code get the the files from Window directory and get the results in the table with all the file paramters
declare @filepath varchar(200)
declare @tt varchar(200)
DECLARE @size VARCHAR(256)
DECLARE @fileName VARCHAR(500)
DECLARE @fileDate datetime
/*********************************************************************
****** Important parameter to set ******
**********************************************************************/
set @filepath = 'dir C:\temp\*.pdf /-C '
--EXEC master.dbo.xp_cmdshell 'dir /?'
--EXEC master.dbo.xp_cmdshell 'dir c:\temp\*.txt /-C '
DECLARE @files TABLE (filename varchar(200), fileSize varchar (20), DateCreated datetime)
declare @jobs table (filenamedt varchar(1000))
INSERT INTO @jobs EXEC master.dbo.xp_cmdshell @filepath
DECLARE db_cursor CURSOR FOR
SELECT * FROM @jobs
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @tt
WHILE @@FETCH_STATUS = 0
BEGIN
begin try
select @filedate = convert(datetime,substring(@tt,0,21))
,@size =substring(ltrim(substring(@tt, 21, len(@tt))), 0, charindex(' ', ltrim(substring(@tt, 21, len(@tt)))) )
,@fileName = substring(ltrim(substring(@tt, 21, len(@tt))), charindex(' ', ltrim(substring(@tt, 21, len(@tt)))) , len(ltrim(substring(@tt, 21, len(@tt)))) )
if(@filedate is not null and @size is not null and @filename is not null )
begin
insert into @files ( DateCreated, fileSize , filename)
values (@filedate, @size,ltrim(@filename))
end
end try
begin catch
end catch
FETCH NEXT FROM db_cursor INTO @tt
END
CLOSE db_cursor
DEALLOCATE db_cursor
select * from @files
Thanks, Khurram

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

khurramsaifi

Your code works well with a file path such as:

set @filepath = 'dir C:\dj5551\*.exe /-C '

but with a file path that contains blanks no data is returned

for example":

set @filepath = 'dir C:\Documents and Settings\ron\Recent\*.doc /-C '

With that caveat may I suggest you submit your code as a script to be published, as in that format many more people would read it and benifit from it.

If you are uncertain as to how to submit a script for publication:

In the page header where is says "Welcome khurramsaifi, click on "My Account", on the next page displayed under "Write for us"

click on the underlined link titled 'Contribution Center", then on the next page scroll down and click on "Contribute SQL Script" and make your contribution.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

khurramsaifi SSC Enthusiast Points: 183 More actions · Answer 2

I have contributed the script as you have mentioned.

Alan. T. Hall of Fame Points: 3221 More actions · Answer 3

Just a thought:

It is also extremely easy to do this with SSIS. This would give you more options and might be easier to reuse.

khurramsaifi SSC Enthusiast Points: 183 More actions · Answer 4

It can work in SSIS but all the file parameters come in one column (which can be pain). This script separates the file paramters in separate columns (string parsing).

If you know an easy way to get this done is SSIS getting the files with it parameters in separate columns, please suggest.

Thanks, Khurram

Alan. T. Hall of Fame Points: 3221 More actions · Answer 5

You can read the file name information into a record set destination putting it into the columns you need. The for each container can then iterate the record set to load the files.

Any constant or work variable can be assigned to the package.