Data Import Tool - System Admin

Data Import Tool Overview ⏯

Satuit will not take responsibility for any problems you could create in your database because you did not properly prepare your data files for import. Please read the Data Migration Requirements document before attempting to use any of the Satuit Import Wizards.

There will be a fee if you import data incorrectly or import bad data into your database and require Satuit to restore the database from backup.

Additional Child Grids

There are 7 additional child grids to the accounts table (pw_pclientinfo table) you can add to the detail form. All of these grids can be used with the import tools as well.

• pperformance
• pholdings
• paccountasset
• accountchildgrid1
• accountchildgrid2
• accountchildgrid3
• accountchildgrid4

Import Overview

Satuit offers several data import tools to import different types of data into the database. As mentioned above, care and an understanding of the tools should be used prior to starting any import. All imports require the use of a Comma Separated Value (CSV) file. The following types of imports are available.

• Contact and Business Information: This import is typically used to import new businesses and contacts, or update existing contacts in the Satuit database
• Account Details: This import will update account-level details from an Excel spreadsheet that is saved in Comma Separated Value (.csv) format
• Portfolio Details: These five import tools allow users to import investment level details such as those shown below
• Performance
• Holdings
• Transaction History
• Reporting Requirements
• Trade Restrictions
• Fund Sales Summary: Fund Sales details by Business or Contact records

General Rules

The following rules should be observed for all imports regardless of type:

• Picklists must be pre-populated with the values found in your data file. This can be done using the Satuit System Administrator. Pay close attention to spelling as the picklist entered must exactly match the spelling found in the data file
• Import tools require (.csv) file format, with one record per row. Beginning with version 16.4 you can also use TAB, Semicolon, or Pipe delimiters and .txt files in addition to .csv
• Always create a unique ID for the import and map it to a text field. We recommend the following format. DATE-DATA-REP, for example, 021508-MMD-JSMITH
• Always retain the Unique ID from the legacy data set if one is available
• Column headings in the .csv file should only contain characters of numbers, letters, or underscore in the names. Any other characters will cause an error when you try to save the map

Data Transformation

Import tools provide basic data transformation capabilities, enabling greater flexibility when importing data from another source using the System Admin Import Tools, imports via the BulkAPI, as well as imports via the Satuit Data Exchange Utility.

