Format names, phones, addresses

Format Fields in Bulk or Automatically to Maintain Consistent Data

Data comes from many sources—website forms, internal data entry, integrations, and APIs. This makes enforcing formats in specific fields difficult, making records difficult to segment and search for.

With Insycle, you can format any field in your database using pre-defined rulesets, and automate those templates to ensure your fields are consistently formatted correctly.  

Two different Insycle modules can help.

Which Module to Use When

Existing data in any field can be formatted in bulk with the Transform Data module. 

If you have an export from a third-party application or other data in a CSV, the new data can be formatted on import using the Magical Import module. 

Formatting in Bulk Using the Transform Data Module

The Transform Data module is ideal for formatting fields like names, phone numbers, and addresses. You can easily format and standardize any free-text field in your CRM, in bulk.

Process Summary

  1. View deals in the Record Viewer.
  2. Set up functions to format field values.
  3. Preview the changes.
  4. Apply changes to the CRM records.
  5. Set up automation to maintain deal name formatting.

 

Step-by-Step Instructions

Step 1: Filter and Preview Records for Formatting

Open the Transform Data module and pick a record type, such as contacts, companies, or deals, to get started. For our purposes, we will be using contacts, but you can also format names, phone numbers, and address data for companies

For a quick start, select an existing template. Insycle includes pre-built templates that can help us to format names, phone numbers, and addresses. There might be a template that already does exactly what you need. You can also use an existing template as a starting point and customize it to your requirements.

select record type

Under Step 1, adjust the settings to filter the CRM records down to those you would like to format. This ensures we aren't trying to format fields that don't contain any data. 

It is best practice to tackle one field at a time, in this example, we filter down to records with existing data in the Street Address field.

filter-for-formatting.png

You can add any filter relevant to your use case—lifecycle stages, record creation dates, engagement triggers, etc. Insycle can utilize any data in your CRM in the filter step.

Click the Search button and scroll down to the Record Viewer at the bottom of the screen. You will see all contacts that match your filter.

records preview

You can alter the fields that appear in this preview by clicking the Layout tab in Step 1.

Step 2: Set Up Functions to Standardize Field Values

In Step 2, we set up our functions for formatting our data. 

While it is best practice to format one field at a time, we include both the First Name and Phone Number field below for demonstration purposes. 

Formatting phone number and first name

For Phone Number, we use the "Format phone USA" function to format all phone numbers that meet the prerequisite of having 10 digits in our database to the U.S. standard.

For First Name, we are doing several things:

  • Removing leading or trailing whitespace.
  • Removing salutations such as Mr or Mr. or Mrs or Mrs. 
  • Capitalizing first names.

This not only formats our names correctly but cleans them up a bit as well.

In our next example, we'll format different variables within the Street Address field. We use the "Map Terms" function to target multiple terms in the Existing Text input, then standardize them to our preferred format in our New Text input.

stree-address.png

On the Street Address field, we are standardizing various aspects of the address. 

  • Street → St
  • Drive → Dr
  • Avenue → Ave
  • Boulevard → Blvd

There are also pre-built functions for standardizing states, to either their full name (New Mexico) or their abbreviation (NM)—Look for the Standardize US States functions. 

Preview Changes in CSV Report

Now with the filters and functions set up, you can preview the changes. That way, you can check to ensure your standardization configuration is working as expected before those changes are pushed to your live database.

First, click the Review button.

review button

In the Transform Data popup, select the Preview option.

preview or update

On the Notify tab, add any additional recipients who should receive the CSV (and make sure to hit Enter after each address). This is the email that is generated when the process runs and will have a CSV attached showing the changes. You can add additional context to the subject line and email body.

On the When tab, click Run Now and select which records to apply the change to (in most cases this will be All), then click the Run Now button.

Open the CSV file from your email in a spreadsheet application and review the (Before) and (After) values for each field. For example, First Name (Before) and First Name (After).

