Import-Export Templates - Impex 2.7 and SuperImportExport 1.3

by Drew Bourrut

Published 1998-02-01    Printer-friendly version

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 andSuperImportExportfrom 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.

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.

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.

SuperImportExport ver. 1.3 – BoxSoft - $59

This product is from BoxSoft Development (headed by Mike Hanson). I mention this because I've used some of Mike's other products for years. I've always liked Mike's products so I was interested in seeing how this product compared.

Features

The following features are found in SuperImportExport:

Import

• Comma Delimited ASCII format supported
• Allows the user to specify the picture format for imported data
• Optionally allows user to strip off the header record
• Selective import of fields by user. That is, the user can choose not to import certain fields.
• The user may specify auto assigning of import fields. In this case, field names in the header record must match those of the target.
• Developer may specify auto numbering of imported data. If specified, the template will use the dictionary information to auto-number each new record added to the target file.
• The developer may specify that the template should check the dictionary to determine which fields the user shouldn't see. Individual fields can be overridden on the "Include" tab found on the template for the procedure. Additional fields also may be included.
• The developer may specify automatic exclusion of GROUPs. This tells the template to exclude any GROUP fields that it finds in the file. Individual groups may be overridden on the "Include" tab.

Export

• Comma Delimited ASCII format supported
• Selective export of fields by user. That is, the user can choose which fields to export.
• Selective order of export fields. The user can specify the order in which the fields appear in the exported data file.
• The user may optionally omit the header record.
• The developer may force a specific export file name.

Preparing SuperImportExport

This product comes as a zipped file that I unzipped into a SIMEX directory. I found it contained a "readme" and a setup.exe, so first I ran Setup (typical user… didn't read the readme file). The setup requires a password which, once entered, completes by installing the templates into CW20\SUPER. From prior experience with Mike's templates that was exactly where I expected them to go.

The manual is supplied as a PDF so I kicked in Adobe Acrobat and printed the manual, thereby killing a tree branch. Following the instructions in the manual, I would normally have updated the redirection file to include the super directory. Since I use other "Super" products, it had already been updated. Then I registered the template. I was now ready to add the SuperImportExport template to my app.

Preparing My App

I have an app similar to Consultant's Edge called Service Edge and I decided to add the import-export capabilities there. I opened my app and added the SuperImportExport global extension. The extension has four tabs of possible settings but I chose to use the defaults.

I then opened the procedure properties for my procedure (selectcustomer), and attempted to add a procedure extension template for filecopy, import, and export. Unfortunately, I only found filecopy. I looked further, figured I was stuck, and decided to look at the sample app. After a two-minute look, I realized my problem and it was back to my app. The problem was that I needed to create a new procedure for import and export, as the two were procedure templates.

I created two procedures; one called importcustomer and one called exportcustomer. The process went like this. Click on Procedure new. Choose Super Import template. Then select a file as the receiver of the imported data. Do the same for export. Finally I added two item options to my main frame's maintenance menu option – Export Customer and Import Customer. Each invoked its respective new procedure. Now I was ready to compile.

Compiling my app

Actually, my app is compiling as I write this. I have about 160 procedures so I thought I’d get some typing done while I wait. Zzzzzzz! Uh... Oh... It’s done compiling. Again, this compile worked flawlessly.

Using my app

First, I decided to export my Customer file, so I selected Maintenance / Export Customer from my main frame menu.

Choosing fields to export in SuperImportExport

This brought up a pleasant, easy to use screen where I could select which fields I wanted to export. The export went flawlessly. So now I tried import. With import you can map what input field maps to what field in your data file. The import also worked flawlessly. There is little to say here; the product works very easily.

Mapping import fields to database fields in SuperImportExport

Reactions and Features Most Liked

I like the product. It works cleanly and simply. This sounds familiar but this product was also easy to use. It is a somewhat older product (written for CW1.5 and 2.0) and is limited to export and import of ASCII data only. I particularly liked the fact that the export and import screen "felt" very similar. Also the Picture button on the import screen allows one to deformat the import field. In export, I was particularly interested in the ability of specifying (in the procedure files definition) a series of related files under the primary file to allow a complex export. For example: Customerè Invoiceè LineItemè Inventory Itemè Vendor. Pretty snazzy Mike!

Room for improvement

I liked the product and the price. The manual could be clearer in explaining how to implement each feature. I found that I had to look at the demo app to realize that I needed to add a procedure for import and export. The SuperImportExport product has many options that can be set on the global extension tabs. While looking at them I pressed the help button and found the system looking for IMPEXP.HLP, which is missing from the product. Mike sent it to me, but this oversight needs to be fixed. On export, the user must manually specify each field to be exported. When there are many fields and all are to be exported, an "All" button would be a great help. In addition, supporting formats other than ASCII delimited would take the product to another level.

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.

Category	Impex Score	SuperImportExport Score
Ability to do the task	Very Good	Good
Ease of use	Very Good	Good
Ease of Installation	Good	Very Good
Documentation	Very Good	Good
Technical Support	Excellent	Excellent
Modifies Shipping Templates	No	No
Black-Box DLLs/LIBs	No	No

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.

SuperImportExport 1.3 lists for $59 US and can be purchased from Mitten Software, the distributor for all BoxSoft products. Sales phone number is: 800-825-5461, Fax: 612-593-5028. A demonstration of SuperImportExport is included in the BoxSoft SuperDemo program along with most other Super template products. SuperDemo can be downloaded from www.boxsoftdevelopment.com or from www.mittensoftware.com.

Vendor comments from Mike McLoughlin of Sterling Data (Impex)

My plans for IMPEX include the addition of Fixed Length ASCII, and possibly Excel formats. But first priority is adapting the templates to give C4 compatibility which I hope to finish in February - this upgrade will be available free to anyone with version 2.5 (released Feb 97) or higher. If you are developing for a language other than English I would like to point out that all source code is supplied with IMPEX so translating window text, prompts etc. is quite easy.

 

Vendor comments from Mike Hanson of BoxSoft Development (SuperImportExport)

As was mentioned in the review, SuperImportExport is one of our older products. We have many new features planned, including an improved end-user interface; the ability to save/restore import and export configurations; preview on import with skip record support; flexible control of visible field names; computed virtual fields for export; and of course support for Clarion 4. We are considering adding xBase support, but we are not ready to commit to it at this time. Our first goal is to finish the C4+ABC compatible version, which will be released in 4-6 weeks.

Printer-friendly version

Copyright © 1999-2008 by CoveComm Inc. All Rights Reserved. Reproduction in any form without the express written consent of CoveComm Inc., except as described in the subscription agreement, is prohibited.

Clarion Magazine ISSN 1718-9942