ASCII Made Easy

Problem/Question/Abstract:

Manipulating ASCII Files with the Table Component

Answer:

ASCII files, the veritable antitheses of database server-based data, are nonetheless important files for receiving data from a wide range of sources, as well as an effective medium for sharing data with others. This installation of "DBNavigator" takes a look at several mechanisms that Delphi provides for reading and writing ASCII files using the TTable class.

Using tables to read and write ASCII files is not the only solution provided for by Delphi. For example, you can use typed or untyped files and perform basic file input/output (I/O) using functions and procedures of the System unit, e.g. AssignFile, ReadLn, WriteBuffer, etc. An advantage to this approach is that applications created using only these techniques do not require the Borland Database Engine (BDE). However, for the database developer whose applications must use the BDE already, the TTable class provides a number of handy and relatively easy to use methods for using and creating ASCII data.

Overview of ASCII Files

ASCII, which stands for the American Standard Code for Information Interchange, uses 8-bit integers to represent the characters and control codes commonly encountered in data. Each ASCII character or control code has a decimal equivalent. For example, the decimal value 65 represents an uppercase A, 97 represents a lowercase a, and 10 represents a line feed.

An ASCII file is a file that contains only ASCII characters, and is normally considered to hold only text. What makes ASCII files so interesting is that they can be created from almost any source, from "big iron" mainframes to the very earliest personal computers. This makes them a convenient format for importing data from another source, and well as exporting data to be used by some non-database program, such as a spreadsheet or word processor.

In this article, I will limit my discussion to three types of ASCII files: delimited, fixed-length, and simple text. Delimited ASCII files contain two or more data fields. A single character, called a separator, separates these fields. The most common character used for this purpose is the comma. The string data within a delimited file is identified by being preceded and followed by a delimiting character (or delimiter), most often the double quotation mark. The following is an example of what a delimited file may look like:

"Plumber","Mark",1000,5.2,3/4/95
"Ramerez","Pablo",1050,16.75,8/15/97
"Johannson","Christina",998,-25.25,9/1/98

Fixed-length files don't use separators or delimiters. Instead, the data fields are defined by their position within a record. While the records in a delimited file are of a variable length, in a fixed-length file each record is the same length. The following is an example of a fixed-length file:

Plumber        Mark 1000 5.23/4/95
Ramerez        Pablo 1050 16.758/15/97
Johannson      Christina   998 -25.259/1/98

Simple text files do not have individual fields, but are instead composed of a sequence of characters. An HTML file is a good example. The following is an example of a portion of a simple text file:

***********************************************************
                DELPHI 4 RELEASE NOTES
***********************************************************

This file contains last-minute information about Delphi
4 and additional information that enhances the
usability of Delphi. We recommend you read this entire
file before using Delphi 4.

Simple text files are the easiest to work with, and therefore are discussed first.

Reading Simple Text Files

To read a simple text file using a Table component, set the Table's TableName property to the name of the text file and open the Table. The TableName property can either include the fully qualified path, or you can enter the path in the DatabaseName property and only the file name in the TableName property. Figure 1 shows the Table and DBGrid page of the example TEXTFILE project with Delphi's README.TXT file loaded into a Table. (The projects discussed in this article are available for download; see the end of this article for details.)


Figure 1: Any text file can be opened using a table and displayed in a DBGrid.

While using a Table component to access a text file is simple, it's generally used only when your code is going to work with the text in the text file line-by-line. As you can see in Figure 1, the DBGrid, although capable of displaying the text in the Table as a single field, doesn't provide a view of the data suitable for an end user. If you merely want to display text from a simple text file, a Memo component (or other control that encapsulates TStrings) is better. This can be seen in Figure 2, which shows a Memo component from the Memo page of the TEXTFILE project.


Figure 2: A memo is often the best control for displaying a simple text file to your users.

All the code associated with the TEXTFILE project can be found in the OnClick event handler for the Select Text File to View button:

procedure TForm1.Button1Click(Sender: TObject);
begin
  if OpenDialog1.Execute then
  begin
    Memo1.Lines.Clear;
    Memo1.Lines.LoadFromFile(OpenDialog1.Filename);
    Table1.Close;
    Table1.Tablename := OpenDialog1.Filename;
    Table1.Open;
  end;
end;