Apply Changes to the CRM

If everything in your CSV preview looks correct, return to Insycle and move forward with applying the changes to the live CRM data.

Click the Review button under Step 3 and select Update Mode

transform data preview or update

On the Notify tab add any additional recipients, then click Next

On the When tab you can choose to run and apply the update now, or you can set up an automation to run the template on a set schedule. The first time you apply these changes to the CRM, we suggest you Run Now.

In the Run Now tab, you can control how many records you can run the template for.

run now

After you've seen the results in the CRM and you are satisfied with how the operation runs, you can save all of the configurations as a template.

Return to the Template menu at the top of the page and click the + to give it a unique name and then click the Save button.

629e890e6c5c3.png

Set Up Automation to Keep the CRM Formatting Consistent

Set up automation for this formatting operation to run on a set schedule.

Under Step 3, click Review, and on the Transform Data popup select Update mode.

Click through to the When tab, and under Automate, select the interval at which you want this formatting process to be executed.

automate

By automating with a template, you'll ensure that your fields are always consistently formatted on an ongoing basis. Learn more about setting up automation.

Formatting on Import Using the Magical Import Module

If you have a CSV file containing data to be imported as new records, use the Magical Import module to format values in the new data as you import it.

Process Summary

  1. Upload the CSV file and
  2. Map the CSV columns to the CRM fields and set import modes for fields.
  3. Select the unique identifier field, and choose an action to take.
  4. Optionally, specify how to prepare and modify your data before importing.
  5. Set up the formatting rules for the imported data, click Apply. 
  6. Import your formatted CSV data.

 

Step-by-Step Instructions

Step 1: Select the File, Map Columns, and Specify How to Import Values

Select the CSV File for Import

Select the Magical Import module from the left navigation, then select the database and the record type in the top menu.

Select the CSV file that you would like to import. Uploading a file into Insycle does not import it to the CRM right away, instead, you can prepare the data in a variety of ways before actually importing it to the CRM.

choose csv file

Map CSV Columns to CRM Fields

After uploading your CSV data, Insycle analyzes your file and compares the columns in the file to fields in your CRM database.

A column is included for each field in the CSV, with the CSV column header appearing as the top row. Then, Insycle automatically detects the matching CRM fields and maps them for you. The second row shows the mapped CRM field names. If your CSV contains fields that are not matched to your CRM, a red exclamation point error notification will show.

mceclip3.png

These "Not Mapped" fields need to be mapped manually by clicking the dropdown and finding the appropriate CRM field. If they remain unmapped, they will not be imported. You can choose to leave those columns unmapped or click the “X” next to the column name when you hover over it to remove it from the list.

Set the Import Mode for Each Column

With all relevant fields mapped, tell Insycle how to use the data on a field-by-field basis by selecting the Import Mode, which is the third row in the table.

append to multi-select picklist

Four Import Mode Options

  • Update – Update CRM records with all non-empty field data contained in the CSV.
  • Fill – Import CSV values only when the corresponding field in your CRM is empty.
  • Overwrite – Insycle will replace any existing data in your CRM with the data in the CSV—even if the CSV field is empty.
  • Append – Import values from the CSV and append (add them to) the existing data already in your CRM. A typical example of how “Append” is used would be in a “Notes” field, or to add to existing picklist data without including historical selections in your CSV. 

You can also preview and edit the import data directly in Insycle by clicking the pencil icon next to any field when you hover over it.

If a record has errors, the red exclamation point notification will be shown on the left side of the record. When you hover over the red exclamation mark, an explanation of the error will be displayed.

record exception

Records that have errors in them will not be imported. Other fields will be imported as normal.

Step 2: Select Unique Identifier Field and Choose Action to Take

Insycle uses Identity Fields to compare your CSV to your CRM data. Identity Fields must be "unique identifiers." These are data points that would only belong to a single contact—such as email addresses, phone numbers, home addresses, or ID numbers.