• Data Transformation
  • Users have the ability to assign alternative values for picklist fields (both single picklist and multiple value keyword fields. Go to the Picklist option under System Configuration and you will see a column for alternate mapping. Enter the alternate values you wish to accept for the item (e.g. FI or Fixed for the Fixed Income Product) and any data being imported with those values will go to the right value.

• Multiple accepted values for checkbox fields
  • Checkbox fields accept the values of Yes/No, Y/N, True/False, T/F, and 1/0; automatically mapping them to 1/0 as appropriate.

• Ability to Perform Calculations on numeric/currency fields
  • When creating/editing an Import map, users can specify simple calculations (add, subtract, multiply, divide) to be performed on the source data value to ensure it is reflected accurately within the platform. This could be useful in many situation such as when a market value come from back office in billions but you want to display in Satuit as millions, you could use the divide by 1,000 calculation. To accomplish this you should
    • While mapping the file there is an additional column available for perform calculation on any numeric fields.
    • Click the icon to open the Edit Calculation Window.
      Select the operator you wish to use (add, subtract, multiply, divide) and then the value to apply. 

Listed below are the fields that are required for the contact import:

• Rep – must be identical to system login (i.e. JDOE)
• First name
• Last Name
• City or Zip Code– if blank, please fill with UNKNOWN
• Business – for High Net Worth (HNW) clients, please create an entity of the name (i.e. Jane Doe Entity)
• Any field you have made required using Form Configurations

Other field considerations:

• Unique ID – (Optional) When possible you should assign each record with a unique ID if one does not previously exist
• Refer Name – When Importing data, use a reference name to name your import to allow for easier troubleshooting when needed by Satuit support
• Title – Social Title – should only have one value, i.e. Mr. or Ms. but not “Mr. & Mrs.”
• Dear – You can either copy the info from “First Name” before it is concatenated with the middle initial or concatenate the values in “Social Title” with “Last name”. However, for the more formal version to work for each contact, every contact must have a social title assigned to it
• Names – Satuit has a field for the first name as well as the last name. In some databases, “Middle Initial” and “Suffix” are often their own fields. In Satuit, the middle initial needs to be concatenated into the first name field (i.e. John B.) and the suffix into the last name field (i.e. Smith, CFA)
• Company/Business/Entity – In Satuit, the database is structured to start at the company level, therefore “Company” is a required field. For High Net Worth clients, we suggest creating an entity such as “Jack Smith Entity” or “Smithfield Family”
• Relationship – Often we see keywords in the “Relationship Type” field that are better suited to the “Business Keywords” or “Entity Type.” “Relationship Type” should reflect what their relationship is to your company – such as client or prospect, as opposed to what type of company they are or what they do. “Business keywords” and/or “Entity Type” allow you to further define those relationships – i.e. Fund of Funds, Insurance, or Foundation
• Data is often exported out of Outlook into one cell. Lines of data are often separated by either a “carriage return” (appears as a square symbol in Excel) or a comma. Satuit allows up to 3 lines of address information so information exported out of Outlook should be parsed (if needed) into address lines 2 & 3
• When reviewing a .csv or .xls file for import into Satuit, we often see data that is in the incorrect column i.e. “City” is in “State” and “County” is in “Address3”. This should be cleaned up prior to import
• Additionally, we often see, especially with European addresses, a “Post Code” included in the “City” field. This should also be parsed with the “Post Code” going into its own field
• Finally, Satuit allows for each contact to have 3 different addresses such as business, home, and vacation home

When reviewing data from Outlook, we often note that clients have input two (or more) phone numbers into one phone field. These should be parsed out. During a contact import, Satuit currently allows the import of the following contact information:

• Company Switchboard
• Company Fax
• Company Website
• Direct contact phone
• Direct contact fax
• Direct mobile phone
• Direct email
• Alternative contact information (i.e. home phone, or 2nd email)

The following rules should be observed for all imports regardless of type. Picklists must be prepopulated with the values found in your data file. This can be done using the Satuit System Administrator. Pay close attention to spelling as the picklist entered must exactly match the spelling found in the data file.

• Import tools require Comma Separated Value (.csv) file format, with one record per row
• Always create a unique ID for the import and map it to an unused text field. We recommend the following format. DATE-DATA-REP, for example, 021508-MMD-JSMITH

Always retain the Unique ID from the legacy data set if one is available. You must have Import Rights & Permissions to run an import. (This is set by your Satuit System Administrator.)

Macro to Remove Carriage Returns

• Sub Clean_Carriage_Return()
• Selection.Replace What:=Chr(10), _
• Replacement:=";", LookAt:=xlPart, _
• SearchOrder:=xlByRows, MatchCase:=False
• Selection.Replace What:=Chr(13), _
  • Replacement:="", LookAt:=xlPart, _
  • SearchOrder:=xlByRows, MatchCase:=False
• End Sub

When in doubt, submit a ticket to Satuit Support!

Note

If the import process creates duplicate records due to differences in spelling or data entry errors, you can use the Satuit Merge tools to merge business or contact data.

Data Import Tools Home Screen

All Imports will be saved in a single list. This list has the user-created name and description of each import as well as basic information about the import itself. These fields include the import type and who created the import. From here you can either 'Add' a new import, 'Edit/Start' Import, 'Delete' an existing import, or Filter the existing imports to find one.

Beginning an Import

To begin a new import click Add and then select the type of import you would like to run. To re-use an existing template, select it from the list then click Edit/Start Import. You will notice that the Edit/Start Import button will be grayed out unless a template row has been selected.

Deleting a Map

To delete a previously created import template, select the template(s) you wish to delete, then click the Delete button. You will be prompted to confirm your decision. Click Yes to permanently remove the import template(s) or click No to cancel.

Account Links Import Tool ⏯

This tool requires v16.0+ of Satuit

Linking is a very powerful feature where contacts live in the system once at one entity but can be linked to other records as needed to help manage your complex relationships. The account links import tool lets you create those relationships in bulk using an Excel csv file. The tool allows you to link contacts to accounts and define the contact and account roles as well as set view on portal permissions for each contact and account. Additionally, you can choose to also link the contact to the account's entity and link the contact's entity to the account's entity.