FTP / S3 Import Templates

To create a new Import Template:

1. Above the list of existing Import Templates, click + New button.

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

3. Click Create. The Workspace is refreshed to show a blank Import Template details screen, where you can configure the details of the Import Template.

4. Optionally, you can assign one or more tags to your Import Template. 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.  

Schedule

The "Schedule" section allows you to set a schedule for automatically executing the import process. To define the schedule frequency and duration:

1. Select either "Start Immediately" or "Start On." If you select "Start On," enter the start date and time.

2. Define the recurrence frequency:

3. • Daily: Enter an interval (every "X" days).

   • Weekly: Select one or more days of the week.

   • Monthly: Select either:

   • • A specific number ("15th of the month" for example).

     • Define a business rule to calculate a date ("second Tuesday of every month" for example).

   • Yearly: Select either:

   • • A specific month / date ("January 15th" for example).

     • Define a business rule to calculate a date ("second Wednesday in August" for example).

3. Select the schedule starting time:

   • Once a day: Enter a specific start time.

   • Several times a day: Define an interval in either minutes or hours. Also, define the active window during which the schedule can run by selecting the starting and ending times for this window.

4. Select how long this automated schedule should run, either "Do Not End" or "End On." If you select "End On," enter the stop date and time.

5. By default, the system will use the time zone that's defined for your user account. To use a different time zone, click Set Time Zone in the Tool Ribbon. A "Set Time Zone" pop-up window is displayed. Select a time zone, then click ok.

6. To enable the schedule, click Edit > Start Schedule in the Tool Ribbon.

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

FTP / S3 Download

The "FTP / S3 Download" section is used to define the details of the expected file location and name.

1. From the "FTP / S3 Profiles" drop-down menu, select the desired FTP or S3 Profile (see FTP / S3 Profiles for more details).

2. In the "File Path" field, enter the filepath to the location on the FTP server or S3 Bucket where the import file will be stored. The value you enter here should include both the folder path and the file name. Typically, files imported into the platform are placed in an "incoming" folder. We recommend adding a date / time stamp to the file name in order to keep file names unique. If your import file contains a date / time stamp, use a date mask in the "File Path" field, such as {(yyyy-MM-dd)} for example.

   • Sample "File Path" value:  /incoming/IINFL100_DAILY{(yyyy-MM-dd)}.csv

Note: Messaging will not pick up a file with the same exact file name more than once.

3. In the "Post Processing Suffix" field, enter a value that the platform will append to the end of every inbound file name. This suffix is used to differentiate processed files from new files, and also to prevent any confusion around duplicate file names. We recommend using a date / time stamp as part of the Post Processing Suffix to help identify when the file was processed.

   • Sample "Post Processing Suffix" value: .processed_{(yyyyMMdd_HHmmss)}

4. Optionally, if you want the platform to delete the file from the FTP site or S3 bucket after it's been downloaded, place a check mark next to "Delete File."

5. Optionally, if you want the platform to check the file's "last modified" date and time prior to importing the file, place a check mark next to "Timestamp." This option is intended to prevent the platform from re-importing the same data file.

6. Optionally, if you want to use PGP encryption, place a check mark next to "PGP Key," then enter your encryption key.

7. Optionally, if the import file is compressed and needs to be expanded, place a check mark next to "ZIP File." If you select this option, a "Password" field is displayed. If you're importing password-protected ZIP files, enter the password in this field.

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

Files to Import

An Import Template can optionally be used to import multiple files within the same ZIP file. You can choose to handle all of these files in the same manner, or you can select different configuration options and Data Maps for each file. Initially, this section displays a single item with the default file name (*.*), which indicates "all files."

To configure the import file (or files):

1. Click the edit link (pen icon) next to the default file name (*.*). The "File to Import" pop-up window is displayed.

2. If you want all the files within the ZIP file handled the same way, simply leave the default file name (*.*) in the "File Name" field. If you want to handle each file within the ZIP file separately, then enter the specific file name in the "File Name" field.

