Printer Friendly

Append from: a powerful dbase command.

The best of database management and word processing come together with APPEND FROM.

Hidden deep within the DBASE III+ manual is a description of a deceptively simple-looking command: APPEND FROM. This command is used to add records to a database from an external source, such as another DBASE file, a Lotus worksheet, a PFS file, or even a simple word processing document.

It is this last capability, in combination with DBASE III's text exporting functions, that allows you to combine the best of both the word processing and database management worlds.

A database management program has superior information manipulation capacities. You can keep all of your information in one place, then extract elements as needed. For instance, from a reference book file, you could print a list by call number for inventory purposes or by date of purchase to make a "New Resources" Est.

A word processing program, on the other hand, has superior printing features as well as macro functions that can be programmed to work without operator intervention.

The following discussion uses dBASE III+ and WordPerfect version 4.2, although it is applicable to other versions) to describe the elements necessary to combine information with a database and word processor and then gives two illustrations of their use.

WordPerfect into DBASE

The basic command is APPEND FROM <filename> DELIMITED, where <filename> is the name of the source text file.

The reason APPEND FROM <filename> DELBMED is superior to the simple APPEND FROM is that, for the latter, the source must be a DBASE file, and field names must match. As you will see in the subject listing example below, you may wish to move information from one DBASE file to another, but not necessarily to one with exactly die same field names. One solution is to export the information to a TXT file, then import it into a DBASE file.

APPEND FROM <filenmne> DELIMITED has certain restrictions that must be followed for a successful conversion:

* Each "record" in the TX7 file is

one line ending with a carriage return

and a linefeed, or a "hard

return."

* Each field" in the TXT file is a

string of characters, symbols, and

spaces ending with a comma, and

is said to be "delimited" by that

comma.

* Be sure to CREATE the DBASE

file into which the information is

going with the fields in the same

order as they appear in the TXT

file. The length of each field

should be at least as long as the

number of characters in each

"field" in the TXT file, or a little

longer, when importing.

For example: a WordPerfect list looking like this:

BX1990.J36 ,JerusalemBible [HRt]

BX1990.K89 ,Kohlman's Bible [HRt]

can be imported into a DBASE file with the following structure:

CALLNUMBER Character 20

TITLE Character 30

The major drawback of the APPEND FROM <> DELIMITED command is that it will always skip to the next field at a comma, even a comma such as "Bailey, Mary Ellen." The solution is to use WordPerfect's REPLACE command to replace all commas with some other character. A period is a possibility, but if you regularly use WordPerfect to print your reports, replace the commas with an unusual character such as a caret (A), then replace all carets in the final product with commas.

dBASE to WordPerfect

There are two choices here. using the standard REPORT FORM <filename> TO FILE XXX.TXT, or using the commands SET ALTERNATE TO, SET ALTERNATE ON, and then CLOSE ALTERNATE within a small program.

REPORT FORM is best for files containing fewer than 500 records. The procedure is as follows:

1. Set the margins to the combined total of all fields to be printed, plus room for the commas to be placed between fields.

2. Set the report length to 500, to avoid page break characters being exported as well.

3. Set "Plain page?" to Yes.

4. For the columns of information: set first column contents at the first desired field; the second column contents at '.' (apostrophe, comma, apostrophe); the third column at the second desired field; the fourth at','; and so forth.

For files of more than 500 records, use MODIFY COMMAND to write a small program like the one below:

USE XXX.DBF SET TALK OFF SET ECHO OFF

* These avoid having the commands sent to * your text file along with the information.

SET ALTERNATE TO LIST.TXT

SET ALTERNATE ON * Be sure to both specify your * alternate (destination), * the text file, and also to turn * the option ON.

DO WHILE.NOT. EOF() * "do while.not. eofo". skip", * and "enddo" take the program * through the database file record * by record. ? CALLNUM[BER,',',TITLE * This will output each call number, * followed by a comma, then the title, * followed by a hard carriage return.

SKIP

ENDDO

CLOSE ALTERNATE * Restore normal functions. SET TALK ON SET ECHO ON RETURN

Return to the dot prompt.

Two Output Options

If you are not going to bring the information back into DBASE, the commas are not necessary. Also, consider including special characters that can be manipulated by macros in WordPerfect. For example, put an asterisk *) before and after each trimmed title, then use a macro to underline between pairs of asterisks. ? CALLNUMBER,',*',TITLE,'*'

