| Downloads |
|---|
| 100084.zip |
The available tools for writing structured data to files and reading it back are too complex for everyday use in Windows scripts. A set of simple wrapper functions can fix that problem, allowing scripters to save dictionary data to a file and read it back without laborious coding. |
Tools for saving data to files weren't developed with administrators or scripters in mind. This isn't typically a problem for most support staff, but when you need to save script data—that is, complex information used within a script—to a file and read it back elsewhere, having a simple tool to handle the process is crucial. I'll discuss some of the reasons why existing tools for persisting data to a file aren't as useful for scripters as they are for programmers, then I’ll demonstrate a couple of Windows Script Host (WSH) helper functions that let you quickly read and write simple key-value data pairs to an XML file and read them back—without having to understand the intricacies of XML.
The Problem: Data File Access Wasn't Made for Scripters
Plenty of tools exist for saving data to files, but they're called tools because they can be used by programmers to build data access applications. Everything from routines for writing plain text files to Microsoft's ActiveX Data Objects (ADO) and XML reading and writing components are technically tools. However, these tools are for designing ways to read and write data that meets programmers’ needs. They aren’t designed to let someone read or write arbitrary structured data without worrying about the details. The problem is that the tools are for programmers, not scripters.
For example, consider the de facto standard for exporting data to text files: XML. XML can encapsulate virtually any data in a text file, along with a schema that clearly defines the kind of data encapsulated. A well-designed application can determine whether it can use the encapsulated data and if so, exactly how to translate the data. A programmer who is new to XML might take a few hours to get the feel for how XML works, but a trained programmer is typically already used to dealing with complex data structures in code, which makes another complex data structure such as XML more comprehensible. Furthermore, spending a few hours getting data to import and export via XML isn't a significant issue if you're using XML as part of a long-term programming project and are likely to use the same types of data in future versions of the application.
For scripters, however, XML often seems to be a cumbersome answer to a question they didn't ask. Administrative needs typically involve being able to quickly save a collection of data, then cleanly reuse that data elsewhere. Even for short scripts, saving simple data to an XML file requires a substantial up-front learning investment. In addition, a short script can easily grow two or three times longer because of the amount of work needed to manipulate the structures.
Fortunately, you can significantly simplify the process of saving data to files by using tools native to Windows 2000 and later. Native Windows support for ADO includes some scriptable interfaces that let you save data to a file and later read that data back without a lot of decision-making. Although it still takes a few lines of code to handle each task, the code is identical no matter what you're doing—which lets you wrap all of the work into a reusable function. The code is the same for any list of data saved to a file, so once you have the function you can focus on real work rather than thinking about how to save data. And if we restrict the problem to merely saving data contained in a dictionary and later restoring the data back into a dictionary, working with data this way gets even simpler.
Creating a Table with ADODB.Recordset
The first step is understanding how to create a table of data from script. If you've used scripts that deal with data sources before, some of this code will be familiar.
First, you need to create some data to use. For convenience, let’s generate some arbitrary data in a scripting dictionary:
Set sd = CreateObject( _
"Scripting.Dictionary")
Set Wmi = GetObject("winmgmts:")
Set results = Wmi.ExecQuery( _
"select name,sid from Win32_UserAccount")
For each result in results
sd(result.name) = result.sid
Next
This code gives us a dictionary containing the names for each local user account as keys and the string-formatted SIDs associated with each account as the value. You can, of course, use any dictionary populated with data here; this example is simply one demonstration of important name-value pairs that you would typically store in a dictionary.
For demonstration purposes, let's create some fake accounts: abel (SID of 101), baker (SID of 102), and charlie (SID of 103). Your local account list will be much longer, and each SID will be 45 characters—but this example data will be easier for us to display compactly. Figure 1 shows how our sample data might look in a spreadsheet.
Next, we need to get our data into an ADO recordset. We create a recordset like this:
Dim rs
Set rs = CreateObject( _
"ADODB.Recordset")
A recordset is essentially a table, much like a spreadsheet. To set up a recordset, we need to tell it what data columns (called fields in ADO terminology) are in the table. We start by setting up a reference to the recordset fields collection like this:
Set fields = rs.Fields
The fields collection has an Append method for adding fields to it. We need to specify the name of each field and the type of data in the field. Although we could call the fields Name and SID, let’s use the generic names Key and Item as shown in Figure 2. This naming convention will be more accurate for general use with any data dictionary. We also need to tell the recordset that both fields contain string data; this action makes the recordset completely generic, because you can convert any data to a string. Thus, we create the two fields like this:
const adBSTR = 8
fields.Append "Key", adBSTR
fields.Append "Item", adBSTR
The constant adBSTR is the value ADO uses to represent string data types; I could have used the value 8 in place of adBSTR, but this approach gives you more information than the seemingly arbitrary number 8.
Our recordset is now ready to use. To open it, type
rs.Open
Next we need to add the data to the recordset. Each time we want to add a new set of data, we must explicitly add a new record using the recordset's AddNew method, then set the value for each field by name. We do this by cycling through all the keys in the dictionary sd, and adding the dictionary key in the field named Key and the item associated with the dictionary key to the Item field, as the following code shows:
For each key in sd.keys
rs.AddNew
rs.Fields("Key") = key
rs.Fields("Item") = sd.Item(key)
WScript.Echo key, sd.Item(key)
Next
The WScript.Echo statement shows you the actual data added to the recordset at each step.
The final step is to save the data to a file. The Recordset object has a Save method to do this for you; you simply need to specify what filename to save to and what format to use for saving the data. For our purposes, two formats are available: Microsoft's Advanced Data Tablegram (ADTG) format, and ADO XML. Let’s use XML, because you can read and modify it in a text editor, unlike the ADTG format. To save the data to the file C:\usersids.xml, run the following code:
const adPersistXML = 1
rs.Save "C:\usersids.xml", adPersistXML
Although persisting data to an arbitrary file in an arbitrary form can be complicated, we’ve done so quite simply. We can now store all our data for easy reuse later on. To export data, all we need is to have a set of data in a dictionary and the name of the file we want to save to.
The code in Listing 1 is a complete function, SaveDictionary, that you can drop into your own scripts to export data. It includes some extra code to handle one special problem: The recordset's Save method won’t overwrite a preexisting file. For example, if you try to write data to the file C:\usersids.xml but C:\usersids.xml already exists, the Save method will fail. To let you freely overwrite files, the SaveDictionary function checks to see whether the file you will save to already exists. If the file is present, SaveDictionary silently deletes it before saving the recordset. After you have your data in a file, the next step is reading that data into a script.
Getting Data From an ADODB XML File
So far we've used a dictionary to collect data, then saved that data to a file. To make this technique useful for real work, we need a way to get the data back into a dictionary in another script.
Generally speaking, using ADO to read data from a file is complicated. The problem isn't the amount of code (although it can take several lines and a few object references). The problem is that for general use, you need to set up a data connection object with all sorts of details specified in a connection string that has a wide variety of possible forms. Fortunately, by exporting to ADO's XML, we've bypassed all of that work: Windows will know we're dealing with a file in a native format, so we just need to specify where the file is and open the recordset, like this:
set rs = CreateObject("ADODB.Recordset")
rs.Source = "C:\usersids.xml"
rs.Open()
rs.MoveFirst()
Our script now has the data from the file C:\usersids.xml in the recordset rs. Now we can create our dictionary:
Set sd = CreateObject(_
"Scripting.Dictionary")
To re-create the dictionary from the recordset we need to step through each record in the recordset. Stepping through the recordset isn't quite as easy as stepping through the dictionary, but it's close. The code for stepping through the recordset is as follows:
Do Until rs.EOF
Set fields = rs.Fields
sd(fields(0).Value) = _
fields(1).Value
WScript.Echo fields(0).Value, _
fields(1).Value
rs.MoveNext
Loop
Let's walk through this loop once. The recordset is always parked on a particular record, and it stays there until you do something such as add a new record or explicitly move to another existing record. When we start, the recordset is on the first record. We could refer to the field named Key in that first record as fields("Keys"), and refer to the field named Items as fields("Items"). However, you can also refer to an existing field by index—the first field in a recordset has an index of 0, the second an index of 1. Using the indices ensures that our code works no matter what names we used for the fields.
Another point to note is that instead of putting fields(0) and fields(1) into the dictionary, I used fields(0).Value and fields(1).Value. To get the actual value contained in the field instead of a reference to the field object, you must specify the Value property of that field—which is why I used fields("Key").Value and fields("Item").Value. If you don’t specify the values, your dictionary will contain references to the fields instead of the data from them, and as soon as you close the recordset, the references will be useless.
After getting the data into our dictionary for this value, we need to explicitly move the recordset to the next record, using rs.MoveNext. After reaching the end of the data, close the recordset. To demonstrate that the dictionary contains the data you want, run the following code to echo the dictionary's contents:
rs.Close()
For each key in sd.keys
WScript.Echo key, sd(key)
Next
Although the demonstration code is several lines, it's almost completely reusable; the only thing you need to change to read another file is the filename. I've wrapped the code into a LoadDictionary function, which Listing 2 contains, that takes any filename as its argument and returns a dictionary object. You can drop the LoadDictionary function into your own scripts and load the data from a file into a dictionary without needing to rewrite the code for reading the file.
Using the Functions in Your Own Scripts
These two functions provide a straightforward way to export structured data from one script and reuse it in another script. Although this solution isn’t suitable for extremely complex data, it works perfectly if you simply need to use and save name and value pairs. You can edit the intermediary data file by hand if necessary, which isn’t possible for most standard binary database storage.
I've also included two scripts and a data file to demonstrate how the functions work. To download these files, click the Download the Code Here button at the top of the page. The first file is ExportLocalAccounts.vbs, which obtains a list of local user accounts from Windows Management Instrumentation (WMI), then uses SaveDictionary to save the account names and SIDs to a file called ExportedLocalAccounts.xml. The second file is an example usersids.xml file. The last file, ShowExportedLocalAccount.vbs, reads usersids.xml from the current folder and echoes the names and SIDs. If you're not used to working with functions in scripts, the demonstrations will show you how to correctly call the functions in your own code.