Please Note: this is a review of IMPEX 2.7 and the latest version (5.1) has many more features.

Import-Export Templates

by Drew Bourrut

(Article from Feb 1998 issue of Clarion Online)

The purpose of import/export templates is to make it relatively easy for the programmer to provide users with a way to get data out of their programs and to easily import data from other sources. In theory, it is possible to bring into a Clarion program data that is in any supported file driver format (Foxpro, dBase 3&4, Btrieve, etc), but in practice there are only a few widely supported formats which most commercial programs use to exchange data. The most common formats are:

• ASCII delimited – also know as Basic or CSV (comma separated values), this is pure text with fields separated by commas and, possibly, quotes around the data items. ASCII delimited data can also have a header record that names the fields found in the following records. For example
  "PersonName","Street","City","State","Zipcode"
  "Bob Jones","122 Main St.","East FiddleFaddle","NY","11223"
  "Carey Q. Inseam","17 West Charles St","Smithtown","NY","11234"
• dBase 3 – This is a structured file format used by many programs. This format also allows for memo fields.

For this review, I looked at two products; Impex from Mike McLoughlin of Sterling Data and Super ImportExport from Mike Hanson of Boxsoft. I understand that both products are being upgraded for Clarion 4 but I reviewed them in their CW 2.003 release.

I’ll begin by looking at Impex, then I’ll discuss Super Import/Export. Finally, I’ll compare the two.

Impex ver. 2.7 – Sterling Data - $149

Features

The following are the features of Impex as described in the manual.

Import:

• dBASE and Comma Delimited ASCII formats supported. (dBASE key/index files are not used, just the .DBF and .DBT data files, thus avoiding problems with non-standard formats.)
• Duplicate checking can be enabled at both the developer and end-user levels. The user has the option to process or ignore duplicates and save them to a text file. At the end of the import run the Duplicates text file can be viewed/printed.
• Fields can be excluded by the developer from appearing in the user's browse. So internal flag fields etc. which the user can't import to will not appear and cause confusion.
• File information such as field names, types, sizes is read straight from the DBF header record and displayed in a browse.
• The user can then match up fields by a Map by Name button (for similar labels) and then drag and drop. These Map Structures can be saved for future use.
• Records are added to your existing data file and if there is an Auto Number key the autonumber field will be incremented automatically.
• Checks can be made by means of an EMBED point just before a record is added. For example, you could check if a duplicate already exists or if a required field is blank (such as Company Name).
• For ASCII files there is the option to skip the header record (useful if this record is just field names) and allow double quote marks, or not.

Export:

• The user can define which fields are included in the export file. So, for example, the user could exclude confidential data fields from being exported. These Selections can be saved for future use.
• Seamless integration with Toolcraft's QueryWizard for filtering which records are exported.
• For ASCII files there are options for header record, double quotes and date format. Date fields are automatically detected by the templates.
• IMPEX links up with 3rd party query packages by means of an EMBED which allows testing against a tagfile such as produced by SuperQBE.
• The developer can specify in what key order records are exported and which fields are displayed to the user.
• A new field can be added at export time so, for example, a field containing First Name + Middle Name + Last Name could be composed and added to the export file.
• dBASE only allows a max field label length of 10 characters - the templates allow the developer to specify an alternate field name when exporting to dBASE.

Preparing Impex

This product comes as a zipped file that I unzipped into an IMPEX directory. I found it contained a tpl and tpw template file, a demo app, and a manual in RTF format. First I printed the manual and followed the instructions for install. I then copied the template files IMPEX.TPL & IMPEX.TPW to the CW20\TEMPLATE directory.

Preparing my App

Since I was going to try this product by incorporating it into my Consultant’s Edge product, I copied to my app’s directory IMPEXD.TPS, all the *.ICO files, IMPEX.TXA and IMPEX.TXD.

Impex allows you to specify how an export or import is to be done and to save this definition for future use. These "saved layouts" are found in the IMPEXD.TPS file. IMPEX.TXA is a text file of a sample app. It has specific procedures that are used by the templates. IMPEX.TXD is a text file containing the dictionary definitions used by the templates and the IMPEX.TXA procedures.

First, I started Clarion and registered the Impex template.

I then opened my dictionary (BT.DCT) and using File/Import Text imported IMPEX.TXD. Closing and saving my dictionary, I now opened my app (CE.APP). Still following the IMPEX directions, I added the GlobalsImport template to my app’s Global Extensions. Here I got a pleasant surprise. Apparently Impex wants full control of involved files during import, so there is a checkbox that will cause a message window to appear warning the user that other users on the network should not be using the app during import. Nice touch!

Next, I used File/Import Text to import IMPEX.TXA. This brought in a series of IMPEX related procedures. These were:

• IMPORTER – Import options form for ASCII files
• IMPORTER2 – Import options form for dBase 3 files
• PROCIMP – Import procedure for ASCII files
• PROCIMP2 – Import procedure for dBase 3 files
• EXPFORM1 – The export options form used for both dBase 3 and ASCII files
• EXPORT1 – The export procedure for dBase 3 files
• EXPORT2 – The export procedure for ASCII files

