Setting

Definition

Source Field

Choose a field from the list.  This list is read from the header record in the import file specified in the previous step of the wizard.  Either choose a field to import, or select -Insert Constant- to insert a constant.

Constant

Constant / Parameters

If -Insert Constant- has been chosen for the source field, then enter the constant value in this column.  The value must agree with the destination field data type.  For instance, do not enter an alpha constant that is destined for a numeric field.  When inserting a constant value, you may use General Tags.  When using a General Tag, additional job-specific parameters are passed as variables 1 through 8 which may also be used in the tag.

In addition, you may reference other fields of data within the current import record as follows:

[%<CSV field name>%]

Where <CSV field name> is the name of the field as defined in the header row of the import file.  Everywhere a reference to a field name is specified as part of the constant value, the current import value for that field will be substituted.  This allows building concatenations of several fields to be brought into a single MIE destination field.

For Import Merge jobs, you may specify substitutions that will be performed prior to attempting a match.  Such substitutions will only apply to the *MERGE 1ST/2ND/3RD -> merge transforms.  Substitutions allow you to force both sides of the matching fields (the incoming CSV data against the existing MIE data) to agree with each other prior to attempting to match.  A good example of this is attempting to match hand-collected serial numbers to existing serial numbers.  A common human data entry error is to confuse "Z" as a "2", or "0" as an "O", or "S" as a "5" (and vice versa).  By telling the MIE to substitute all Zs for 2s, all Os for 0s, and all Ss for 5s prior to attempting to match, it normalizes the effects of common data entry errors and increases the probability of getting a match.  Substitutions are only done during matching and do not affect the actual data that is written to, or already existing in, the database.

In the example above, the substitutions would be specified in the Constant/Parameters column as follows:

O=0|Z=2|S=5

The general form is <character to find #1>=<sub #1>| ... |<character to find #N>=<sub #N>

Transform

Transform / Merge

Choose --> (no transformation) or one of the data transformations.  Transform will perform the specific transformation on the data before writing it to the destination field.

For Import Merge jobs, there are three transformations that have special meaning: *MERGE 1ST->, *MERGE 2ND->, and *MERGE 3RD->.

These transforms define the CSV field(s) that will be used to attempt to match the incoming data to an existing MIE record.  The match attempt will happen in order - first the *MERGE 1ST-> definition will be attempted, after having removed the characters to remove and substituting the characters to substitute (if any).  If no match, then the MIE will attempt to match on the *MERGE 2ND-> definition via the same methodology, and then will move on to the *MERGE 3RD-> if *MERGE 2ND-> fails to match.

The incoming-to-existing field crossings defined by the *MERGE... transforms DO NOT ACTUALLY PLACE DATA INTO THE TARGET MIE COLUMNS.  They only define how matching will be attempted, and in which order.  If, upon match, you want to actually forward the incoming column into the MIE target column, specify that field cross as you normally would using another row in the field crossing definition grid.

Destination Field

Choose a field from the schema into which the transformed data should be written.

Business Rule

Choose a business rule to enforce on transformed data before being allowed to be written.  Note that if any column of the imported CSV record fails its defined business rule, the entire CSV record will be ignored.  The job log will contain all instances of where data failed business rules.  If you choose a business rule defined as a drop-down, the MIE will take the transformed value and perform a lookup in the drop-down list.  If the transformed value is not in the drop-down list, the business rule fails and the entire CSV record will be ignored.

Business rules are enforced for *MERGE...-> transform columns as well.

Condition

Whereas a business rule will be applied to the data coming in from the source import file, a condition will be applied to the matching target record.  Conditions are used to, as the name implies, conditionally import data.  Conditions only apply to records that already exist and are about to be updated with incoming data.  Once a target record is identified for update, it is tested against the condition specified for the given column or columns (different conditions may be applied for different incoming columns).  If the condition is met (true), the data for the column will be allowed into the target table.  If the condition is not met, the existing data for the column will be retained (and the new data ignored).

Conditional import behaves differently based on whether the import job is a straight or merged import, and whether the merged import allows mass updates.  If the target table has only one matching row for the current line being processed for the CSV file, then any conditions not met will only apply to the column for which the condition was specified.  All other columns will still be updated by the incoming data.  For example, if the first name column has a condition set and that condition, once tested, yields false, then the incoming data for first name will be ignored.  If, in the same incoming record, email address is a column for which there is no condition specified of for which the specified condition is met, then email address will be allowed in.  Stated simply, when there is only one matching target record, conditions are applied and enforced individually by column.

When the matching target is two or more records, then any column for any record that is not met will cause the entire incoming line of data to be ignored.  No changes will be applied to any record where any column's condition is not met.  Stated simply, when there are two or more matching target records, conditions are applied and enforced on the entire record.