ACC: How to Use DataPerfect Data in Microsoft Access (100153)

MORE INFORMATION

To use DataPerfect data in Microsoft Access, the information must first be saved from DataPerfect in MS-DOS Delimited Text format. Once this is done, Microsoft Access can import the data using the Text Delimited format.

Problems with Multiple-Line Field Entries

DataPerfect allows you to create multiple-line entries by pressing ENTER inside fields (creating a carriage return character). For example, in the Customer Information panel of the CLIENT1 database supplied with DataPerfect, the Address and Comment fields in some records contain two- line entries.

NOTE: In DataPerfect, a "panel" is equivalent to a Microsoft Access table.

DataPerfect's MS-DOS Delimited Text files use a carriage return to mark the end of each record, so carriage returns cannot be included in field data. However, DataPerfect does not account for this condition when it saves the data in MS-DOS Delimited Text format. When carriage returns are included in field data, DataPerfect generates a file that cannot be successfully imported into Microsoft Access. You must remove the carriage returns before importing the data into Microsoft Access.

The following instructions explain how to:

• Export the Customer Information panel supplied with DataPerfect.
• Use Microsoft WordPad, the word processor supplied with Microsoft Windows 95 (or Microsoft Write for earlier versions of Windows), to delete the extra carriage returns.
• Import the resulting data into Microsoft Access.

How to Export the Panel from DataPerfect

1. Start DataPerfect.
2. From the list of databases, select CLIENT1, and press ENTER.
3. From the list of panels, select Customer Information, and press ENTER.
4. Press SHIFT+F7 to display the report list. Select Built-In Short Reports from the list, and then press ENTER to access the Built-In Report/Export menu.
5. Press 2 to select Disk File On/Off, and then press 1 to select Create File. Type "CUST.TXT" (without the quotation marks), and press ENTER.
6. Press 8 to select Report/Export Format options. Press 6 to select Export DOS Delimited Text.
7. Press ENTER to accept the pipe symbol (|) as the Field Delimiter. Press SPACEBAR to overwrite the tilde (~) character as the Record Delimiter, leaving the <CR><LF> in place.
8. Press SHIFT+F7 to begin the export, and then press any key to return to the list of reports.
9. Press F1, then F7 twice, and then 0 to exit DataPerfect.

How to Remove the Extra Carriage Returns

1. Start Microsoft WordPad (or Microsoft Write in Microsoft Windows 3.x).
2. On the File menu, click Open.
3. If you are not already there, you may first have to select your DataPerfect folder, which is usually called DATA. Then, type "*.TXT" (without the quotation marks) at the File Name box and press ENTER to display a list of .TXT files. From the list of files, click on CUST.TXT, and then click Open. (if you are working with Microsoft Write, select No Conversion when you are asked to convert the text file).
4. The document will open on the screen with each field separated by a pipe symbol and each record ended with a carriage return. Lengthy records will wrap to the next line.
5. Lines that begin with the following text have an extra carriage return that incorrectly continues the record to the next line:
   

         1|Burnett
         26|Corrales, Jr.
         7|Frame
         19|Rhoton
         22|Tolman
   						

   Move to the end of each of these lines, and press DELETE to make the line flush with the line above it.
6. The second line of the records that begin with the following text have extra carriage returns that incorrectly mark the end of the record:
   

         10|Grayson
         21|Sergeant
   						

   Move to the end of the second line of these records and remove the extra carriage return by pressing DELETE.
7. On the File menu, click Save.
8. On the File menu, click Exit.

How to Import the DataPerfect Data into Microsoft Access 7.0 or 97

1. Open the Microsoft Access database into which you want to import the data.
2. On the File menu, click Get External Data, and then click Import. This will open the Import box. Change to the folder where the text file is located and double-click it. This will start the Text Import Wizard.
3. Click Delimited to select Text Delimited Format, and then click Next.
4. Click Other and type the pipe symbol (|) as the Delimiter that separates your fields. Leave blank the First Row Contains Field Names check box and leave "{none}" for the Text Qualifier. Then click Next.
5. If you do not have any table ready to receive the data, you want to click the In A New table button. Otherwise, specify the table name in the combo box. Then click Next.
6. At this time you will be presented with a screen where you need to specify the data type and indexing choice, and the name for each field.
7. Select the field for the primary key, and then click Next.
8. Specify the table name or Click Finish.

How to Import the DataPerfect Data into Microsoft Access version 1.x or 2.0

1. Open the Microsoft Access database into which you want to import the data.
2. From the File menu, choose Import.
3. In the Data Source list, select Text (Delimited).
4. From the file list, choose CUST.TXT (you may first have to select your DataPerfect directory, which is usually called DATA), and then choose Import.
5. In the Import Text Options box, choose Options.
6. Delete the double quotation mark from the Text Delimiter box.
7. Type the pipe symbol (|) in the Field Separator box.
8. Choose OK to begin the import.

Microsoft Access will report any errors discovered during the import. You can examine the data in the resulting table and check to see that the proper data was imported into the proper columns. If you find data from one record spilling into another record, check for an extra carriage return in that record in the export text file and then import the data into Microsoft Access again.