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

  • 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

  • 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]

  • I have contributed the script as you have mentioned.

  • 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.

  • 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

  • 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.

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

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