Data Maps

Data Maps can be defined as either a reusable asset, or as a one time-only asset that gets built "on the fly" when defining a Manual Import.

To create a new reusable Data Map:

1. Above the list of existing Data Maps, click + New button.

2. A "New Data Map" pop-up window is displayed. In the "Name" field, enter a name for the new Data Map.

3. From the "Data Source" drop-down menu, select the Source Table. This table represents the main table into which data will be loaded; a Data Map can also optionally load data into tables that are joined to this Source Table.

Note: You can never modify this Source Table after the Data Map is created.

4. Click Create. The Workspace is refreshed to show a blank Data Map details screen, where you can configure the details of the Data Map.

5. Optionally, you can assign one or more tags to your Data Map. To assign a tag, click on the "Add tag" field in the Edit section of the Tool Ribbon. The system displays a pop-up menu of all the existing tags. You can select one of these tags, or type in a new one and press Enter. You can repeat this process to add more tags. To remove a tag, click the "X" icon next to the tag label.  

6. If the import file will include a header row, place a check mark next to "File Heading."

7. From the "File Delimiter" drop-down menu, select the delimiter character -- Tab, Comma, Semi-Colon, Pipe, or Caret. Or, if the file is fixed width, select "Fixed Width."

8. Optionally, from the "Text Qualifier" drop-down menu, select a text qualifier -- either Single Quote or Double Quote.

9. From the Data Processing drop-down menu, select one of the following options that controls how the system handles the import records:

• Update or Create: Select this option if you want the import process to create new records, and update existing records.

• Update Only: Select this option if you want the import process to only update existing records (new records are ignored).

• Create Only: Select this option if you want the import process to only create new records (updates to existing records are ignored).

Note: If you selected a Load and Send table above in step 3, the Data Processing drop-down menu is disabled, with "Create Only" selected. For a Load and Send Data Map, this is the only valid option.

10. The "Map to" drop-down menu contains all of the fields in the Source Table selected above in step 3. From this menu, select the first field in the import file, then click the add button (plus-sign icon). If you later need to remove a field, click the remove button ("X" icon) to the right of the field name, then click Edit > Save in the Tool Ribbon to commit the changes. Repeat this step as needed to add more fields to the Data Map.

Note: If you selected "Fixed Width" from the "File Delimiter" drop-down menu, the system adds "Start" and "Length" text fields so you can enter the starting and ending positions of each field in the import file.

As you add more fields, the system automatically increments the "Col #" value by one, but you can optionally enter a different value here, if needed. If you need to ignore a column in the import file, simply skip over that column number in the sequence. For example, if you want to ignore column #4 in your import file, the numbering sequence in your Data Map would go from Column #3 to Column #5, and leave out column #4. The important point to keep in mind is that the sequence you're defining here represents the column order from the input file, and the mapped field you select is the location in the database where that data is being inserted.

You can also use negative values as the "Col #" in order to negatively map fields. When you negatively map a field, you're telling the system that you want to populate the specified "Map to" field in the database, but that you don't expect this value to be present in the import file. Instead, the value is going to come from some place other than the import file, such as a SQL script, for example.

The Data Map must include the field (or fields) that make up the Unique Identifier for the Source Table, even if you're not actually intending to import the Unique Identifier field. The user interface performs a validation on the Data Map  that requires the Unique ID field (or fields) be present in the Data Map. If you're not actually providing the Unique ID value in the import file, you'll need to use the Soft Match feature (described below). In this situation, select the Unique ID field from the "Map to" drop-down menu, and enter a negative number for the column number. In this manner, the Unique ID is present in the Data Map (allowing you get past the screen validation), but you don't actually have to provide any values for it, and it won't interfere with the columns that you are importing.

11. Optionally, to utilize Advanced Options for a field, click the down-arrow button next to that field. The "Advanced Options" section is displayed. Place check marks next to the desired options you want to use for this field.

12. If the Source Table contains Calculated Fields, the system displays an additional "Run Calculated Fields" drop-down menu. Optionally, select a Calculated Field. When the system processes an import file using this Data Map, the system will derive and populate this Calculated Field for each record in the import file. Repeat this step as needed to derive additional Calculated Fields.

Note: If you run a Calculated Field as part of an import process, the system derives and populates that field only for the records in the import file. Conversely, if you set up a Calculated Field schedule, the system derives and populates the field for every record in the table (see Tables for more details).

Note: If you selected a Load and Send table above in step 3, the Calculated Fields option is not available.

13. If the Source Table includes Joins to other tables, each Join is listed separately. Click the check mark icon next to the Join name to enable that Join. Then repeat steps 9 through 13 to map the columns in the import file to fields on the joined table. The Data Map must include the field (or fields) that make up the Unique Identifier for each joined table.

If the import file doesn't contain the Unique Identifier field for the Source Table, then you must use the Soft Match feature in order to successfully load the data. The Soft Match lets you match to the database using any fields with a Data Type of "Email," "Phone," "Twitter," "Facebook  ID," or "Push Registration ID." For example, let's say the Unique ID on your "Recipient" table is "Member ID," but you don't have Member ID data available in your import file. You could instead select some other field, such as "Email Address," that contains unique data, and match on that instead.

If the soft-matched value in the import file matches an existing value in the database, then the platform will make the update to that record in the database. If the soft-matched value does not already exist in the database, the platform creates a "temporary" record. This temporary record can later be merged with a full record that contains the Unique Identifier.

As described above, if you're using the Soft Match feature, you still must add the Unique Identifier field (or fields) to the Data Map, even if you're not actually intending on providing the Unique ID fields on the inbound data. The Unique ID must be part of the Data Map in order to save the Data Map.

14. If using the Soft Match feature -- the "Available Fields" list box is populated with all of the mapped fields that can be used for a Soft Match. Select a field in this list box, then click the center "Move" bar. Optionally, you can use Shift + click or Control + click to select and move multiple fields. The selected field (or fields) is added to the "Selected Fields" list box. Repeat this step as needed to add more fields to the Soft Match. To remove a field from the Soft Match, click the "X" icon next to the field name within the "Selected Fields" list box.

Note: If you selected a Load and Send table above in step 3, the Soft Match feature is not available.

15. In the Tool Ribbon, click Edit > Save.