The Table and Memo components in the TEXTFILE project are configured to be read-only. It's possible, however, to make changes to the data in a simple text file using a Table or a TStrings object. Changes made to a Table are posted on a line-by-line basis (unless CachedUpdates is used), while changes made to the text in a TStrings must be saved by calling the SaveToFile method.

Using Fixed-length ASCII Files

The use of a fixed-length ASCII file requires a schema file. A schema file contains a description of your ASCII file's metadata, including field names, field types, and field sizes. The following is an example:

[FIXED]
Filetype=Fixed
CharSet=ASCII
Field1=LastName,Char,20,00,00
Field2=FirstName,Char,20,00,20
Field3=IDNumber,LongInt,11,00,40
Field4=SomeReal,Float,20,02,51
Field5=SomeDate,Date,11,00,71

You will immediately recognize this file as being in the same format as an INI file. The file begins with the name of the file that it describes. Delphi assumes the file extension of this file is .TXT. Furthermore, the schema file must use the same file name as your ASCII file, but have the file extension .SCH.

On the line following the ASCII file name is the entry FileType=, which is followed by the type of ASCII file. This type can either be the value Fixed or Varying (the value is not case-sensitive). Next, you must identify the character set. In the US this value is almost always ASCII.

The remainder of the schema file contains descriptions of each of the fields in the ASCII file. Each field is described on a separate line that begins FieldN=, where N is the ordinal position of the field in the table structure. Consequently, the first field is defined by a line that begins Field1, the second field by a line that begins Field2, and so forth.

The definition of each field includes five parts: the field name, the field type, the maximum size of the field, the number of decimal places (this applies only to floating point value fields), and the column in which the field begins. The acceptable field type values are shown in Figure 3.

Field Type
Use for
Char
Strings
Float
64-bit floating point numbers
Number
16-bit integer
Bool
Boolean values
LongInt
32-bit long integers
Date
Date fields
Time
Time fields
TimeStamp
Date + Time fields

Figure 3: The values for the field type part of the field definition.

In the preceding schema file example, the first field is declared to have the name LastName, be a string field, contain a maximum of 20 characters, include a meaningless 00 in the decimal part, and finally be found with 0 columns offset from the start of the record. The fourth field, by comparison, is declared to have the name SomeReal, be a floating point number, have a maximum of 20 characters in its value, include 2 decimal places in its display, and whose value can be found starting at column 52 (offset 51 characters from the beginning of the record).

You can create your fixed-length schema file manually, or you can have Delphi generate it for you. Creating a schema file manually involves entering the properly formatted file definition using any text editor, such as Notepad or WordPad. Just make sure you save the file as a text file using the same name as your data file, but with the extension .SCH.

To have Delphi create your fixed-length schema file, you use BatchMove (either a BatchMove component, or the BatchMove method of the TTable class.). This requires that you already have a BDE-supported file type that contains the data from which you want to create a fixed-length ASCII file. If such a file does not already exist, you can create one using the Database Desktop application that ships with Delphi. From the Database Desktop select File | New | Table, select Paradox (or any other file type you are familiar with), and then enter the structure of your table in the Create dialog box, as shown in Figure 4.


Figure 4: The Create dialog box in the Database Desktop.

Once you have a table with the desired structure, use the following steps to create your schema file:

In Delphi, create a new application.
Place two Table components on your form.
Using the DatabaseName and TableName properties of Table1, select the data file that contains the structure from which you want to create a schema file. For example, set DatabaseName to DBDEMOS and TableName to CUSTOMER.DB.
Select Table2, and set its TableName property to the fully-qualified file name of the ASCII file for which you want to create a schema file. Include the entire file path as well as the .TXT file extension. For example, set TableName to C:\Program Files\Borland\Delphi4\Projects\CUSTOMER.TXT. Also set the TableType property of Table2 to ttASCII.
Now place a Button component on your main form, and double-click it to create an OnClick event handler. Add one statement to the event handler created by Delphi:

procedure TForm1.Button1Click(Sender: TObject);
begin
  Table2.BatchMove(Table1, batCopy);
end;

Run your application and click the button.

That's it; you've created a schema file. If you perform the preceding steps using the CUSTOMER.DB table in the DBDEMOS database, you'll find the following schema file in the directory you entered for your TableName:

