Format names, phones, addresses

How to Format Fields in Bulk and 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.  

Existing data in any field can be formatted in bulk with the Transform Data module, or 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.

Format 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. Find and review records for formatting.
  2. Set up functions to format field values.
  3. Preview the changes.
  4. Apply changes to the CRM records.

Step-by-Step Instructions

Step 1: Filter and Review Records to Be Formatted

Navigate to Data Management > Transform Data, pick a record type, and explore the default templates for a pre-built solution.

Under Step 1, adjust the settings to filter the CRM records down to those you would like to format. This ensures you aren't trying to format fields that don't contain any data. And it is best practice to only tackle one field at a time.

In this example, records are filtered down to those with existing data in the Street Address field.

format1.png

You can add any filter relevant to your use case—lifecycle stages, record creation dates, engagement triggers, etc. Insycle can use 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. All records that match your filter will appear. You can alter the fields that appear in this preview from the Layout tab in Step 1.

format2.png
Step 2: Configure Formatting to Apply to Fields

Under Step 2, give Insycle instructions on what formatting and cleanup changes to make to the identified records.

Select the Field that contains the value you want to start with, then select the Function and enter the parameters. Click the plus at the end of the row to add an additional function for this field.

Each function will use the value output from the previous function, so their sequence matters.

There are pre-built functions for standardizing phone numbers, countries, states, domains, and other values, so you should explore the options in the Function dropdown or review the Function Catalog.

format3.png

In the above example, these standardizations are being made to the First Name in each contact record:

  • Remove whitespace from before or after the value.
  • Remove salutations such as Mr or Mr. or Mrs or Mrs. 
  • Capitalize the first name value.

The next example will format specific parts of the Street Address field values. The "Map: Terms" Function will look for the Existing Text and if found, will change it to the New Text value. Here in the Existing Text fields, the bar character "|" between the values means OR, as in, look for "street" or "st.".

transform-data-hubspot-contacts-standardize-street-address-step-2.png

Altogether, these rules will standardize the Street Address like this:

  • Street → St
  • Drive → Dr
  • Avenue → Ave
  • Boulevard → Blvd
Step 3: Preview Changes and Update CRM Records

Preview Changes in the CSV Report

With the filters and functions set up, you can preview the changes. It's important to verify that your formatting is working as expected before those changes are pushed to your live database.

Under Step 3, click the Review button, then select the Preview option.

format5.png

On the Notify tab, add any additional recipients who should receive the CSV (and make sure to hit Enter after each address). 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 (you could do All, but if you have a large number of records, you may just want to do a chunk for your preview), then click the Run Now button.

Open the CSV file from your email in a spreadsheet application and review the values for each row. You can see the (Before) and (After) values side-by-side. For example, Last Name (Before) and Last Name (After).

If the results don't look the way you expected, go back to your filters in Step 1 and functions in Step 2 and try making some adjustments, then preview again.

format6.png

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.

Under Step 3, click the Review button again, and this time select Update mode.

On the When tab, you should use Run Now the first time you apply these changes to the CRM. If you have a large number of records, you may want to do a smaller batch to review the results in your CRM.

format7.png

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, and set up automation so this formatting operation runs on a set schedule.

If you have several templates you'd like to run together automatically, you can create a Recipe. Additionally, HubSpot users can integrate Insycle Recipes into HubSpot Workflows.

By automating with a template, you'll ensure that your fields are consistently and automatically formatted on an ongoing basis. 

Pro Tip: Formatting Examples

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

Format Phone Numbers to E. 164 International Standard

format9.png

 

Format 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.
  2. Map the CSV columns to the CRM fields and specify how to import the values.
  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. 
  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

Navigate to Data ManagementMagical Import, then select the database and the record type in the top menu.

Click the Choose button and 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.

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 icon will show. 

format11.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. If you choose to leave these unmapped, you can remove them by hovering over the column name and clicking the “X”.

Specify How to Import Values 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.

format12.png

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.

format13.png

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.

format14.png

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." This is data 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.

Note that when using an email field, Insycle will automatically cross-reference any additional email fields in the records for a match. If using a domain field, Insycle will check additional domain fields. 

magic-import-step-2-import-email.png

Select one of the Records Modes to tell Insycle how the imported data should be handled during the import process. 

  • Update existing and create net new – If unable to find a corresponding record, a new record will be created in your CRM.
  • Only update existing – 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 – 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.
