Use the Data Preparation step in Magical Import to format names, phone numbers, and addresses in a CSV before importing the data into your CRM, using the same functions as in the Transform Data module.
In the Magical Import module's Data Preparation step, you can format and standardize names, phone numbers, addresses, and other text fields in a CSV before importing. The functions are the same ones found in the Transform Data module. Data Preparation works only on the CSV values shown in the Preview. It does not read or change data already in your CRM.
Formatting CSV Values in the Data Preparation Step
In the Magical Import module, the Data Preparation step applies Functions to a CSV column before writing the data to your CRM.
- In the Magical Import module, select your CSV and template. See Selecting a CSV and Template for Import.
- Review how your CSV columns map to CRM fields and which Matching Criteria are set under Data Mapping. See Mapping CSV Columns to CRM Fields, Field Logic, and Matching Criteria.
- Click the Data Preparation heading to expand it.
- Under Column Name, select the CSV column to format.
- Under Function, select the formatting function.
- (Optional) To apply more than one function to the same column, click the grey + (plus) button next to the function. Functions run in sequence, and the arrow buttons change their order.
- Click Apply. You can click Apply after each change to the function(s) to see the changes in the Preview.
- Review the column in the Preview. To edit a value directly, hover over it and click the pencil icon.
Note: You must click Apply in order for your change to affect the import.
In the example below, the settings will do three things:
- Capitalize first and last names
- Standardize countries to the three-letter ISO alpha-3 codes
- Remove any letters from phone numbers and format them to the E.164 international standard
Review the Results
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. Insycle will also email you a CSV report of the result.
Review the CSV file to see how each row in your import was processed. For records already in your CRM, you can see the (Before) and (After Update) values side-by-side for each field in your import. For new records, the (Before) cells will be blank.
If you see any "Failed" Results, review the Message to understand the issue and determine steps to resolve it. You can also revisit any warnings shown in the module Preview.
Common Formatting Functions for Names, Phone Numbers, and Addresses
In the Data Preparation step of Magical Import, the Function dropdown includes these functions for common formatting tasks.
| Task | Function | Example |
|---|---|---|
| Capitalize names | Format: Proper case person | john doe >> John Doe |
| Capitalize addresses | Format: Proper case company | 123 main st. >> 123 Main St. |
| Remove salutations | Remove: Terms | Ms Helen Smith >> Helen Smith |
| Format phone numbers to E.164 | Format: Phone E.164 Standard +xxxxxxxxxx | +(44) 161-868.8000 >> +441618688000 |
| Replace address abbreviations | Map: Terms | Main St. >> Main Street |
| Standardize country names to three-letter codes | Standardize: Country name to Code3 | Canada >> CAN |
For the full list of functions and how each one works, see Format Names, Phone Numbers, Addresses in Transform Data and the Function Catalog.
HubSpot Phone Number Validation and E.164 Formatting
In HubSpot, the phone number property validation setting changes which phone formats HubSpot accepts. When enabled, HubSpot accepts phone numbers only in E.164 format and rejects any other format with an error. To avoid the error, either disable phone number validation in HubSpot or format phone numbers exclusively with Format: Phone E.164 Standard +xxxxxxxxxx.
Next Steps in Configuring Your Import
After formatting your CSV values, continue to Filtering CSV Rows with Data Validation Rules to exclude rows that don't meet your quality standards before importing.
Additional Resources
Help Center Articles