SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


List Files In Directory Using SQL


List Files In Directory Using SQL

Author
Message
Alasdair Thomson
Alasdair Thomson
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1060 Visits: 506
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.
Alasdair Thomson
Alasdair Thomson
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1060 Visits: 506
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
Alasdair Thomson
Alasdair Thomson
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1060 Visits: 506
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
Fishbarnriots
Fishbarnriots
Say Hey Kid
Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)

Group: General Forum Members
Points: 694 Visits: 2465
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
ColdCoffee
ColdCoffee
SSCrazy Eights
SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)

Group: General Forum Members
Points: 9103 Visits: 5555
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


ColdCoffee
ColdCoffee
SSCrazy Eights
SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)

Group: General Forum Members
Points: 9103 Visits: 5555
Exclamation ATTENTION Please : Two Year Old Thread
andre.quitta
andre.quitta
SSC-Addicted
SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)

Group: General Forum Members
Points: 453 Visits: 400
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?
myother1
myother1
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 29
The age of a thread does not mean it is not pertinent.
FortyEightK
FortyEightK
SSC-Enthusiastic
SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)

Group: General Forum Members
Points: 172 Visits: 456
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
Jeffery Williams
Jeffery Williams
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1271 Visits: 913
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search