For example, no other contact in the database would have Maria Hernandez's email address, phone number, or ID number listed on their contact record. If a contact record contains a match for Maria Hernandez's data in one of those fields, we know that is the contact record for Maria Hernandez.

Under Step 2 on the Import tab, select the Identity Field from the dropdown.

email identity field

To determine whether to create a new record or update an existing one, Insycle uses the Identity Field to match CSV rows to records in the CRM. For example, when importing contacts, you'd typically use an email address, phone number, or any other unique value.

If using the "Email" Identify Field, Insycle searches your CRM database for records with a matching email value.

Select one of the following Record Modes to tell Insycle how the imported data should be handled during the import process:

  • Update existing and create net new – Insycle will attempt to match imported records to existing CRM records using the identity field. If Insycle is unable to find a corresponding record, a new record will be created in your CRM.
  • Only update existing – Insycle will attempt to match imported records to existing CRM records using the identity field. If a corresponding record is found, it will be updated with the data from your CSV import. Records that are not matched with an existing CRM record will not be imported.
  • Only create net new – Insycle will attempt to match imported records to existing CRM records using the identity field. Only records that can not be matched with an existing record in your database will be imported. Records that already exist in your CRM will not be updated.
Using Multiple Identity Fields

You can select more than one identity field, however, Insycle will check all of the fields to match. If one single field does not match, the corresponding matching CRM record will not be identified and will not be deleted.

Using the "Update existing and create net new" setting, Insycle will fail to match many records and instead will create a new record.

If you use the "Only update existing" setting with many Identity Fields, you will identify far fewer existing records and much of your CSV data will not be imported because Insycle will not be able to find the correct record to update.

too specific

Outside of specific use cases, it is typically best to use a single identity field.

Troubleshooting: Using Overly Broad Identity Fields

If you're using a field that is not uniquely identifying, Insycle will likely identify the wrong record in your CRM as a match and will not be able to identify a record to pull data from. For instance, there are probably many people with the first name "John" in your CRM, but you want to ensure that you are importing data to the correct "John" record. This is why it is important to use uniquely identifying fields.

too broad
Step 3: Specify Formatting Changes to Make

You can make formatting changes and other updates to the CSV data in Insycle before it is uploaded to your CRM. 

On the Functions tab of Step 3, select fields and functions to apply to the field data.

format on import

First, select the Column Name. This is a column from the CSV file.

Select a Function for each column, telling Insycle how the data should be formatted when it is imported.

In the example above, we are doing three things:

  1. Capitalizing First and Last Names.
  2. Formatting phone numbers to the E.164 international standard.
  3. Standardizing US states to their full name.

There is an extensive list of functions available for any field in your CSV. To find functions that might apply to a specific field, you can use the search feature in the Function dropdown.

phone number formatting

Some examples of other functions that you can apply in this step include:

  • Formatting names and companies as title case.
  • Formatting phone numbers and addresses to popular standards.
  • Removing terms, whitespace, non-letters, and symbols.
  • Formatting states to full state names or abbreviations.
  • Adding prefixes and suffixes.
  • Extracting data, such as domains, from a field.

Click the Apply button to apply your formatting changes to your CSV data. The formatting changes are applied to the data when you click this button, before it enters your CRM. 

 

Step 4: Optionally, Set Up Bulk Actions to Take

Under Step 4, several options give you additional control over the data you import.

mceclip0.png

  • List – Add your imported data to an existing list. Only available for HubSpot.
  • Bulk Update – Update a specific field on import. For example, we could update all imported records to have the Job Title of “Founder,” regardless of the data that is contained within the CSV file.
  • Associate – Associate contacts to companies, deals, and custom objects automatically when importing data. Select a CSV field and the CRM field you’d like to match it with to create associations. Also known as “Link” when using Salesforce.
  • Date Format – Format dates contained within your CSV file to your preferred standard.