3. From the "Import Priority" drop-down menu, select the appropriate priority for this import -- Urgent, High, or Normal (the default is "Normal"). The Priority you select determines the order in which your imports will be handled. When the system initiates an import with a higher priority than an in-progress import, the higher priority file moves to the next in line, waits for the in-process import to finish, then begins processing. If your imports have the same priority, they will be processed in the order in which they were added (older imports first). 

Note: You can't adjust the priority of an in-progress import. The import priority you select in the "Files to Import" dialog box will be applied to this template from this point forward, and not to any in-progress imports.

4. From the "Data Source" drop-down menu, select the Table in your database where the import file is to be loaded.

5. In the "Data Map" field, either begin typing in the Data Map name, or click the browse button (magnifying glass icon) to browse for and select it. You can also create a new Data Map by clicking the new button (plus-sign icon). See Data Maps for more details.

6. Optionally, if you want to view the details of the Data Map after selecting it, click the "jump-to" button (green up-arrow icon). The system displays the Data Maps screen. When finished, click the Import Template tab in the Top Navigation Pane to return to the Import Template screen.

7. If you have any CASS / NCOA Processes defined for your account, an additional "Postal" drop-down menu is displayed. Optionally, select an NCOA Process from this menu. See NCOA Processes for more details.

Note: The "Postal" drop-down menu is not displayed if you selected a Load and Send table above in step 4.

8. If you have any custom stored procedures defined for your account, an additional "Post-Update Process" drop-down menu is displayed. Optionally, select a stored procedure from this menu. Custom import procedures are typically used to perform additional data transformations on the import file.

9. If your account utilizes a "Parent / Child" database architecture, an additional "Hierarchy Options" drop-down menu is displayed. Select one of the following options:

   • Update Current Customer Only: This option will load the data to the Parent system only.  

   Note: If you selected a Load and Send table above in step 4, then the "Update Current Customer Only" is selected by default, and can't be changed. This option is the only valid setting for Load and Send processing.

   • Update Current Customer and Children: This option will load the data to the Parent and to any Children systems as well.

   • Custom Hierarchy Rule: This option allows you to select a custom Hierarchy Rule. If you select this option, the Hierarchy Rule field is displayed. To use an existing Hierarchy Rule, either begin typing in the Hierarchy Rule name, or click the browse button (magnifying glass icon) to browse for and select it. You can also create a new Hierarchy Rule by clicking the new button (plus-sign icon).

10. If you selected a Load and Send table above in step 4, an additional "Bypass Record limits" check box is displayed. The Load and Send feature imposes a limit of 100 columns and / or 5 million rows on the import file. If you need to import a larger file, you can optionally override this limitation by placing a check mark in this check box.

11. Optionally, if you want commit this file to the database without requiring manual review and approval of the parsing results, place a check mark next to "Auto-Commit." If you leave this option unchecked, the platform will pause the import process after the parsing step, to allow you to manually review and approve the parsing results before committing the import to the database (see Review and Approve an Import for more details).

12. If you want to capture and store records that encounter exceptions, place a check mark next to "Import Exceptions" (note: this is an optional feature that must be enabled within your account in order to be available for selection).

The next option relates to the platform's "update history" functionality. Update history for a file means that the platform writes an update for every record that came in on the file, to denote that the record “came in on this file at this time.” This level of detail allows you to create Filters that can look for all records that came in on a specific file, or in a specified timeframe, and return all of those records. The default setting is to retain this information, in order to build an activity history for all records.

If you don't need, or want, to store update history, you can turn this feature off. An example would be something like a Product table. Let's say you don't create Filters on Product records, but instead use that data only to personalize order confirmations. In this case you can ignore the update history, so that you have only the data, but not the unnecessary details of what records were updated, and when. The advantages to not storing update history are basically storage space and performance. Ignoring update history will give you a slight performance boost on import processing, and store less data on the backend.

13. Optionally, check the "Ignore Update History" check box if you don't want to keep a history of the imported records.

Note: The "Ignore Update History" check box is automatically unchecked, but not displayed, if you selected a Load and Send table above in step 4. Also, the "Filter / Trigger" check box (described below) will be checked, and disabled.

