July 1998

DB->dilemmas

Text Tables II

Also, the article on text tables alone was worth the annual subscription. I'm considering using the TBatchMove object to populate my tables. I'd like to see a related article.

Fred C. Mitchell
Princeton NJ.

Thanks, Fred. I received several responses like yours. Importing text files is a definitely a trouble spot, primarily because the documentation is so weak. I've recently worked with the TBatchMove object and found it to be a superb tool. In this column, I'll show you how to build a utility to import text files using the TBatchMove component. Along the way, I'll discuss an easier way to write schema files.

My motivation

On my first attempt to port the data, I opened the text file as a table and used a TQuery object with a parameterized INSERT query to populate the Paradox table. Much to my surprise, the process took hours. And there were only 10,000 records.

To get the data into a Paradox database, I decided to use the TBatchMove object, hoping it would offer reasonable performance. I was astonished at the results. The entire text table was copied in eight or nine seconds. Initially, I thought the command had failed.

Scheming to save time and effort

struct fld {
  char FieldName[25];
  int FieldLen;
  char type;	//Field type
  };

Listing A: The declaration to set up a field structure

#define CHAR_TYPE	'c' //code value
#define DATE_TYPE   	'd'
#define SHORT_TYPE	'i' //16 
	bit integer
#define CURRENCY_TYPE	'm'
#define PAD_TYPE	'p'
#define LAST_INSTRUX	'z'

fld f[]=
{  //fName   	len	Type
  {"p1",         3,     PAD_TYPE},
  {"InvoiceNo",  6,     CHAR_TYPE},
  {"p2",         1,     PAD_TYPE},
  {"BranchID",   2,     SHORT_TYPE},
  {"p3",         1,     PAD_TYPE},
  {"Description",15,    CHAR_TYPE},
  {"SoldBy",     2,     SHORT_TYPE},
  {"InvoiceCode",1,     CHAR_TYPE},
  {"InvoiceDate",12,    DATE_TYPE},
  {"Reference",  7,     CHAR_TYPE},
  {"InvoiceAmt", 8,     CURRENCY_TYPE},
  {"eof",        0,     LAST_INSTRUX}
};
String TextTableName("Aging");
String DBTableName(TextTableName);

void BuildSchema(fld f[], String &
	TextTableName, const char SCHEMA_
		TYPE, bool Debug);

void BuildTable(fld f[], String &
	TableName, bool Debug);

void CopyTextToTable(String &Text
	TableName, String &DBTableName);

void ViewTable(String NewFile);

Table A: Field-type specification cross-references

Flg	Macro Name	Schema	SQL
'c'	CHAR_TYPE	CHAR	CHAR
'd'	DATE_TYPE	DBIDATE	DATE
't'	TIME_TYPE	TIME	TIME
'D'	DATETIME_TYPE	TIMESTAMP	TIMESTAMP
'm'	CURRENCY_TYPE	FLOAT	MONEY
'f'	FLOAT_TYPE	FLOAT	FLOAT
'i'	SHORT_TYPE	NUMBER	SMALLINT
'l'	LONG_TYPE	LONGINT	INTEGER
'b'	BOOL_TYPE	BOOL	BOOLEAN

I tested the SQL types using Paradox and Access 97. All the types are supported in both environments except BOOLEAN. The MS Access Boolean specifier is LOGICAL and doesn't accept BOOLEAN as a field type in a CREATE TABLE statement.

Using a table similar to the one shown in Table A adds considerable flexibility to your application. Modifications to the program's macros accommodate requirements across multiple databases. Simply changing BOOLEAN to LOGICAL in the source code allows me to create an Access table instead of Paradox.

Double mapping also provides flexibility. For example, the Schema specification doesn't support a currency field type. I wanted to create a database table using currency fields. So, I simply mapped the schema's float type twice--once as float and once as money. When creating the schema file, the program uses FLOAT for both. However, the SQL command uses either FLOAT or MONEY, as specified.

TBatchMove

Listing B: Code required for porting data from a text table to a database table

  String dbTable(DBTableName);
  dbTable +=	DB_FILE_EXT;
  tblPdox->TableName = dbTable;
  tblTxt->TableName = TextTableName;
  dbTxt->Connected = true;
  dbPdox->Connected = true;
  tblTxt->Active = true;
  tblPdox->Active = true;
  BatchMove1->Execute();
  tblPdox->Active = false;
  tblTxt->Active = false;
  dbTxt->Connected = false;
  dbPdox->Connected = false;