|
This is a long delayed response to
Karen's message. I had several other things I was working on, and
know that the issue is fixed, but over the years I have seen many
posts looking for ways to search files based on different criteria
like name, date, size, etc... I wrote a _vbscript_ file that will
generate a CSV list of all the files and directories in a given
directory and collect several bits of information about each. I
use a stored procedure to call the _vbscript_ and to load the
results into a temp table for searching. I'll paste my code below
for anyone who wants it.
This code calls the CHKTABLE SP I mentioned in an earlier message. I have not duplicated it here. There is one slight problem to searching files this way. The R:BASE wildcards (_ and %) are valid for use in file names. If you want to search with them as wildcards, then you can SELECT ... LIKE 'whatever%' or SELECT ... CONTAINS 'whatever_.docx'. If, on the other hand you are looking for a file with a name that contains either of those characters, you have to escape them, so you have to use LIKE. I recommend the pipe '|' as an escape character since it is an invalid Windows file name character. SELECT ... LIKE 'doc|_.txt' ESCAPE '|' would return doc_.txt, but not doc1.txt. If you do use this SP and VBS file, make sure that you create vdlsource TEXT 255 as the only argument (Windows file names are limited to 255 characters) and set the return type to be an integer. Remember, a return of 0 IS NOT AN ERROR, it just means the source directory is empty. -1 indicates an error. There are three files. the _vbscript_ file, the RMD file that makes the SP, and an RGW file used for the import. Please let me know if you have any questions or suggestions. Thanks, Jason _vbscript_ file 'GenDirList.vbs 'Jason Kramer '07-05-2012 'This script generates a CSV file containing a list of all the files and folders in 'a given folder. It requires one parameter, the folder to scan. It will produce an 'output file named dirlist.csv in the same folder this file resides in if the source 'folder is found. If the source folder itself does not exists, no output file will 'be generated, and any previous output file will be deleted. If the source folder 'exists, but is empty, then dirlist.csv will be created, but will be empty. If the 'source folder exists and contains any files or folders, they will be listed in 'dirlist.csv, one per line with the following format: 'name,date created,date last accessed,date modified,size,type (based on extension),kind (FILE or FOLDER) 'If there is an existing dirlist.csv when this script runs, it will be overwritten or deleted 'without warning. Option Explicit Dim fso,sBasefol,folBaseFol,fleDirList,fleCurFile,folCurFol 'File system object,the path to the source(base) folder 'the base folder object,the output file,the current file 'the current sub-folder. Set fso = CreateObject("Scripting.FileSystemObject") If WScript.Arguments.Count = 1 Then sBaseFol = WScript.Arguments(0) If fso.FolderExists(sBaseFol) Then Set folBaseFol = fso.GetFolder(sBaseFol) Set fleDirList = fso.OpenTextFile(("dirlist.csv"), 2, True, 0) If folBaseFol.Files.Count <> 0 Then For Each fleCurFile in folBaseFol.Files fleDirList.WriteLine(fleCurFile.Name + "," + Cstr(fleCurFile.DateCreated) + "," + Cstr(fleCurFile.DateLastAccessed) + "," + CStr(fleCurFile.DateLastModified) + "," + Cstr(fleCurFile.Size) + "," + fleCurFile.Type + ",FILE") Next End If If folBaseFol.SubFolders.Count <> 0 Then For Each folCurFol in folBaseFol.SubFolders fleDirList.WriteLine(folCurFol.Name + "," + Cstr(folCurFol.DateCreated) + "," + Cstr(folCurFol.DateLastAccessed) + "," + CStr(folCurFol.DateLastModified) + "," + Cstr(folCurFol.Size) + "," + folCurFol.Type + ",FOLDER") Next End If fleDirList.Close Else If fso.FileExists("dirlist.csv") Then fso.DeleteFile("dirlist.csv") End If End If Else If fso.FileExists("dirlist.csv") Then fso.DeleteFile("dirlist.csv") End If End If RMD File -- DIRLIST Stored Procedure -- Jason Kramer -- 07-05-2012 -- This RMD file is used by the DIRLIST stored procedure. It requires one -- parameter, the source directory (folder), and returns an integer indicating -- an error or the number of rows (files and directories) in the source -- directory. It creates the TEMP table tdirlist to store the name and some -- information (date created,date last accessed,date last modified,size,type, -- and kind) about each file or directory. If the source folder is empty, then -- tdirlist will still be created, but will have zero rows (a return value of -- zero IS NOT AN ERROR. It is a count.). If the source folder does not -- exist, or an error occurs, a negative one will be returned. If tdirlist -- already exists from a prvious call, then it will be emptied and refilled -- with new data. -- The size is given in bytes. All directories (folders) will have a size of -- zero since this is how Windows reports their size. This IS NOT an error. -- The type is what Windows consideres the file based on its extension, not -- its contents. -- The kind is simply FILE for files and FOLDER for directories (folders). -- DIRLIST requires the CHKTABLE stored procedure to exist. -- DIRLIST requires the gendirlist.vbs file to exist in the current directory. -- DIRLIST requires the dirlist.rgw file to exist in the current directory. -- Required parameter - vdlsource TEXT - the source directory(folder). -- Return value: -- -1 - An error occured. -- 0 ... X - The number of files and folders in the source. SET VAR vdllaunch TEXT = NULL SET VAR vdlretval INTEGER = NULL SET VAR vdllaunch = 'gendirlist.vbs|"' + .vdlsource + '"|W' LAUNCH .vdllaunch IF (CHKFILE('dirlist.CSV')) = 1 THEN IF (CALL chktable('tdirlist')) = 0 THEN CREATE TEMPORARY TABLE tdirlist (fname LONG VARCHAR NOT NULL,fdatec DATETIME NOT NULL,fdatela DATETIME NOT NULL,fdatelm DATETIME NOT NULL,fsize INTEGER NOT NULL,ftype LONG VARCHAR NOT NULL, fkind TEXT 6 NOT NULL) ELSE DELETE FROM tdirlist ENDIF IF SQLCODE < 0 THEN SET VAR vdlretval = -1 ENDIF GATEWAY import CSV dirlist.csv REPLACE tdirlist OPTION SPECIFICATION_FILE_NAME dirlist.rgw IF SQLCODE < 0 THEN SET VAR vdlretval = -1 ELSE SELECT COUNT fname INTO vdlretval FROM tdirlist ENDIF ELSE SET VAR vdlretval = -1 ENDIF CLEAR VAR vdlsource,vdllaunch RETURN .vdlretval RGW File [Common] App=R:BASE eXtreme 9.5(64) Description=Gateway Import: specification Specification=dirlist DatasetKeys=fname;fdatec;fdatela;fdatelm;fsize;ftype Mode=4 TableType=Text SourceFileName=C:\Users\jjkramer\Desktop\Test\dirlist.csv CharacterSet=0 [Mappings] Count=7 Map0=fname = Field1 Map1=fdatec = Field2 Map2=fdatela = Field3 Map3=fdatelm = Field4 Map4=fsize = Field5 Map5=ftype = Field6 Map6=fkind = Field7 [Text] FieldDelimiter=2C TextQualifier=22 RecordSeparator=0D0A Fixed=0 RowFirst=1 RowLast=2147483647 [DataFormat] DateOrder=0 DateSeparator=2F TimeSeparator=3A DecimalSeparator=2E FourDigitYear=1 LeadingZerosInDate=0 Jason Kramer University Archives and Records Management 002 Pearson Hall (302) 831 - 3127 (voice) (302) 831 - 6903 (fax)On 6/14/2012 3:55 PM, [email protected] wrote: Okay, now I see it. I guess I've been lucky then? I've always used |