11. • If you left "Ignore Update History" unchecked, you can optionally use the import file as part of a trigger or Filter by checking the "Filter / Trigger" check box, and selecting a folder where you want the platform to save the import item. If you check this check box, the system will automatically generate a Filter that selects the records in that import. This system-generated Filter will be named "Import File <Import name>" and will be located within the same folder as the import item. The Filter will consist of a single rule that uses the File Imports Activity type to target the records in that import file.

    • If you checked "Ignore Update History," you can optionally select to maintain a copy of the import file by checking the "Copy" check box, and selecting a folder where you want the platform to save the copy. This feature is primarily intended for auditing purposes, as you can use the copy of the import file to view file statistics, counts, parsing errors, etc.

14. Click save file mapping. The grid in the "Files to Import" section is updated to show the file name, Data Map, Table, and priority for this import file.

15. If you need to configure additional import files, click add new file, then repeat steps 2 through 11 as needed. Please note that if you're defining multiple import files within the same FTP Import Template, you can have a mix of Load and Send tables and regular relational tables within that same FTP Import Template.

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

Notifications

If you checked the "Import Exceptions" check box above, an additional "Notifications" section is displayed on the screen. This section allows you to define email alerts if the contents of your import file surpass certain thresholds for a selected import metric or exception.

1. In the "Choose Notification Type" drop-down menu, select the desired metric (or "All Metrics"). The "Notification Settings" pop-up window is displayed.

2. In the "Notification Thresholds" section, enter the desired threshold settings that will trigger the alert notification. You can enter one or both of the following rules. If you enter both rules, the notification will be triggered if at least one condition is met.

   • Percentage of file: Enter the percentage of "invalid" records on the import file that will trigger the notification.

   • Number of rows: Enter the quantity of "invalid" records on the import file that will trigger the notification.

3. Email notification settings can optionally be saved as a template, which allows you to reuse those settings. In the "Email Settings" sections, you can either select an existing email notification template, or create a new template.

   • Existing Template: Selected the desired template from the "Template" drop-down menu.

Note: If you want to delete an existing Template, select it from the "Template" drop-down menu, then click delete.

1. • New Template: Enter the email subject line, "from" email address, and recipient email address (or multiple addresses). Enter a short message for the email notification. Optionally, if you want to save these email notifications settings, enter a name for the template in the "Save Template As" field.

4. If you want the system to send notifications after the parsing step, place a check mark next to "Parsing."

5. If you want the system to send notifications after the database update step, place a check mark next to "Update."

Note: You must check at least one of the above check boxes.

6. Click ok. the system adds the email notification to the "Selected Notifications" list. To view or edit the notification details, click the edit button (gear icon); the "Notification Settings" pop-up window is displayed. To delete a notification, click the delete button ("X" icon).

7. Repeat steps 1 through 6 as needed to define the email notifications for additional metrics. Each selected metric is handled separately, and will generate its own email notification message.

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

 

To copy an existing item to use as the basis for a new item:

1. Search for the desired item (see Search for an Item for more details).

2. Click on the item name. The main item screen is displayed and populated with the details of the selected item.

3. In the Tool Ribbon, click Edit > Save As. A "Save as" dialog box is displayed.

4. Enter a name for the new item.

5. By default, the new item will be saved in the same folder location as the base item. Optionally, click the magnifying glass icon to browse to and select a different folder location.

6. Click save a copy. The system creates a copy of the selected item.

 

 

To view or edit an FTP / S3 Import Template:

1. When the screen is displayed, a list of all the current Import Templates is displayed in the left-hand side of the Workspace. Optionally, you can filter this list by typing in all or part of FTP Import Template name in the "Search by Name" field.

2. Click on the Import Template that you want to view. The Workspace is refreshed to show the details of the selected Import Template.

