HTML Conversion and FTP Automation

Automating the conversion process while empowering users

Lauren Hightower

With the growing popularity of the Internet, information systems (IS) departments are finding themselves in the odd position of maintaining and managing unfamiliar data on their companies' World Wide Web sites. But it doesn't really make sense for IS departments to interpret data developed and maintained by other departments just so it can be translated to a different format for distribution. This is equivalent to having IS print reports for other departments, rather than giving those departments the printer and software to do it themselves. The people responsible for the data should prepare it for any type of distribution--paper or electronic. After all, IS is in the business of managing information, not creating and maintaining it.

The HTML tags used in World Wide Web documents are not designed to be viewed in a WYSIWYG environment. Instead, HTML codes are applied to an ASCII document and interpreted by a browser when it displays the document. This open architecture allows an enormous amount of flexibility and ensures that any browser capable of parsing and interpreting HTML tags can display any Web page that adheres to the HTML standard.

Unfortunately, HTML is nonsense to many people accustomed to working with slick, user-friendly business applications. Until software developers create HTML filters for business software (as they have done for most word processors), IS is faced with maintaining the information or training end users to mark up and update their own HTML documents.

OpenExchange comes with C, Delphi, and Visual Basic header files. I added the Delphi header file to my project and openex to the uses clause of the implementation section of my data in the input file.

The procedure convert_file (available electronically; see "Availability," page 3), uses oe_OpenInputFile to open the input file, oe_ReadDict to read the structure of the file, and oe_GetNCases to determine the number of rows. A call to oe_SelectAllVars returns the number of columns in the spreadsheet or database. Alternately, you could use oe_SetVarSelect to select individual columns or fields. Calling oe_InitReadOnly performs initialization on the input file. OpenExchange has several high-level functions designed to transfer data from one supported type to another, for example from Paradox to Excel. These functions automatically initialize the input file. If you handle the conversion and output yourself, you must call oe_InitReadOnly to force the initialization because the high-level functions are not being used. oe_ReadRecord performs the bulk of the work, reading in an entire record. Once read, the oe_CopyVarDataStr function copies individual columns in the record to string variables. Because I am interested in formatting the text in fixed-width columns, I use oe_GetVarPrintWidth to find the physical width of a database field or the print width of a spreadsheet column. I add three spaces to each width to allow some white space between columns, then pad each field I read with the appropriate number of spaces. To tidy up, I call oe_CloseFiles to close the input file.

Once the output file is complete, the FTP process moves it to the server where it replaces the "live" version of the file. I always encourage users to view the output file as a local HTML file on their PC before they transfer it to the server. Using the URL file:///C|/EXCEL/WORK/ RATES .HTM, users can open a local file in their browser the same way they would any other URL.

The FTP process is handled through calls to WINSOCK.DLL. I used Distinct's TCP/IP SDK and Windows socket to implement my file transfer. The Distinct FTP API documentation provides in-depth instructions for using and calling standard FTP services in the WINSOCK.DLL. Assuming users are physically connected (or have established a SLIP or PPP connection) to the TCP/IP network and are set up to communicate across that connection, they should be able to open an FTP session and transfer a file to any directory on the server to which they have access.

To establish a connection to the server, I call the ftp_open function. Once the connection is made, I call ftp_login to pass the login name and password. The trickiest and most important part of the transfer uses the ftp_put function. ftp_put transfers the file from the local drive to the server. ftp_put requires an additional function, sendfilecontents, to read data from the input file and move bytes of information into a buffer. The sendfilecontents function returns the number of bytes in the buffer while there is still information in the input file, and 0 when it is finished reading the file. The ftp_put uses the results of sendfilecontents to transfer the correct number of bytes from the buffer to the server.

HTML Automator was designed for a very specific purpose. A few enhancements can open the door to a host of other useful possibilities. For starters, in its current design, HTML Automator reads an entire spreadsheet or database. OpenExchange has the capacity to narrow its focus to a particular range of fields or cells. Users accustomed to keeping several sources of data in one spreadsheet can specify particular ranges in different configuration files to create several HTML files from one spreadsheet.

For the most part, my HTML Automator ignores security issues. Our Web server runs on the same box as our FTP server, making it easy to transfer files directly to the appropriate directories on the Web server. If your organization requires a greater degree of security, you might devise a system to place the files in a temporary directory where they can be reviewed before they go "live."

In the current implementation of HTML Automator, there is no way to add text to an HTML file, other than what is stored in the header, data, and footer files. Adding a memo box might help users write their own HTML to include before or after the data, as introductory or summary text.

For aesthetic reasons, you might prefer to write your filter to produce HTML files that incorporate the <TABLE></TABLE> tags. Tables give your page a professional look that doesn't come through with the traditional <PRE></PRE> tags. Unfortunately, tables (defined in HTML 3.0) are a new and evolving standard on the Web. Some browsers don't support them yet and your filter might change as the standard develops.

HTML Automator assumes users are connected physically to a TCP/IP network or already have established a connection through a SLIP or PPP connection. Additional calls to functions in WINSOCK.DLL can check for a TCP/IP connection before the FTP process and initiate the call if it doesn't find one. This is especially useful if a significant number of users are remote.

If your organization maintains several Web servers, it would be prudent to have a list of FTP servers and the corresponding login names and passwords stored so that users can choose a server from a list rather than type in a cryptic IP address or domain name.

IST

OpenExchange DLL 1.0

P.O. Box 3774

Joplin, MO 64803

417-781-3282

<PRE>
<EM>TYPE OF LOAN</EM> <EM>PERCENTAGE</EM>
2 Year Fixed Loan         4.50
3 Year Fixed Loan         5.50
4 Year Fixed Loan         6.00
5 Year Fixed Loan         7.00
6 Year Fixed Loan         8.25
7 Year Fixed Loan         9.00
8 Year Fixed Loan        10.25
9 Year Fixed Loan        10.25
10 Year Fixed Loan       10.75
11 Year Fixed Loan       11.00
12 Year Fixed Loan       11.25
13 Year Fixed Loan       11.50
14 Year Fixed Loan       12.10
15 Year Fixed Loan       12.25
</PRE>

(a)

<HTML>

<HEAD>
<TITLE></TITLE>
</HEAD>
<BODY BGCOLOR="#FFFFFF">
<P>
<CENTER><IMG SRC=logo.gif ALIGN=MIDDLE><CENTER>
<HR>

(b)

<HR>
<CENTER><IMG SRC=banner.gif ALIGN=MIDDLE></CENTER>
</BODY>
</HTML>

Information            Purpose

Input Filename         Name of the file to be converted.
Output Filename        Name of the resulting HTML file.
Type of File           Type of input file that OpenExchange supports.
FTP Server             Name or IP address of the FTP server.
Login Name             Login Name for the user on the FTP server.
Password               Password for the user on the FTP server.
Server Directory       Directory where the FTP module will place the output file.
HTML Header            Name of the HTML file to be used as the header on the output file.
HTML Footer            Name of the HTML file to be used as the footer on the output file HTML.
Title                  Title for the resulting output file.