Over the last several weeks, I've been working extensively with comma-separated
value (CSV) files. One simple example is the component I described in "Gathering
File System Data" (October 2006, Instant-Doc 93451), FileDB.wsc, which saves
information about files to a CSV file. CSV files are plain-text database files
in which each line represents a record (row) that's divided into fields (columns),
with commas separating data items from each other, as Figure
1 shows. The first line (sometimes called a header line) of the CSV file
contains the field or column names, and subsequent lines contain the data. Double-quote
characters define individual items in case a data item contains a comma. CSV
files can contain an arbitrary number of lines, and they might not contain a
header line that contains the field names. As I worked with the output from
FileDB.wsc, I realized that an HTML application (HTA) would be an ideal way
to quickly and easily view CSV files. Let's take a look at the HTA I wrote and
see how the scripting behind it makes it work.
An HTA is a dynamic HTML (DHTML) document with an .hta extension that runs
at a higher privilege level than Internet Explorer (IE). As such, HTAs can use
any available ActiveX objects on the computer, even those that aren't marked
as "safe for scripting" for IE. For example, an HTML document that attempts
to use the FileSystemObject will generate a warning message if you open the
document in IE because the FileSystemObject is not marked "safe for scripting."
(And this is a good thing, because otherwise any Web site that you browse in
IE could potentially access the local file system.) In contrast, HTAs run in
mshta.exe, which allows access to all ActiveX objects on the computer. As such,
HTAs need to be treated with the same care as scripts or executables, but they
provide a convenient way to create GUI-based applications.
The Microsoft Tabular Data Control (TDC) simplifies the creation of an HTA
for viewing CSV files. The TDC is an ActiveX object that lets you display CSV-based
data in an HTML table. It's a core IE component that first appeared in IE 4.0
and is available on every Windows OS that has IE 4.0 or later. The HTA I wrote,
CSVviewer.hta, is a convenient tool for anyone who needs to view a CSV file
but doesn't need a data-analysis tool such as a spread-sheet or a database.
The CSVviewer .hta script is too long to print in its entirety here, but you
can download it from the Windows IT Pro Web site at http://www.windowsitpro.com/windowsscripting,
InstantDoc ID 94260.
The CSVviewer.hta script generates a screen like the one that Figure
2 shows. The HTA is straightforward: Simply enter the name of a CSV file
or click Browse to select a file, then click OK. If the CSV file's first line
doesn't contain field names, deselect the First line of CSV file contains
field names check box. The Display x records per page setting (where
x is a number between 1 and 999) defines the number of CSV file records
that the application will display at one time. To clear the form and start over
with a different CSV file, click Reset. Finally, you can click Exit to close
the application.
After you've selected a CSV file and clicked OK, the data from the file appears
in a table in the lower half of the window along with a Filter button and a
set of navigation buttons, as Figure 2 shows.
The Filter button provides access to the TDC's Filter property, which I explain
in a moment. The First Page, Last Page, Previous Page, and Next Page buttons
let you navigate through the data. You can also sort the data by clicking the
column headings. The first click of a column heading will perform an ascending
sort; subsequent clicks on the same column heading will reverse the sort order
with each click.
A Peek Under the Hood
CSVviewer.hta is easy to use, but there's plenty of scripting under the hood
that makes it work. I wanted CSVviewer.hta to be able to display data from an
arbitrary CSV file; that is, I didn't want to "hard-code" the column names and
the CSV file's name into the document. The CSVviewer.hta script accomplishes
these goals by reading the column headings and setting the appropriate TDC properties
at runtime.
Creating the object reference. Before you can use the TDC in
an HTML or HTA document, you need to insert an <object> element for it.
CSVviewer.hta uses the following HTML code to insert a TDC object:
<object id= "csvdata"
classid= "clsid:333C7BC4-460F-
11D0-BC04-0080C7055A83">
<param name="CaseSensitive"
value= false //>
</object>
The id attribute provides a named reference for the data in the table
to connect, or bind, to the TDC's data source (a CSV file). The classid attribute
must always be set to this particular value because it specifies the TDC's globally
unique identifier, which remains constant regardless of the computer it's used
on. The <object> element can include one or more <param> elements
that specify the TDC's initial property settings. CSVviewer.hta sets the TDC's
CaseSensitive property to false, which means that data comparisons will ignore
case. The other TDC properties (such as the DataURL property that specifies
the CSV file) are set at runtime. For more information about the TDC, see "About
Micro-soft Tabular Data Control" (http://msdn.microsoft.com/library/default.asp?url=/workshop/database/tdc/overview.asp).
Selecting a file. I wanted the HTA to provide an interactive
way of selecting the CSV file to view. HTML provides the <input type="file">
element, which includes a Browse button. This element's main purpose is to provide
a way to upload files to a Web server, but CSVviewer.hta uses it to select a
file. The id attribute makes the element's contents (i.e., the typed
or selected filename) easily accessible to the HTA's script code.
Verifying that the file exists. An <input type="submit">
element provides the OK button. This element's onclick event handler
calls the processFile function; that is, the HTA executes the processFile function
when you either press Enter or click OK. The processFile function, which you
can see in Listing 1, uses the
File-Exists method of the FileSystemObject to determine whether the selected
file exists. If the file doesn't exist, the function uses the alert method to
display an error message; otherwise, the function sets the global variable filename
to the name of the selected file. The function then executes the getFields
and putData methods, which I describe in a moment.