3. Optionally, to view detailed information about the Import Template, click the FTP Import tab in the Tool Ribbon. The Item Details screen is displayed, showing who created the item, who modified it last, and what the last actions taken on the item were. The direct link URL to the Import Template is available here, which can be copied to the clipboard using the "Copy Link" icon. This can be used to easily share and also open the Import Template in a browser. On this screen, click "Related Items" in the Function Menu to see other items in the system that reference or utilize this Import Template. When finished, click the Edit tab in the Tool Ribbon to return to the main edit screen.

4. Optionally, you can assign one or more tags to your Import Template. 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.  

5. Optionally, to rename the Import Template, click Edit >  Rename in the Tool Ribbon. A "Rename Item" dialog box is displayed. Enter a new name for the Import Template, then click save new name.

6. The "Schedule" section allows you to set a schedule for automatically executing the import process. Optionally, make any necessary changes to the schedule frequency and duration (see "Create a New FTP / S3 Import Template" above for more details). To pause or disable a schedule, click Edit > Stop Schedule in the Tool Ribbon.

7. The "FTP / S3 Download" section is used to configure the file handling details. Optionally, make any necessary changes to the file handling options.

8. The "Files to Import" section is used to configure the import file (or files), and to select the priority and Data Map. Optionally, make any necessary changes to the import file configuration.

9. The "Select Notifications" section is used to define email alerts if the contents of your import file surpass certain thresholds for a selected import metric or exception. Optionally, make any necessary changes to the email notification settings.

10. The "Imports" section shows a log of all files imported using this Import Template. The log shows the file name, current status, date / time that the import was finished, and the number of rows that were imported. To refresh the log to see the most updated results, click the refresh link (double blue arrow icon). To view additional details for an import file, such as download start and end time, click the down-arrow icon to the left of the filename.

11. To view parsing details or database update details, click the stats link (magnifying glass icon). The "File Statistics" pop-up window is displayed. The "Parsing" section of this window (expanded and displayed by default) shows the stats and results of the parsing process. Click the "Update" section to view the database update stats. For more details on approving a file import, see "Review and Approve an Import" below.

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

 

The platform parses import data to ensure that the values are clean, valid, and usable. Depending on the Data Type for a field, various methods of parsing and cleansing are utilized. The platform may adjust the import value for a field according to rules set for that type of field. For example, if a numeric value is being imported into a field identified as a "Phone," the platform will verify that this value is the correct length, and will remove any unnecessary and invalid characters. For more details on the Data Parsing rules utilized by Messaging, please see the Data Parsing Help topic.

When importing data via an Import Template, you have the option of automatically committing the data to the database without reviewing it, or of pausing the import process to allow for a manual review of the parsing results.

To manually review and approve the parsing results for an import file:

1. When the screen is displayed, a list of all the current Import Templates is displayed in the left-hand side of the Workspace. Optionally, you can filter this list by typing in all or part of Import Template name in the "Search by Name" field.

2. Click on the FTP / S3 Import Template that you want to view. The Workspace is refreshed to show the details of the selected Import Template.

3. Scroll down to the "Imports" section. This section shows a log of all files imported using this Import Template. The log shows the file name, current status, date / time that the import was finished, and the number of rows that were imported.  

4. Next to the desired import file, click the stats link (magnifying glass icon). The "File Statistics" pop-up window is displayed.

5. Click "Parsing" to review the results of the parsing step. This section shows how many records were on the file, and how many records encountered parsing-related warnings or validation errors. To see detailed statistics, click on a parsing or validation sub-heading.

6. Click "Update <table name>" to review the results of the update step. This section shows how many records passed the parsing step, and how many records encountered update-related warnings or validation errors. To see detailed statistics, click on a validation sub-heading

7. To approve and commit the import records to the database, click commit import. Or, to cancel the import, click rollback.

 

To delete an item:

1. Search for the desired item (see Search for an Item for more details).

2. Click on the item name. The main item screen is displayed and populated with the details of the selected item.

3. In the Tool Ribbon, click Edit > Delete. A confirmation dialog box is displayed.

4. Click delete item to confirm the deletion.

Foldered items are moved to the Recycle Bin. Non-foldered items are permanently deleted.