I am attempting to export an AREV 2.1 legacy database to dBase for a new Windows application; when I use the native AREV export utility (to export to dBase III files), one db file inevitably gets exported with a corrupted table/index header; the file is readable in dBase utilties (e.g., via DCLIP), but not in my C++ utility (via ODBC); all of the legacy db tables make extensive use of text/memo and multivalued fields;

When defining the export layout be sure to set the memo field length to 10 (the default I believe). Anything else leads to the corruption problem.

Also you may have to try different versions of the ODBC driver. The Foxpro/DBase ODBC driver included in the latest MDAC package is quite good and should work with the ARev export created DBF.

If not, the older versions for Visual FoxPro 3.0 and 5.0 are available on Microsoft's FTP site. You need to download the index in order to find the file names.

I tried changing the memo-field length, but no joy; there are two other significant legacy db files with numerous memo fields, and I exported those with no problem; now, I am not an AREV expert, and I am not certain which files actually hold the data, and which hold the application; there are numerous files named Rev#####.os and Rev#####.lk (with one .os file per .lk file);

Well .. you've got to do some snooping to find out which REVnnnn files contain what you're looking for. Even if you're not an AREV guru, it's pretty straightforward.

You need to start the AREV App and press the F5 key to get to a TCL window.

Next you can look at the Files attached by the App (LIST FILES.

Make a note of the volume location(s) for files that look like reasonable candidates for data. Depending on the version of AREV, type LISTMEDIA or LISTVOL to get the REVnnnnn DOS filenames. This is not particularly useful since there's no easy way to manipulate this data directly from DOS.

You can examine the REV dictionary for these files by typing LISTDICT . This will tell you whether the data fields or symbolic (calculated) fields are Single or Multivalued. For your purposes, you may have to treat multi-valued fields as memo or write an export process that will output the data in "Normalized" format for D-Base type import.

Don Miller

C3 Inc.

In ARev press the function key f5, this should open the TCL window provided the user you are logged in has this enabled.

At the command prompt ":" type

RUNMENU EXPORT

This should bring up the EXPORT TOOLS MENU

Choose "dBase III"

In the "Process Name" field enter the export process you are using or press F2 for the pick list.

This should bring up the following information: name (and path) of the DBF file being created, the logical name of the ARev source file (e.g. RECEIVABLES) and a list of the field names, field type, field length and decimal count.

All the fields with type of "M" should have a field length of 10 and decimal count set to blank.

Press F9 to save your change (if any) and begin the export process. When asked if you wish to overwrite the DBF and DBT files answer "Yes".

NOTE: Dbase III files are limited to 256 columns max. I do not recall the physical size limitations of the DBF and DBT files or the maximum amount of data that can be stored in a memo field. If these maximums are being exceeded this could be source of your problems also.

David,

You might try taking a slight detour in getting your data out of Arev. Try exporting the data to a tab-delimited Ascii file first. The export process seems to work okay for this. Then either do the import from your new application, or use the likes of Excel to work the data around to what you need.

I do a good number of conversions for a living.

Regards,

Eric Drews

http://www.drews-ent.com

Then again it could be the version of the ODBC drivers as I stated previously.

For instance the Microsoft dBase Driver that shipped with Visual FoxPro 3.0b (I don't have the version number handy) chokes on a field descriptor with the name "NO" (abbreviation for number) where as version 4.00.3711.08 works fine on the same DBF, as does the Visual Foxpro ODBC driver v6.00.8428.00.

I'd urge the use of the VFP ODBC driver as it is a universal DBF driver and can figure out what type of DBF it is (dBase III, IV, V, Foxpro 2.x, Visual Foxpro 3.x, 5.x etc) without you having to tell it.

Again the MDAC update kit is available from Microsoft and usually ships with the service packs for their database products and Win NT 4.0.

The next chapter in my AREV follies:

It turns out the corruption occurs because of memo-field lengths–dBase 3 can handle 5K bytes in a memo, and some of the memos in the legacy AREV db are 9K+!

Next approach: the AREV app creates various reports, incorporating these memos; presumably these reports are written to temp files for viewing/printing purposes; is there any way to capture these temp files (other than as hardcopy) for ASCII import purposes?

You either use the ASCII export rather than a dBase III export to a comma or better still a tab delimited file. These can then be imported into just about any other database program that exists.

The other alternative is to use PDISK which routes the printer output to a file. The drawback here is depending on the source of your output you could have printer control characters, page and column headings embedded in your file, although to some extent these can be suppressed , again depending on the source (RList, EasyWriter, Forms, RBasic program).

Basic syntax for PDISK - to route output to file:

PDISK path\filename {(options)}

To return output to printer:

PDISK PRN {(options)}

Options are O=overwrite, S=suppress messages

There are also several utilities out there that can do the job inside and outside of ARev (LH2TXT for instance). If you do not require filtering prior to the export this might do the trick for you. Search on LH2TXT, export, conversion as this topic has been discussed many times previously.

Last night I took the source code for EXPORT.DB3 from the REVSOURCE file of ARev version 2.12 and modified it to create Foxpro v2.5 files.

These can have maximum record sizes of 64K. I also corrected the Y2K bug that exists in versions prior 3.12. I don't have the file specs for FP v2.6 or higher, besides there are a number of binary formats in these that would be difficult to support.

I was able to successfully export memo fields of ~32K and read them in Foxpro v2.5b, Foxpro for Window v2.5b, Visual Foxpro v3.0b, VFP 98, and into Word 97 and Excel 97 via ODBC and file import.

Let me know if you want a copy.

View this thread on the forum...