Jason, Since you seem to mess with VBScript a bit, you might be interested in using the RMScript DLL I wrote that is a wrapper for the MS Script Control ver 1.0. It allows you to store your VBS as VarChar data, pass the Script to the Script engine, then call it from RBAse using DLCall. This is pretty nifty where you want to return values directly to an RBase variable.
The RMScript, examples and helpfile are at: http://www.byerley.net/RMScriptPak.zip ----- Original Message ----- From: "Jason Kramer" <[email protected]> To: "RBASE-L Mailing List" <[email protected]> Sent: Friday, July 06, 2012 2:07 PM Subject: [RBASE-L] - File Searching : 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 VB Script 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 VB Script 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 VB Script 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 : : VB Script 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 : wildcards up until now, and it's never been unpredictable. : : So then is there any reliable way to check for files in a directory : without having to load a directory listing into a file, load into a temp : table, etc.? Alot of work when a simple Chkfile always used to work : for me... : : Karen : : : : In a message dated 6/14/2012 2:48:52 PM Central Daylight Time, [email protected] writes: : : Karen, : Better make that trip to the Opthamologist... ;-) : : From the 6.5 HelpFile: : : "CHKFILE(filename)) : : Checks to see if a file exists. If no path is specified, the function checks : for the file in the current directory. Otherwise, the function checks for : the file in the specified location. : : The function returns a 1 if the file is found, and 0 if it is not found. : Wildcards in the filename will produce unpredicatable results. " : : From the 9.1 HelpFile: : : "(CHKFILE('filespec')) : Checks to see if a file or folder name exists. If no path is specified, the : function checks for the file or folder name in the current directory. : Otherwise, the function checks for the file or folder name in the specified : location. : : The function returns a 1 if the file or folder name is found, and 0 if it is : not found. Wildcards in the filename will produce unpredicatable results." : : : : :