Step 5: Select Records and Import the Data to Your CRM

If you do not select individual records, Insycle will attempt to import your entire CSV. Alternatively, you can select individual records for import in the Record Viewer. 

When ready, click the Import X Contacts button under Step 5, and your formatted CSV data will be imported into your database. 

Import contacts

After the import runs, you’ll receive a report that breaks down import information—how many records you tried to import and how many succeeded, failed, were updated, deleted, or unmodified. Click the Run ID to open a CSV record of the import.

A record of these changes can be found anytime in the Activity Tracker.

Save Configuration as Template to Maintain Consistency

After you've seen the results in the CRM and you are satisfied with how the import runs, you can save all of the configurations as a template. All settings—including mapping, import modes, and formatting functions—will be remembered from one import to the next. You'll save time on future imports and reduce errors.

Return to the Template menu at the top of the page and click the + to give it a unique name and then click the Save button.

template

Formatting Examples

In Insycle, you can select pre-built functions, or build custom functions, to format and standardize any field in your CRM. 

Here are some more examples:

 

Capitalize First Name, Remove Salutations and Whitespace
format first name
Format Phone Numbers to E. 164 International Standard
phone number
Format and Standardize Street Addresses
format street address

Troubleshooting

Phone Number Formatting In HubSpot

HubSpot's automatic phone number formatting features can conflict with Insycle's ability to format phone number fields. If HubSpot's automatic phone number formatting is turned on and you'd like to use Insycle for formatting, you'll need to revert this and un-apply the format in HubSpot.

However, it is not always simple to remove because clicking "Remove" alone will not remove this. You need to also add one space to the phone number for the "Confirm" button to be activated.

phone-format-1.png

Add the space at the end of the phone number field in HubSpot, then click 'Confirm.'

phone-format-2.png

Lastly, make sure to click the Save button to save the changes.

phone-format-3.png

Frequently Asked Questions

Which templates can I use to format names?
Template Name Description

Format First Name

Formats first name for proper capitalization

Format Last Name

Formats last name for proper capitalization

Remove Middle Name From First Name

Removes middle names from the "First Name" field

Split Full Name Into First and Last Name

Splits a full name field into First Name and Last Name fields

Extract First and Last Name From Email Address

Extracts first and last names from email addresses and places them in appropriate fields

Which templates can I use to format phone numbers?
Template Name Description

Format Phone Number E. 164

Formats phone numbers to the E. 164 international standard

Format Phone Number US (xxx) xxx-xxxx

Formats USA phone numbers to the (xxx) xxx-xxxx standard

Format Phone Number US +1 (xxx) xxx-xxxx

Formats USA phone numbers to the 1+ (xxx) xxx-xxxx standard

Which templates can I use to format addresses?
Template Name Description

Standardize State Address

Formats phone numbers to the E. 164 international standard

Standardize Street Address

Formats USA phone numbers to the (xxx) xxx-xxxx standard

Extract City from Full Address

Formats USA phone numbers to the 1+ (xxx) xxx-xxxx standard

Extract Postal Code from Full Address

Extracts postal code from an address and places it in the City field

Can you help me build a custom template to format a specific field in my database?

Yes. If you are having trouble figuring out how to format a specific field, contact customer support via the live chat to receive help with building your template.

Do I have to filter my data?

Yes. While you can run the Transform Data module without one, Insycle will analyze your entire database. This is not advisable, as the operations can take a very long time for large databases. Instead, it is easier to filter your data down into small segments and use multiple templates rather than running operations for your entire database.

Is it best practice to build a separate template for formatting each field?

Yes. Ideally, you'll want to build separate templates when using Transform Data for formatting each specific field. This helps you to keep things organized and troubleshoot issues if they arise.

Can I format numbers with country codes?

Yes, Insycle can help you format country codes. For more information, check this help article.

Additional Resources

Related Help Articles

Related Blog Articles