Step 3: Specify Formatting Changes to Make (Optional)

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 the Column Name from the CSV file.

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

format16.png

In the above example, the settings will do three things:

  1. Capitalize first and last names.
  2. Standardize countries to the three-letter ISO alpha-3 codes.
  3. Format phone numbers to the E.164 international standard and remove any letters.

Explore the Function dropdown to find formatting options for any field in your CSV.

Step 4: Set Up Bulk Actions to Take (Optional)

For additional control over the data you import, use the options under Step 4.

format17.png

  • List – Add your imported data to an existing list. Only available for HubSpot.
  • Bulk Update – Update a specific field on import. For example, you could update all imported records to have the Job Title of “Founder,” regardless of the data in 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. When using Salesforce this is known as “Link.”
  • Date Format – Format dates contained within your CSV file to your preferred standard.
Step 5: Select Records and Import the Data to Your CRM

By default, Insycle will attempt to import all of your formatted CSV data into your database. If needed, you can select individual records for import in the Record Viewer. 

Note that changes are applied to your CRM immediately; there is no preview step. To import, click the Import X Contacts button under Step 5. 

format18.png

After the import runs, the Import Result breaks down the 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.

format19.png

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

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 to reproduce the same formatting operation in the future. 

Pro Tip: Using Multiple Identity Fields

You can select more than one Identity Field in Step 2; however, Insycle will check all of those fields on your CRM records to match. If just one of the selected fields in a CSV row differs from CRM records, it will not be identified as a match.

With this setup, if you select the Update existing and create net new Records Mode, and Insycle cannot match all the selected Identity Fields, it will create new records for the unmatched CSV data.

If you use the Only update existing mode, the CSV rows that don't fully match all the selected fields in any CRM records will be ignored.

Generally, it is best to use a single identity field.

magic-import-too-many-identity-fields.png
Troubleshooting: Using Overly Broad Identity Fields

If you're using a field that is not truly unique as an Identity Field, it's likely that Insycle will identify the wrong record in your CRM as a match and won't be able to identify a record to pull data from. For instance, there could be 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 uniqueness is key.

magic-import-too-broad-non-unique-id-field.png

Advanced How-Tos

Combining More than One Function

If there isn’t a single function that makes the change you’re looking for, you can still complete the step in one template.

Functions can be layered to make changes in a series of steps. They will apply to the field value cumulatively, executing in order from top to bottom.

For example, you can use two functions to clean up Website URL values with multiple directories. Instead of “https://app.insycle.com/data/bulk/contact/,” you just want to keep the main domain value, “insycle.com":

transform-data-contacts-clean-up-url-step-2-numbered.png

  1. The Extract: Domain from URL function will remove the https:// and subdomain, but the directory values after the domain, '/data/bulk/contact/' would remain. Leaving the Target Field blank will write the value back to the original field.
  2. To get rid of those directory values, add the function Split: By any delimiter and keep Nth term to remove everything after the forward slash, “/”, and keep the value in the first section.

For another example, you can use several functions to populate the country value by using the email address:

transform-hubspot-contacts-get-country-from-email-step-2a.png

  1. Select the email field with the value to start with.
  2. Select the Split: By any delimiter and pick the last term function. 
  3. In the Parameter field, enter a "." to use as the delimiter, telling Insycle which part of the email value to isolate and use for the next steps.
  4. Click the plus at the end of the row to add an additional function for this field.
  5. Now that you have the two-character country code, select the Standardize: Country Code2 to Name function to transform it to the full country name. Click the plus.
  6. Select the Copy: Value function and the Target Field the country name should be written into.

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.

This can be a little tricky because clicking "Remove" alone will not revert the format. You need to also add one space to the phone number.

format22.png

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

format23.png

Lastly, make sure to click the Save button.

format24.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.

transform-data-chat.png

Why do I need to filter my data?

While you can run the Transform Data module without a filter, 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 more efficient and manageable 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 for formatting each specific field. This helps you to keep things organized and troubleshoot issues if they arise.

Can I format phone numbers with country codes?

Yes, Insycle can help you format country codes. For more information, see the Format Phone Numbers for Country Codes article.

Additional Resources

Related Help Articles

Related Blog Posts