The FileDB Object's Methods
You can see a list of the FileDB object's methods in Table 1. Here's how each method works:
The Include method. The Include method, which Listing 2 shows, uses the FileSystemObject object to obtain a collection of files to be added to the Recordset object. The Include method's syntax is fairly specific. To add all files in a folder, the Path parameter must end with a backslash (\). Without the trailing backslash, the Include method assumes you want to add a specific file. To include files in subfolders of the specified folder, the Recurse parameter must be true.
Callout A in Listing 2 shows how the Include method adds information about a file to the recordset. The method follows these steps:
- It calls the Recordset object's AddNew method. This creates a temporary record that hasn't been saved to the recordset yet.
- It sets each field's value. In this case, the Path field will get the Parent-Folder property's value, the Name field will get the Name property's value, and so forth.
- It uses the Recordset's Update method to save the record to the recordset.
The Exclude method. This method, which uses the Recordset object's Filter property to select the records in the recordset that match the folder or filename specified in the Path parameter (see Web Listing 1). The Exclude method's Path parameter must end in a backslash if you want to remove every file in a particular folder. Otherwise, the Exclude method will assume you want to remove a specific file. Unlike the Include method, the Exclude method doesn't use the FileSystemObject object. This object doesn't let you use wildcards (or something similar) to filter a collection of files by file type or filename.
Fortunately, the Recordset object's Filter property provides the needed filtering capabilities. In the Exclude method, the Filter property is set to a string such as
Path Like 'foldername%'
where foldername is the name of the folder. If the Recurse parameter is true, the percent (%) character is included. Otherwise, it's excluded. (I'll provide more information about the percent character shortly.)
When the Path parameter doesn't end with a backslash, the Exclude method assumes you're searching for a specific file. In this case, the Exclude method extracts the file's parent folder name, gets the Files collection for that folder, and iterates through the collection. The Exclude method selects the record in the recordset that exactly matches the name specified in the Path parameter. The Exclude method then deletes the file associated with that record.
When the Path parameter ends with a backslash, the Exclude method removes the trailing backslash from the Path parameter and works with the entire Files collection. When the percent character isn't present in the Filter property's string (i.e., Recurse is false), the Exclude method searches for folders whose names start with the folder name specified in the Path parameter. When the percent character is present (i.e., Recurse is true), the Exclude method searches for folders and their subfolders whose names match the folder name specified in the Path parameter. The method then deletes all the files in the folders found by the search.
The Exclude method ends by setting the Filter property to an empty string. This removes the filter and gets the method ready for the next search.
The WriteCSV method. The Write CSV method writes a recordset to a CSV file. The method's first parameter is the name of the CSV file to save; its second parameter is a Boolean (true/ false) value that tells the method whether it's allowed to overwrite the file if it already exists. The FileDB object's source code contains two functions, WriteFields and WriteRecords, which use a TextStream object to write the Recordset object's fields and data to a CSV file. The first line of the CSV file will contain the field names. Figure 2 shows what the CSV file looks like based on the recordset in Figure 1.
As I was developing FileDB, I discovered that the Recordset object doesn't account for duplicate records, so a file can be added to a recordset more than once. To account for this, I needed to filter out duplicate records because the CSV file should contain only one line for each file.
To filter out duplicates, the WriteCSV method first saves the Recordset object's data to a temporary CSV file using the WriteFields and WriteRecords functions, then closes the Recordset object. Next, it creates a Connection object and configures a connection string that sets the temporary file's directory as a data source. The WriteCSV method opens the temporary CSV file as a recordset and uses a Select Distinct query to filter out duplicate records. After this step is completed, the WriteCSV method uses the WriteFields and WriteRecords functions to write the final copy of the CSV file. Last, the method deletes the temporary CSV file. You have to create a temporary CSV file first because there's no way to execute the Select Distinct query against a disconnected recordset. (You can't execute SQL statements such as the Select Distinct query without a data source.)
A Sample Script
Listing 3 shows a sample script, CurrentDirectory.vbs, which uses the FileDB component to create a CSV file containing a listing of the files in the current directory. First, the script declares the FileDB variable that will hold an instance of the FileDB object. Next, it creates the FileDB object using VBScript's CreateObject function. It then uses the FileDB object's Include method to add all the files in the current directory (the trailing backslash is required to tell the Include method that we're giving it a folder name). The second parameter (Recurse) is set to False, so files in subfolders will not be added to the recordset. Last, Current-Directory.vbs uses the FileDB object's WriteCSV method to create a file called CurrentDirectory.csv. The Write CSV method's second parameter is set to True, so the CSV file will be overwritten if it already exists.
The FileDB component has many potential uses. For example, a file server administrator might want to store the data for files in a shared network folder. The administrator can then import the CSV file into a database tool (e.g., Microsoft Access) and perform useful queries or reports on this information (e.g., a listing of the 100 largest files in the database). Next month, I'll provide the script that opens an NTBackup Bks file and creates a CSV file based on its contents.
Know Your Limits
Keep in mind that the FileDB component stores information about each file in memory. If you run the component against a folder structure with a large number of folders and files, it could potentially consume a substantial amount of memory.
End of Article
Prev. page
1
[2]
next page -->
