Exporting TTable to an ASCII File

An application that I was writing for my Tennis Club, required the ability to export selected records and fields from a Paradox Table into an ASCII file. There was no requirement for the ASCII file to be in any specific format (ie Comma-Separated Values).

There are several solutions:
Use a TQuery, TBatchMove, and TTable by me.

Use the DBExport Component by LVDI

CD ASCII Import/Export Component by Colin Davies

TDB2ASCII component by Scheff

TQuery, TBatchMove, and TTable

A document titled TI2844 - Using The ASCII Driver With Comma-delimited Files describes how to read an ASCII file from within DELPHI.

However, I had a need to create an ASCII file from a paradox table. Only selected records and selected fields were to be exported. Ideally, I was hoping to produce a comma-separated file as this is the format of file I have used the most.

Selecting the Records to be exported

In most of my applications, I use the TTable component. For this function however, this would require code to filter out the unwanted records. Having good experience using SQL on a mainframe, I decided to use the TQuery component to perform the selection. The TQuery component has an SQL property into which I coded the following:

SQL to filter the Paradox Table

The SQL statement uses local SQL because this is a Paradox table - hence the quotes around the table name. Also, a BDE alias is used for the table name. The example in Appendix C - Using Local SQL of the Database Application Developer's Guide does not enclose the alias/table name in quotes however this is the only way that I could get it to run. The syntax of the SQL is only checked at run-time either when the Prepare or Execute methods of TQuery are executed.
 
 

Specifying the SQL Parameter

The Query requires a parameter of :date. This can be specified either:
Default value of SQL Parameter

at design-time by completing the Params property. (Good when developing the SQL as the date can be specified in dd/mm/ccyy format); or
at run-time by setting the parmeter with the following line of code

MshpQuery.Params[0].AsDateTime := BillingRates.PeriodSDT - 1.0;
where MshpQuery is the name of the component and BillingRates.PeriodSDT is a TDateTime variable. This is the method used in the program.

Selecting the fields to be exported

The TBatchMove component is used to perform the actual copy. The Source property is set to the TQuery object and a destination property to the TTable ASCII file. The Mode property has a value of batCopy which not only creates the ASCII file but also its associated schema file. A schema file defines the format of the ASCII table. Could batAppend be used with an existing SCHEMA file to create a CSV file?
 

TBatchMove component used for copy / export

Note only the columns specified in the Mappings Propery are copied. BLOB fields are not exportable - DELPHI generates an exception. I found this out the hard way.
Mapping of Source and Destination Columns

Running the Application

Having set up the above components, all it takes is the following code to copy the selected columns and rows:

if SaveFileDialog.Execute then
begin
MshpQuery.Close;
MshpQuery.Params[0].AsDateTime := BillingRates.PeriodSDT - 1.0;
MshpQuery.Open;
CSVTable.TableName := SaveFileDialog.FileName;
MshpExport.Execute;
StrPLCopy( ApplicationMessage,
Application.Title,
MessageLength);
StrPCopy(MsgPChar, 'Membership details copied successfully' );
Application.MessageBox( msgPChar ,
ApplicationMessage,
mb_IconInformation + mb_Ok );
end;

DELPHI actually creates the SCHEMA file and overwrites any that you may write yourself, however it only creates a fixed format file - it does not create a Comma-Seperated Value File.

DB Export Component

dbexus16.zipallows exporting to Comma Separated Value files.

CD ASCII Import/Export Component

eximv11.zip

TDB2ASCII component

db2ascii.zip

gra...@pcug.org.au