[CUSTOMER]
Filetype=Fixed
CharSet=ascii
Field1=CustNo,Float,20,02,00
Field2=Company,Char,30,00,20
Field3=Addr1,Char,30,00,50
Field4=Addr2,Char,30,00,80
Field5=City,Char,15,00,110
Field6=State,Char,20,00,125
Field7=Zip,Char,10,00,145
Field8=Country,Char,20,00,155
Field9=Phone,Char,15,00,175
Field10=FAX,Char,15,00,190
Field11=TaxRate,Float,20,02,205
Field12=Contact,Char,20,00,225
Field13=LastInvoiceDate,TimeStamp,30,00,245

Unlike simple text files, which cannot be edited very easily in data-aware controls such as the DBGrid, fixed-length ASCII files can easily be used in any data-aware control. The only limitation is that the files have no indexes, and therefore cannot be sorted. All records you add are appended to the end of the file. Also, because there are no indexes, you cannot define record uniqueness based on a unique key. Finally, these files cannot be shared because they have no native locking mechanism.

Using Delimited ASCII Files

Delimited ASCII files are only slightly more difficult to use than fixed-length ASCII files, in part because Delphi will not generate a schema file for you. Instead, you must write the schema file for a delimited ASCII file yourself.

There are only three differences between schema files created for delimited ASCII files and those you use for fixed-length files. The first, and most obvious, is the FileType entry. While you set this entry to Fixed for a fixed-length file, you set it to Varying for a delimited file.

The other two differences involve defining the field separator and the string delimiter. The schema file for a delimited ASCII file contains two additional lines immediately following the FileType= entry. The first of these is the Separator= entry. You use this to define the character used to separate the fields. As mentioned earlier, this character is often the comma. The second entry is Delimiter=, which you use to define the character used to enclose strings. In most cases this will be the double quote character. The following is an example of a schema file for a delimited ASCII file:

[DELIMIT]
FileType=Varying
Separator=,
Delimiter="
CharSet=ascii
Field1=LastName,Char,20,00,00
Field2=FirstName,Char,20,00,20
Field3=IDNumber,LongInt,11,00,40
Field4=SomeReal,Float,11,02,51
Field5=SomeDate,Date,11,00,62

While Delphi won't generate a delimited schema file for you, the similarity between the two schema file types provides you with some assistance. Specifically, using the steps given earlier you can create a fixed-length schema file for your delimited table structure, and then make the three modifications just described to the Delphi-generated schema file.

The use of both a fixed-length ASCII file and a delimited ASCII file is demonstrated in the SCHEMA project, shown in Figure 5.


Figure 5: The SCHEMA project main form.

When you view the FIXED.TXT table, the FIXED.SCH schema file permits Delphi to read the following ASCII file:

Plumber        Mark 1000       5.203/4/1995
Ramerez        Pablo 1050 16.758/15/1997
Johannson      Christina   998 -25.259/1/1998

When you view the DELIMIT.TXT ASCII file, the DELIMIT.SCH schema file permits you to view this file:

"Plumber","Mark",1000,5.2,3/4/95
"Ramerez","Pablo",1050,16.75,8/15/97
"Johannson","Christina",998,-25.25,9/1/98

Final Notes

By default, the BDE is configured to display two-digit years in dates. So, if you write a date field to an ASCII file, only the last two digits of the year are stored. In most cases, you will want to make sure that Delphi writes all four-year digits of your date data. To do this you must update the Date format setting using the BDE Administrator located in your system's Control Panel (see Figure 6).


Figure 6: Setting the Date format in the BDE Administrator.

Also, to display the four-digit year in a date field associated with an ASCII file, you must instantiate the TFields associated with your Table component, and set the DisplayFormat property of your Date fields. For example, setting the DisplayFormat of a TDateField object to "m/d/yyyy" causes all four digits of the year to be displayed.

Another peculiarity of using ASCII tables is that (at least with my copy of Delphi 4) the Table's Active property must be set to False at application startup. While you may want to set the Active property to True during design time - so that you can see your data as you work - you should set Active to False before running your application. Use the OnCreate event handler, or some other similar event handler, to set the Active property to True for your tables that use ASCII data. This code may look something like the following:

procedure TForm1.FormCreate(Sender: TObject);
begin
  Table1.Open;
end;

Conclusion

ASCII files, while not used in day-to-day database applications, provide an effective medium for passing data to and from Delphi and other applications. Not only can a Table component be used to read simple text files, but with the addition of a schema file, the Table component can read, display, and write delimited or fixed-length ASCII files.


Component Download: ASCII_Made_Easy.zip