An if" loop can be used within the main "do while" loop. For example:

IF MEDIUM ='VHS'

? CALLNUMBER,',TITLE ENDIF SKIP

These commands may seem complex. However, the two examples below show how they can make difficult tasks relatively straightforward.

A Subject Heading List

A serials librarian has a DBASE III+ file containing records for all periodicals, both current and inactive. Each record has two fields for subject headings: SUBJECT1 and SUBJECT2. SUBJECT1 is used in all records; SUBJECT2 for only 25 percent of records. The desired product is a listing of all periodicals by subject.

Multiple subject headings, especially when not all records will employ more than one subject heading, have always been a problem to manipulate within DBASE.

It is not possible to simply create the composite database (LIST.DBF) within dBASE, because APPENDING FROM another DBASE III+ file always matches field names. Here, your information would be ignored, as you can't APPEND FROM a field called SUBJECT1 into a field called SUBJECT. When APPENDING FROM a text file, there are no field names to match; simply make sure the fields are in the correct order in the text file.

The steps involved are as follow. (See Example 1 for a listing of the specific commands.)

1. Output a report to a disk file SUB1.TXT) containing =E, SUBJECT1.

2. Output a report to another disk file (SUB2.TXT) containing =E, SUBJECT2.

3. Create a database file (LIST.DBF) with two fields:

TITLE Character 50

SUBJECT Character 30

4. Use LIST.DBF, then APPEND FROM SUBL.TXT and SUB2.TXT, DELIMITED.

5. Delete all records in LIST.DBF where die SUB= is blank.

6. Sort according to subject.

7. Print a report to another disk file <FINAL.TXT>.

8. Apply final formatting within WordPerfect.

9. Print final list. Steps 1 to 7 take approximately thirty minutes on a 10-MHz 80286 AT compatible with a database of 1,200 records.

Finally, another punctuation caution is that DBASE interprets semicolons as carriage returns. Either remove semicolons from your database, or check the word processing TXT file for odd line breaks, such as:

Change

A magazine for youth.

Resurrecting a Dead Database What happens when you accidentally erase a database file on your hard disk? You turn to your back-up copy. What happens when DBASE reports that your back-up copy is, for some unknown reason, not a DBASE file."?

First, panic.

Second, calm down and follow these steps to recreate the database using WordPerfect to manipulate the information into a format readable by the DBASE APPEND FROM command. (See Example 2 for a description of the macros and commands involved.)

1. Make a copy of the damaged .dbf file to work on.

2. Use WordPerfect only in DOS file mode, not normal word processing mode.

3 . Open the .dbf file in WordPerfect.

3.1. Delete the control characters (anything odd) at the top of the file.

3.2. Set margins at the combined total of all fields, so that each record is on one line. Trial-and-error works very well here, since as you make adjustments you will be able to see right away if the fields line up one under another. Delete any control characters appearing within the file and throwing die alignment off.

3.3. Replace the soft returns [SRt] at the end of each line with hard re - n= [HRt).

3.4. Replace all commas with some other character.

3.5. Make sure all the fields line up under each other. This sounds tedious, but goes rather quickly. Place your cursor on the first character in each field and press the down arrow key. Make adjustments when your cursor "jumps" out of the straight down path.

3.6. Place a comma between each field for each record. This can be done with a macro. If you make a mistake, simply delete all commas and try again.

3.7. Save your file, using the DOS text save command.

4. Load DBASE.

