May 6, 2011 at 9:40 am
Greetings. I'm trying to build a table of all the files in a directory or file path specified by the user. If the user chooses 'C:\' as the starting point, it will list all the files on the drive in one column, and the file path in another column. All is well, with the exception of folders that contain single quotes in the folder name like C:\Documents and Settings\Administrator\Administrator's Documents. I don't know why anyone with half a brain would do that, but here it is, and it is causing me trouble. Any how, I've posted the entire code in the third block below, in case anyone is interested, but it boils down to the problem directly below. I've tried everything, brackets, double quotes, REPLACE(), and I cannot get this to run. I sure would appreciate any tips for the problem, or any suggestions for improvement on the procedure in general. Thank you.
EXEC master..xp_DirTree 'c:\documents and settings\administrator\administrator's documents',1,1
Produces (predictably)...
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 's'.
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string ',1,1
'.
And now the whole thing...
/*
The @top_path variable is used as the starting point. Once xp_DirTree is executed on @top_path,
the files will be dumped into #Files, and the folders will be dumped into #FolderBucket, where
they will all be passed into xp_DirTree, and the cycle continues until there are no more
folders to be processed
*/
DECLARE @top_path VARCHAR(200)
SET @top_path = 'c:\documents and settings\administrator\'
DECLARE @sub_path VARCHAR(200)
DECLARE @folder_id INT
DECLARE @sql VARCHAR(4000)
--Create a table to dump the output of xp_DirTree
IF OBJECT_ID('TempDB..#DirTree','u') IS NOT NULL
DROP TABLE #DirTree
CREATE TABLE #DirTree
(
FOLDER_ID INT IDENTITY(1,1),
FILE_PATH VARCHAR(500),
FOLDER_NAME VARCHAR(128),
DEPTH INT,
IS_FILE BIT
)
--Create a table to dump the folders from xp_DirTree
IF OBJECT_ID('TempDB..#FolderBucket','u') IS NOT NULL
DROP TABLE #FolderBucket
CREATE TABLE #FolderBucket
(
FOLDER_ID INT IDENTITY(1,1),
FILE_PATH VARCHAR(500),
FOLDER_NAME VARCHAR(128),
PROCESSED BIT DEFAULT(0)
)
--Create a table to dump the files from xp_DirTree
IF OBJECT_ID('TempDB..#Files','u') IS NOT NULL
DROP TABLE #Files
CREATE TABLE #Files
(
ID INT IDENTITY,
FILE_PATH VARCHAR(500),
[FILE_NAME] VARCHAR(128)
)
--Execute using the @top_path variable set above
INSERT INTO #DirTree(folder_name, depth, is_file)
EXEC master..xp_DirTree @top_path,1,1
--Set the file_path field in #DirTree to be equal to @top_path
UPDATE #DirTree
SET file_path = @top_path
--Begin the loop that looks for records in #DirTree. Below they are deleted, so
--if there are any in there, they need to be processed.
WHILE EXISTS (SELECT folder_id FROM #DirTree)
BEGIN
--Put the folders in #FolderBucket, which will have a value of processed = 0
INSERT INTO #FolderBucket(file_path,folder_name)
SELECT
FILE_PATH,
FOLDER_NAME
FROM #DirTree
WHERE IS_FILE = 0
--Put the files in #Files where they will stay, and nothing more will be
--done with them at this point.
INSERT INTO #Files
SELECT
file_path,
folder_name
FROM #DirTree
WHERE is_file = 1
--Purge #DirTree of all records.
DELETE #DirTree
--Begin the loop that looks for folders to be processed in #FolderBucket.
--Once they are run through xp_DirTree, the processed field will be
--set to 1.
WHILE EXISTS (SELECT folder_id FROM #FolderBucket WHERE processed = 0)
BEGIN
SELECT @folder_id = (SELECT MIN(folder_id) FROM #FolderBucket WHERE processed = 0)
--Create the new file path for xp_DirTree, which is just an extention of the current
--file path with the addition of the current folder name.
SELECT @sub_path = (SELECT file_path + '' + folder_name FROM #FolderBucket WHERE folder_id = @folder_id)
--Run xp_DirTree dynamically with the current @sub_path variable.
--There may be files with single quotes in the name, so I tried to deal
--with them by using REPLACE, but is does not seem to be working.
PRINT(@sub_path)
SELECT @sql =
'
INSERT INTO #DirTree(folder_name, depth, is_file)
EXEC master..xp_DirTree ''' + REPLACE(@sub_path,'''','''''') + ''',1,1
'
EXEC(@sql)
--Set the current folder processed flag = 1
UPDATE #FolderBucket
SET processed = 1
WHERE folder_id = @folder_id
--Update the file_path in #DirTree to get ready for the addition of the next
--level of folders
UPDATE #DirTree
SET file_path = @sub_path + '\'
WHERE file_path IS NULL
END
END
--See the results
SELECT
*
FROM #Files
ORDER BY file_path,[file_name]
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
May 9, 2011 at 7:18 am
Small update here. I noticed that although the file 'administrator''s documents' is named as such when navigating to it, when xp_DirTree lists out the folders in c:\documents and settings\administrator, it actually returns the folder name as 'my documents', and properly displays the files and folders contained. This solves this one instance, but I still have the same problem if a user has personal folder names containing a single quote. Still flumoxed.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
May 9, 2011 at 10:33 am
Looks like a ton-o-fun, Greg. I'll try to have a look at it tonight if someone doesn't beat me to it.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 9, 2011 at 11:58 am
Your code is working perfectly fine for me, Greg. I created a folder in c:\temp called "todd's files" and as long as there is a file in that folder, the folder will show on the output.
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
May 9, 2011 at 2:02 pm
Thanks Jeff, it has been quite fun. And I'm confident I'll think of a use for it later.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
May 9, 2011 at 2:15 pm
toddasd (5/9/2011)
Your code is working perfectly fine for me, Greg. I created a folder in c:\temp called "todd's files" and as long as there is a file in that folder, the folder will show on the output.
DOH! You are correct, it does seem to be working. Come to think of it, I did not check if some of the folders with single quotes had files in them. However, when I did as you did, all seems to be well. Anyhow, sorry for the false alarm (with egg on my face).
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply