List Files In Directory Using SQL

  • Hi All,

    I hope this makes sense, lets say i have folder a and folder b. Within Folder A I have 4 files and in my SQL DB I have a table where the Directory Location and File name is stored. I have an SSIS package to look up the files and move items from folder a to folder b (cut and paste). But every now and then a file is created and the details are not recorded in the DB table. These are then classed as 'orphaned'. Can I get these listed in a table for automated correction/insertion... If i were to have 3 files that are 'orphaned' i want them to appear in a table/list like:

    ID LocationDirectory Filename

    1 c:/ one.txt

    2 c:/ two.txt

    I'm not sure if this can be done either in SSIS or SQL as long as I can get detailed in an SQL table.

  • I found one method that use's xp_cmdshell but I can't use as we have our db locked down, anyone have and other suggestions?

    USE master

    GO

    CREATE PROCEDURE dbo.sp_ListFiles

    @PCWrite varchar(2000),

    @DBTable varchar(100)= NULL,

    @PCIntra varchar(100)= NULL,

    @PCExtra varchar(100)= NULL,

    @DBUltra bit = 0

    AS

    SET NOCOUNT ON

    DECLARE @Return int

    DECLARE @Retain int

    DECLARE @status int

    SET @status = 0

    DECLARE @Task varchar(2000)

    DECLARE @Work varchar(2000)

    DECLARE @Wish varchar(2000)

    SET @Work = 'DIR ' + '"' + @PCWrite + '"'

    CREATE TABLE #DBAZ (Name varchar(400), Work int IDENTITY(1,1))

    INSERT #DBAZ EXECUTE @Return = master.dbo.xp_cmdshell @Work

    SET @Retain = @@ERROR

    IF @status = 0 SET @status = @Retain

    IF @status = 0 SET @status = @Return

    IF (SELECT COUNT(*) FROM #DBAZ) < 4

    BEGIN

    SELECT @Wish = Name FROM #DBAZ WHERE Work = 1

    IF @Wish IS NULL

    BEGIN

    RAISERROR ('General error [%d]',16,1,@Status)

    END

    ELSE

    BEGIN

    RAISERROR (@Wish,16,1)

    END

    END

    ELSE

    BEGIN

    DELETE #DBAZ WHERE ISDATE(SUBSTRING(Name,1,10)) = 0 OR SUBSTRING

    (Name,40,1) = '.' OR Name LIKE '%.lnk'

    IF @DBTable IS NULL

    BEGIN

    SELECT SUBSTRING(Name,40,100) AS Files

    FROM #DBAZ

    WHERE 0 = 0

    AND (@DBUltra = 0 OR Name LIKE '% %')

    AND (@DBUltra != 0 OR Name NOT LIKE '% %')

    AND (@PCIntra IS NULL OR SUBSTRING(Name,40,100) LIKE @PCIntra)

    AND (@PCExtra IS NULL OR SUBSTRING(Name,40,100) NOT LIKE @PCExtra)

    ORDER BY 1

    END

    ELSE

    BEGIN

    SET @Task = ' INSERT ' + REPLACE(@DBTable,CHAR(32),CHAR(95))

    + ' SELECT SUBSTRING(Name,40,100) AS Files'

    + ' FROM #DBAZ'

    + ' WHERE 0 = 0'

    + CASE WHEN @DBUltra = 0 THEN '' ELSE ' AND Name LIKE ' + CHAR(39) + '% %' + CHAR(39) END

    + CASE WHEN @DBUltra != 0 THEN '' ELSE ' AND Name NOT LIKE ' + CHAR(39) + '% %' + CHAR(39) END

    + CASE WHEN @PCIntra IS NULL THEN '' ELSE ' AND SUBSTRING (Name,40,100) LIKE ' + CHAR(39) + @PCIntra + CHAR(39) END

    + CASE WHEN @PCExtra IS NULL THEN '' ELSE ' AND SUBSTRING

    (Name,40,100) NOT LIKE ' + CHAR(39) + @PCExtra + CHAR(39) END

    + ' ORDER BY 1'

    IF @status = 0 EXECUTE (@Task) SET @Return = @@ERROR

    IF @status = 0 SET @status = @Return

    END

    END

    DROP TABLE #DBAZ

    SET NOCOUNT OFF

    RETURN (@Status)

    GO

    And to test:

    EXECUTE sp_ListFiles 'C:\Documents and Settings\All Users\Desktop\FolderName',NULL,NULL,NULL,1

  • Hi All, I found a rather useful post/blog that did what I was after. The link is below...

    http://agilebi.com/cs/blogs/jwelch/archive/2008/02/02/importing-files-using-ssis.aspx

  • Hi,

    The statement

    SELECT SUBSTRING(Name,40,100) AS Files

    FROM #DBAZ

    Does not seem to work for me, but

    SELECT ltrim(rtrim(reverse(SUBSTRING(reverse(Name), 1,charindex(char(32),reverse(Name))-1)))) AS Files

    FROM #DBAZ

    Does the trick

  • Try this:

    SET NOCOUNT ON

    DECLARE @Command VARCHAR(100)

    SET @Command = 'dir /b /s '

    DECLARE @Folder VARCHAR(100)

    SET @Folder = 'D:\AS'

    DECLARE @FilesInAFolder TABLE (FileNamesWithFolder VARCHAR(500))

    INSERT INTO @FilesInAFolder

    EXEC MASTER..xp_cmdshell @Command

    ; WITH CTE AS

    (

    SELECT REVERSE(FileNamesWithFolder) ReverseFileNames FROM @FilesInAFolder

    )

    SELECT --FileNames = STUFF ( FileNamesWithFolder , 1 , (LEN(FileNamesWithFolder) - CHARINDEX ('\', REVERSE(FileNamesWithFolder))+1) , '')

    FileNames = REVERSE ( LEFT (ReverseFileNames, CHARINDEX ('\', ReverseFileNames)-1))

    FROM CTE

    WHERE ReverseFileNames IS NOT NULL

  • :exclamation: ATTENTION Please : Two Year Old Thread

  • I've used variations on this in the past, with the insert into #temp exec xp_cmdshell 'dir'

    I tried to wrap this in a stored procedure, to be used by other procedures, but then I got the dreaded

    [font="Courier New"]An INSERT EXEC statement cannot be nested.[/font]

    Suggestions on how to use the results of the procedure in my own temp table?

  • The age of a thread does not mean it is not pertinent.

  • Just a slight amend to SSCrazy's script and worth a bump as it was very useful. If found that it didn't look in a particular folder as the @folder variable was only defined and never used. It simply required getting rid of that and using the full path in the @Command variable. The updated script is:

    SET NOCOUNT ON

    DECLARE @Command VARCHAR(1000)

    SET @Command = 'dir d:\ftp\clients\somecompany\in /b /s ' -- the full path on the SQL Server instance

    DECLARE @FilesInAFolder TABLE (FileNamesWithFolder VARCHAR(500))

    INSERT INTO @FilesInAFolder

    EXEC MASTER..xp_cmdshell @Command

    ; WITH CTE AS

    (

    SELECT REVERSE(FileNamesWithFolder) ReverseFileNames FROM @FilesInAFolder

    )

    SELECT FileNames = REVERSE ( LEFT (ReverseFileNames, CHARINDEX ('\', ReverseFileNames)-1))

    FROM CTE

    WHERE ReverseFileNames IS NOT NULL

  • I get this error with the latest revision (containing the full path)

    Msg 537, Level 16, State 2, Line 10

    Invalid length parameter passed to the LEFT or SUBSTRING function.

    Can't figure out why. Also it would be great to pull in the file date, can this be done as a field as well?

    <hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh

  • Well spotted! The SUBSTRING error is occurring because I'm willing to bet that the filepath your using has a space in it somewhere - xp_cmdshell doesn't support spaces (as far as I'm aware) so you have to use the short filename instead. This is easy enough to find by using the "dir /x" command in a DOS prompt. For example, say I have a path of:

    c:\ftp\Customer1\DSV Files

    The short filename would be:

    c:\ftp\Customer1\DSVFIL~1

    I'll have to have a play at getting the file date into another column. If you remove the /b parameter (this is the "bare format" parameter) from the dir command you'll get it (along with a lot of other stuff you won't want!), but the CTE will error.

  • I know... old thread. 😉

    Just want to correct that last post. xp_CmdShell support names with spaces in them just like DOS does... you have to put double-quotes around the entire path.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Cheers Jeff! Glad I qualified that with an "AFAIA" now 🙂

  • Heh... no worries. I just love working with xp_CmdShell and wanted to make sure that people knew spaces in a name isn't an obstacle.

    Thanks for the feedback..

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 14 posts - 1 through 13 (of 13 total)

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