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
- Find and review records for formatting.
- Set up functions to format field values.
- Preview the changes.
- Apply changes to the CRM records.
Step-by-Step Instructions
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.
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.
Step 2 allows you to set up functions for formatting and cleaning up your data. 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.
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.".
Altogether, these rules will standardize the Street Address like this:
- Street → St
- Drive → Dr
- Avenue → Ave
- Boulevard → Blvd
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.
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.
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.
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 automatically run together, 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.
In Insycle, you can select pre-built functions, or build custom functions, to format and standardize any field in your CRM.
Capitalize First Name, Remove Salutations and Whitespace
Format Phone Numbers to E. 164 International Standard
Format and Standardize Street Addresses
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
- Upload the CSV file.
- Map the CSV columns to the CRM fields and specify how to import the values.
- Select the unique identifier field, and choose an action to take.
- Optionally, specify how to prepare and modify your data before importing.
- Set up the formatting rules for the imported data.
- Import your formatted CSV data.
Step-by-Step Instructions
Select the CSV File for Import
Navigate to Data Management > Magical 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.
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.
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.
Records that have errors in them will not be imported. Other fields will be imported as normal.
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.
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.
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.
In the above example, the settings will do three things:
- Capitalize First and Last Names.
- Format phone numbers to the E.164 international standard.
- Standardize US states to their full name.
Explore the extensive list of options in the Function dropdown to find formatting options for any field in your CSV.
For additional control over the data you import, use the options under Step 4.
- 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.
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.
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.
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.
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 is different 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.
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.
Troubleshooting
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.
Add the space at the end of the phone number field in HubSpot, then click Confirm.
Lastly, make sure to click the Save button.
Frequently Asked Questions
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 |
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 |
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 |
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.
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.
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.
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
- Format Phone Numbers for Country Codes
- Standardize Deal Name Format Using Values From Multiple Fields
- Map Values From One Field to Another
- Module Overview: Transform Data
- Standardize Job Title, Industry, State, Country, or Any Other Free-Text Field
Related Blog Articles