On two of the procedures (IMPORTER and IMPORTER2) I then added my data file’s name to the file schematic under Other Files, and to a fill-in labeled File to import to. There were now just a few steps left before I was done.

Five procedures (PROCIMP, PROCIMP2, EXPFORM1, EXPORT1, EXPORT2) require that you add your file to the File schematic as a PRIMARY file. Then I had to make sure that: dBase3, Basic, ASCII, TopSpeed, and DOS were included in the Project Database Drivers Library section.. Finally, I either needed to add to my main frame menu or to the toolbar the following procedures: IMPORTER, IMPORTER2, EXPFORM1. For this review I added them to the menu.

I was now ready to compile my app.

Compiling my app

While I expected this to be a longer section, it’s not. The app was ready. I pressed the blue cloud and got no errors. That was a pleasure. So now, on to using my app.

Using my app

To test my app, I chose to export my timetrak file as an ASCII file. I selected the export screen and found that it was pre-selected for export as EXPORT.BAS. Seemed good to me, so I let it do its thing and – zowie! The data was exported. Then I tried importing the file and again it went flawlessly.

Figure 1: Impex Data Import Options for ASCII Files

Now, properly encouraged, I chose the DBF format and noted that the file name didn't change to EXPORT.DBF so I manually changed it. No big deal. Then I attempted my export and – Blam! I got an error that seemed to be coming from the CW dBase driver. It seemed to indicate that it couldn't create the file. I tried a few times with no success. So it was time to check out IMPEX support.

I sent a message via CompuServe to Mike McLoughlin and received a response within a couple hours (terrific!). He told me to check my file definition to see that no fields were defined with names more than 10 characters, as dBase doesn't like them. I checked – and found a shocking truth. "DESCRIPTION" is more than 10 characters. I changed my dictionary definition for the TIMETRAK DESCRIPTION field (of type memo) to DESCRIPTN, recompiled and tried again to export. It went flawlessly.

Figure 2: Impex Export Options

Reactions and Features Most Liked

I like the product. It works cleanly and simply. Other than one problem that was mine, it works as advertised. The template handles duplicate checking if you check of a feature, and there is also an embed point called "Changes to fields just before ADD()" that allow fields to be modified before adding. In addition, during the review Mike released a minor interim release. It’s at his web site (www.sterlingdata.com).

The fact that this product allows for linkage to dBase files is a very important feature. This means support for memo fields. Yes, you can export or import memos as well as more standard data. This also means that you have the ability to directly connect with dBase-based programs through the data files. This is not insignificant. I particularly like that, on export, the user can choose what fields to export and what to call the exported file. On import, I liked the drag and drop implementation that allowed me to say what inputting field should be associated with what field in my data file. Good job Mike!

Room for improvement

The Impex manual clearly states that dBase only allows a max field label of 10 characters and the template allows the developer to specify an alternate field name when exporting to dBase. However, since it is a known limit, it would be nice if the program trapped this error and forced a name change, should the developer happen to overlook the issue.

It would be nice to have Impex use a real install program. The directions are clear but it takes a bit to get prepared. Also, if dBase format can be provided it would seem that one should be able to import or export to any supported driver.

Impex - SuperImportExport Head to Head – The Ratings

Both products will do quite fine if your goal is to easily provide the user with a simple, clear way of exporting or importing. Hanson's "Super" product is less expensive but older and less powerful. McLoughlin's Impex is a little more expensive but has the nice addition of handling dBase files. This is particularly nice as many commercial programs use dBase file structures. Additionally, because Impex supports dBase 3 file structure, it can handle memo export. You should keep in mind, though, if your app is using a file format that supports multiple memos (like TopSpeed) that dBase 3 only supports one memo per file so you can only export one. For the future, it would be nice to see other formats supported.

Another area of interest is how dates are handled. As you know, Clarion calculates dates as the number of days since December 28^th, 1800. (You did know that, didn't you?) Other products use similar schemes. So, let's see what happens when we export a date. Suppose we are exporting a field containing birth dates. Suppose a person was born on August 14^th, 1986. We would find that Clarion stores the value 67,799. Let's export it. Here's what happens depending on the product.

Using Impex to export dates

• Export to dBase3 format – the date is stored in the standard dBase format as 19860814. That is August 14^th, 1986.
• Export to ASCII delimited - You may specify one of four formats: mm/dd/yy, dd/mm/yy, mm/dd/yyyy, dd/mm/yyyy.

Using Super ImportExport to export dates

This product uses a different approach. You must specify the picture to use for output. If you specify nothing, you will get the number 67,799. But you can specify any valid Clarion picture, which means you could output 08/14/86 as well as August 14, 1986. This is a nice touch, but it requires that the user understand pictures. You'd better plan on adding some help information.

Both products provide manuals with easy to follow instructions for use. I'd give the manual edge to Impex for ease of understanding, and to SuperImportExport for level of detail.

Product Details

Impex 2.7 lists for $149 US and can be purchased directly from Sterling Data via their web site (www.sterlingdata.com). More information on Impex and a downloadable demo are available at http://www.sterlingdata.com.