The Import section is used to define import layouts that associate fields from a text, excel, or other format file with the fields in the CDP to place them in the appropriate locations to establish and/or update records. The import definition must be set up when bringing records into a file, and can also be automated within the Automations tool in DataPro.
Step 1 Log into the Compliance Data Platform
The active Institution and File will display in the upper right-hand section to the left of the name of the user logged in.
Step 2 Click Data Processing
Step 3 Click DataPro
Step 4 Click Import
Any Import Definitions that have already been created will be listed under the Import Job Name list
Step 5 To create a new import definition, click the green Add New button
5.1 Type in the Name of the import definition
5.2 File Name is for the import file: Select the file
- Click the drop down arrow to select an already existing file
- Click the “Add new file +” button to browse out and select a file
5.3 Select a File Format (Submission and CenTrax File formats will contain different setup selections than the other file types)
- Fixed Width
- Delimited
- Type the Field Separator in the box if Delimited is chosen
- Excel
- Submission File (The following fields will populate based on the transmittal sheet of the selected submission file_
- File Type
- Activity Year
- CRA Respondent ID
- HMDA Respondent ID
- LEI
- CenTrax File
- CenTrax Files may be imported or merged together if desired
5.4 Type in a Header line count
5.5 Type in the row number for Which row has the field names
5.6 Select a Data Type
- Loans
- Deposits
- Mixtures
- CRA-Qualified
- HMDA-Qualified
- 1071-Qualified
5.7 Select a Data System ID (These were manually created within Settings/Lookups Manager)
5.8 Choose to Enable or Disable the following:
- Include Zero Balance: If a record has a zero balance and this is disabled, the record will not come in. If you are not including balance data then you will want to check the "Yes" bubble for Include Zero Balance.
- Blank values override existing data: Select YES to allow blank values to be brought in during the import process.
- Lookup Table Maintenance: This will allow the system to add new codes to the lookup table if it encounters new codes on the records during the import
- Account Type Mapping: This will allow the system to populate other data fields on a record based on the account type assigned to the record. The account type details are set up within the Settings/Products/Account Types section
- Assign Region by Branch: This will allow the system to assign a region code to the record based on the branch code it has. The region codes are managed within the Settings/Organizations/Regions section
5.9 Select the Calculate Rate Spread option:
- Do not calculate
- All excluding permanent
- Records with no source
5.10 Enable or disable Allow Duplicate Accounts
Note: If Allow Duplicate Accounts is enabled, existing records will not be updated. They will simply be added in again.
5.11 Select Match Records By
- Acct# + Branch + Acct Type
- Acct# + Acct Type
- Acct# Only
- Acct# + Loan#
- ULI Only
- ULI + Acct#
- ULI + Action Date
- ULI + Action Code
- ULI + Action Code + Action Date
5.12 Enable or disable Update Existing Records
5.13 If enabled, place a check mark in the following fields to be updated:
- Address
- Exceptions Only
- Only if address is different
- Geocode
- Import new geocode from file
- Clear geocode
- Balance Fields
- HMDA Fields
- CRA Fields
- Fair Lending Fields
- User-defined Fields
- All Other Fields
5.14 Click Save to establish the import definition
Step 6 Click the Define Fields button to map the field locations
Step 7 To isolate specific fields by the Field Group, click the drop down arrow in the Show Fields box
7.1 Select the desired field group from the list
7.2 To map a field, scroll to the desired field
- For Text files, type in the appropriate numeric value in the Position and Width boxes
- For delimited or excel files, click in the Import Column box and click the drop down arrow to select a field from the list
Step 8 To format a date field, click on the Field Name
8.1 A Date format box will pop up
8.2 Select the appropriate date format for the field
8.3 Click Save
Step 9 To manually select a field from the bottom sample file rather than the CenTrax File, click the drop down arrow to find the field, click the Field Selector radio button located at the bottom of the page
9.1 Click on the field in the list
9.2 Click on the field of data within the data example at the bottom of the page
Step 10 Click Save to retain the field mappings
Note: The Show Fields drop down menu may be used to easily locate and map all of one type of field. For example, if you’re mapping HMDA fields first, you can select HMDA from the show fields drop down, and your list of applicable fields displayed in the file will update accordingly.
Step 11 To run the Import Definition immediately, click the Run Now button located in the upper right hand corner of the import specification window
11.1 When the process is complete, a notification will pop up
11.2 To see results, click on the Results DataPro menu option located on the left hand side of the page.
Note: The status of the import should display as Success and the number of records processed should be equal to the total number of records in the original, imported file. The import can also be confirmed as successful by checking the total number of records displayed within the new file in the File Manager.
Article Reference CDP601