Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

List Files In Directory Using SQL Expand / Collapse
Author
Message
Posted Tuesday, September 23, 2008 8:50 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, February 11, 2014 7:25 AM
Points: 450, Visits: 481
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.
Post #574455
Posted Tuesday, September 23, 2008 9:40 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, February 11, 2014 7:25 AM
Points: 450, Visits: 481
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
Post #574553
Posted Wednesday, September 24, 2008 5:47 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, February 11, 2014 7:25 AM
Points: 450, Visits: 481
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
Post #575085
Posted Wednesday, September 15, 2010 2:34 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 4:21 AM
Points: 246, Visits: 2,038
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
Post #986088
Posted Wednesday, September 15, 2010 4:16 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:03 PM
Points: 2,262, Visits: 5,405
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

Post #986131
Posted Wednesday, September 15, 2010 4:19 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:03 PM
Points: 2,262, Visits: 5,405
ATTENTION Please : Two Year Old Thread
Post #986133
Posted Friday, November 16, 2012 11:47 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 12:29 PM
Points: 94, Visits: 251
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

An INSERT EXEC statement cannot be nested.

Suggestions on how to use the results of the procedure in my own temp table?
Post #1385810
Posted Thursday, April 4, 2013 9:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 11, 2013 8:09 AM
Points: 6, Visits: 29
The age of a thread does not mean it is not pertinent.
Post #1438864
Posted Tuesday, August 6, 2013 4:36 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 18, 2014 2:28 AM
Points: 31, Visits: 325
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
Post #1481228
Posted Tuesday, September 24, 2013 10:37 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, April 11, 2014 12:48 PM
Points: 139, Visits: 601
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
Post #1497974
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse