Data Tools only work for Windows XP. Click Here for a solution

Jul 28

Written by:
7/28/2011 7:53 AM 

Question:

There are records in my source file that I do not want to convert to my target file because they do not meet specific requirements. I know I can filter these records out completely, but can they be written to a different target file in the same map?

Answer:

Using Map Designer in any of the Pervasive DataTools Data Extractors or Data Loaders, you can filter records that match or fail specified criteria and write these records to a reject file. The resulting records in the reject file can be inspected, thus allowing you to determine how to handle those records for further processing, or not.

Objectives

There are two objectives in this sample transformation map, as follows:

• Use the Rapid Flow Integration Language (RIFL) to write an expression in the Account No target field to prevent certain data records from being written to the target data file.
• Use the reject records subsystem to write the rejected records to a reject file where the rejected records can be reviewed manually and/or create a separate transformation map to correct the situation(s) that caused the records to be rejected.

Skill Level

• Basic

Skill Set

• Map Designer
• Basic RIFL Scripting
• Event Handlers and Actions

Design Considerations

This transformation map selects records based on values in a data field in the source file. The field of interest is Account No. The goal is to write to the target only the source records that fall within a specified range of account numbers. We accomplish this goal by writing a simple RIFL expression in the Account No target field. The records from the source file that do not fall within the specified range of account numbers will be written to a ""reject file"".

Procedure

In this transformation map, we reject records that do not meet our simple range-of-account-numbers criteria. In your transformation maps, you may specify more complex rejection criteria. For example, you might want to reject records that have been inactive for a specified period and send the reject records file to your sales or administrative staff for review.

Launch any of the DataTools Data Extractors or Data Loaders. Create a new transformation map and connect to the source and target data files, as follows:

Source Connection

• Source Connector: ASCII (Delimited)
• Source File: \INSTALLDIR\tutor1.asc

Where INSTALLDIR is the installation folder where DataTools is installed on your workstation. The default INSTALLDIR is:

C:\Program Files\Pervasive\DataTools9\Common

• Description: The source is a simple CSV text file containing 100 data records with 10 columns in each record.

Target Connection

• Target Connector: ASCII (Delimited)
• Output Mode: Replace File/Table
• Target File: FOLDER_PATH\trg_tutor1.csv

Where FOLDER_PATH is any drive and directory to which you wish to write the target data file.

• Description: The target is a CSV text file. There is also a second target file where the reject records are written.

Transformation and Map Properties

In the Transformation and Map Properties window you will set up the name and location of the reject file. Here are the steps:

1. In the View menu, select Transformation and Map Properties.
2. In Reject Connect Info, specify the path to a new reject file, as follows:
• In the Reject Type field, enter ASCII (Delimited).
• In the Connect String field, enter the relative path to the new reject file. The connect string is:

File='FOLDER_PATH\rej_reject.txt';

Where FOLDER_PATH is a drive and directory path to which you wish to write the rejected records. Records that are rejected by the transformation map will be written to rej_reject.txt for further administration as required.

3. Click OK to close the Transformation and Map Properties window.

Data Field Mapping

This is where you create the schema of the target data file. The schema of the reject file will, by default, be the same schema as the source data file, but in a CSV text format.

1. On the Map Fields tab, map all fields to the target schema by dragging and dropping the asterisk from the source grid on the left to the topmost empty row in the target grid on the right.
2. Still on the Map Fields tab, click in the Target Field Expression cell for the Account No target field to open the RIFL Expression Builder.
3. Write the following expression on multiple lines in the Expression Builder window for the target Account No field, and click OK when done:



If Fields(""Account No"") >10069 Then

Reject()

Else

Fields(""Account No"")

End If

This expression rejects all records with account numbers greater than 10069.

Event Handlers & Actions

This is where you set up Event Actions that instruct the Map Designer to write records to the target data file and to the reject file. Here are instructions for each.

Write Records to the Target File

Here you will set up one Event Action to write the data records that are NOT rejected to the target file.

1. Click on the Map All button in the button bar to expose the source and target Event Handlers in the two quadrants on the left. The upper quadrant contains source events. The lower quadrant contains target events.
2. In the Source R1 Event Handlers tree (upper left quadrant), click on the AfterEveryRecord Event Handler.
3. In the upper right quadrant, click on the topmost Action cell and select ClearMapPut Record from the Action Name picklist.
4. In the lower portion of the window, click in the Value cell for target name and select Target.
5. Verify that the record layout Value is R1.
6. Click OK to close the window.

Write Rejected Records to the Reject File

Here you will set one target OnReject Event Action to write the rejected records to the reject file.

1. In the Target R1 Event Handlers tree (lower left quadrant), click on the OnReject Event Handler.
2. In the lower right quadrant, click on the topmost Action cell and select ClearMapPutRecord from the Action Name picklist.
3. In the lower portion of the window, click in the Value cell for target name and select Reject.
4. Verify that the record layout Value is R.
5. Click OK to close the window.

Save Your Map

As with any work you do, we recommend that you save your work periodically. So click on the Save Map button in the button bar and save the transformation map. You can accept the default filename, or name it something like: My_Reject_Records_Sample.map.xml

Be sure to retain the .map.xml file extension.

Validate Your Map

To verify that the expressions you entered in the various places in the map are syntactically correct, click on the Validate Map button in the button bar. If the resulting message indicates that the map is valid, proceed to the Run Your Map section. But if the resulting message indicates that the map is not valid, go back to each step above where you entered an expression and correct any mistakes, typographical errors, etc. that would cause the error.

Run Your Map

Finally, you get to run your map, create the target file and the reject file, and see the results of your work. Click on the Run Map button in the button bar. When the map has finished running, proceed to View Your Target Data.

View Your Target Data

If all worked as intended, you just created a target data file. To view the contents of the target data file without having to exit the Data Loader, click on the Target Data Browser button in the button bar. The target file should contain all of the data records from the source where the account number is 10069 or smaller. You can verify that the data is accurate and complete by opening the Source Data Browser and placing the source and target browser windows side by side on your workstation's desktop.

View the Reject File Data

The reject file cannot be viewed through the built-in Data Browser, so you will need to navigate to the drive and directory where the reject file was created and open the file in a text editor, Microsoft Excel, OpenOffice Calc, or the application of your choice that will open a CSV text file. The reject file should contain all of the data records from the source where the account number is higher than 10069.