Display CSV Files in an HTA

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.

   Previous  [1]  2  Next