4.1. Create a database file with proper fields, making sure that each field is at least as long as that in the .TXT file, and that the fields are in the correct order (and that you haven't forgotten any field).

4.2. APPEND FROM the text file into the new database, and there you have it, a resurrected database.

It took approximately three hours of work to resurrect a file containing seventeen fields and over 350 records. It would be better never to need this procedure, but it was more efficient than recreating the file by hand.

The APPEND FROM command is not for simple applications. However, it is sometimes indispensable when confronted with difficult problems.

Example 1: Commands for Producing a Subject Heading List OURFIS.DBF is a database created from the Faxon FISCAL program with information on all periodicals held by the College of St. Scholastica Library. There are many fields, of which only three are pertinent here: TITLE, SUBJECT1, and SUBJECT2.

Program to output subjects and titles to a TXT file:

use ourfis.dbf

set echo off

set talk off

set console off

set alternate to clean.txt

set alternate on

do while.not. eof()

? title,',',subject1

? title,',',subject2

skip enddo close alternate set echo on set console on return

Program to (1) input titles and subjects to a DBASE file; (2) delete titles without subject headings; (3) create a file indexed by subject heading; and, (4) export a list to WordPerfect in the following subject and indented title format:

SUBJECT

TITLE1

TITLE2

SUBJECT2

TITLE3

erase bysubs.ndx use sublist.dbf delete all pack

* these erase previous versions append from clean.txt delimited delete for subs =' 'all pack

index on subs+title to bysubs.ndx set alternate to subjects.txt set alternate on set console off set echo off set talk off msubject ='AAAAAAAAAAAAAA' do while.not. eof()

if subs <> msubject

?'*'+trim(subs)+'*'

?' ',title

else

?' ',title

endif

msubject = subs

skip enddo close alternate set talk on set echo on set console on return

In WordPerfect, a macro (<Alt>-Underline) underlines an subject headings: <F2> Srch: <Alt><F4> Begin block <F2> STch: * <F8> Underline

Press <Esc>, change n=8" to ".*n= 1200 ", then press <Alt>Underline.

Add a heading at the top of the document. It is also useful to check for inconvenient page breaks. Print. Example 2: Commands for Resurrecting a Dead Database This example occurred with REFBKS.DBF, a database of information on reference books included in the college's Media Generalist (school librarian) program.

Message: Not a DBASE file.

Copy REFBKS.DBF to TRYREF.TXT

Begin WordPerfect. <Control><F5> Text In-Out, retrieve TRYREF.TXT as a DOS document, carriage return/linefeed (CR/LF) becoming a hard return ([HRt]).

Delete any extra and/or strange characters occurring at the beginning of the file.

Change margins to exactly the number of characters in a record (e.g., L=O, R=154). Experiment until the first ten records, at leas% line up one under another.

Change page length to as long as allowed. Page breaks win alter some macros and interfere with importing back into DBASE.

Every ten or twenty records, a line of strange characters will show up. You will see the records displaced to the Tight. Remove these characters.

Place hard returns at the end of each line (or record). Macro: Start with cursor at position 0. Push the down arrow key once, then the left arrow key (to go to the end of the previous line. Press the Enter key. Repeat as needed.

If each line does not start with the first character in the record, create a macro to eliminate this gap: Start with cursor at position 0. Press down arrow key once. Press <Alt><F4> Begin Block. Press <Control><Right Arrow Key> to go one word to the right. Press backspace key. Press Y for delete block.

Make sure each field is lined up correctly. Place your cursor on the first character in the field, then press the down arrow key. As the cursor moves down, scan for characters shifted to right or left, and correct. Move from left to right.

Replace all commas <Alt><F2> confirm N) with some other character-try a period, or a caret ") .

Place commas between each field. Macro: Place cursor key at position 0 on the first line (then Begin Macro Definition <Control><F10>). Using the cursor keys, move over to the start of the second field (second column of data), then with the cursor on the first character (and Insert on) add a comma. Continue over to the next field then repeat until the comma before the last field. Press the down arrow key once, then <Home> <Home> <Left Arrow>. End Macro Definition, <Control><Flo>.

Repeat by pressing the <Esc> key and specifying ten repetitions. Check to see that the commas are correctly placed before doing more repetitions. If an error has occurred, put the cursor after the last correct line and do a Replace <Alt><F2> without confirming, replacing all commas with nothing.

Save document using the DOS Text In/Out command <Control><F5>. Then exit WordPerfect without saving.

Load DBASE.

CREATE a database, REFBOOK.DBF, with fields just a little longer than those in the TXT file.

APPEND FROM TRYREF.TXT DELIMITED.

Check to make sure all the fields transfer correctly. If the last record is correct, all is usually well. If not, reexamine TRYREF.TXT for unaligned columns, missing fields, etc.
COPYRIGHT 1991 Information Today, Inc.
No portion of this article can be reproduced without the express written permission from the copyright holder.
Copyright 1991 Gale, Cengage Learning. All rights reserved.

Article Details
Printer friendly Cite/link Email Feedback
Author:Applegate, Rachel
Publication:Computers in Libraries
Date:Feb 1, 1991
Words:2437
Previous Article:Compact Disk Exchange.
Next Article:ProComm Plus and OCLC's Epic.
Topics:

Terms of use | Privacy policy | Copyright © 2018 Farlex, Inc. | Feedback | For webmasters