Standardize Industry Values before Importing Data

In the Magical Import module, picklist validation runs automatically in the Preview when you map a CSV column to a picklist field.

1. In the Magical Import module, select your CSV and template. See Selecting a CSV and Template for Import.
2. Click the Data Mapping heading to expand it, then review the automated mapping or manually map the Industry column to the Industry field in your CRM and set your Matching Criteria. See Mapping CSV Columns to CRM Fields, Field Logic, and Matching Criteria.

3. In the Preview, look for a red warning icon next to Industry values. Hover over the icon to see the warning, “Invalid picklist value.” 

   

   The image above shows the Preview panel in the Magical Import module, with the Filter dropdown set to “Show All Rows.” The table has four columns: Company Name, Website, Industry, and City. Rows 5, 6, 8, and 9 have no warnings. Row 7 (Pinecrest Outfitters) shows a red warning icon next to the row number and next to the Industry value, and a tooltip reading “Invalid picklist value.” appears below the warning icon.

4. (Optional) Once Matching Criteria are set, the Filter dropdown in the Preview becomes available. Select Show Only Warning Rows to list only the rows with warnings. Rows can show warnings for other reasons, so hover over the warning icon to confirm the reason.

In the Magical Import module, picklist validation compares each CSV Industry value to the valid options for the Industry field in your CRM. Before you map values, check which options are valid in either of these ways.

• In your CRM: Open the Industry property (HubSpot) or field (Salesforce) and review its list of options.
• In the Magical Import Preview: Hover over an Industry value, click the pencil icon, and scroll through the dropdown. The dropdown lists the valid options and includes a Search box.

To edit one row at a time, hover over the industry value, click the pencil icon, and select a valid option from the dropdown. To correct many rows at once, clean up the variations in the Data Preparation step, as described below.

The image above shows the Preview panel in the Magical Import module with the Show Only Warning Rows filter applied. The table has four columns: Company Name, Website, Industry, and City. Row 7, Pinecrest Outfitters, shows the Industry value “Camping and Outdoor Gear” with a red warning icon and a grey pencil icon. A dropdown is open below it with a Search box and a scrollable list of valid Industry options. The visible options include Recreation (highlighted), Retail, and SaaS.

In the Magical Import module, the Data Preparation step can apply Map functions to a CSV column, replacing variations of an Industry value with one standardized value.

Once you've selected your CSV and checked that the Industry column maps to the Industry field in your CRM:

1. Click the Data Preparation heading to expand it.
2. Under Column Name, select the Industry column from your CSV.
3. Under Function, select the appropriate Map function. See the table below.
4. In the Existing Text field, enter the Industry values in your CSV that need to change. Separate multiple values with the pipe character ( | ), which means OR.
5. In the New Text field, enter the standardized value.
6. To handle another industry, click the grey + (plus) button at the end of the row to add another function for the same column.
7. Click Apply, then review the column in the Preview. Values that now match a valid picklist option no longer show an “Invalid picklist value” warning.
8. If warnings remain in the Preview, identify the needed picklist values, configure functions under Data Preparation, and click Apply again.

For example, if the Preview flags “Camping and Outdoor Gear” and the valid options include “Recreation,” set the function to Map: Values, Existing Text to “Camping and Outdoor Gear”, and New Text to “Recreation”.

In the image below, three function rows standardize Industry values in one operation:

1. The first function standardizes technology values.
   • Column Name: Industry
   • Function: Map: Contains word. This function finds each term as a separate word, so “IT” matches “IT Services” but not words that merely contain the letters "it", and it matches regardless of capitalization.
   • Existing Text: IT|software|tech|technology
   • New Text: Technology
2. The second function standardizes camping and recreation values. 
   • This added function also applies to the Industry column
   • Function: Map: Contains word. These terms are whole words in the Industry values.
   • Existing Text: Camping|Outdoors|Recreation
   • New Text: Recreation
3. The third standardizes tutoring, training, and higher ed values. 
   • This function also applies to the Industry column
   • Function: Map: Contains substring. This function finds the text anywhere in the value, so “tutor” also matches longer words like “tutoring,” and “higher ed” matches both “Higher Ed” and “Higher Education.” Map: Contains word would not match “higher ed” inside “Higher Education.”
   • Existing Text: tutor|training|higher ed
   • New Text: Education

The image above shows the expanded Data Preparation section of the Magical Import module, marked with a blue numbered badge showing “1”. Three function rows are configured for the Industry column. In the first row, Column Name is “Industry,” Function is “Map: Contains word,” Existing Text begins “IT|software|tech|techno” (cut off), and New Text is “Technology.” In the second row, Function is “Map: Contains word,” Existing Text is “Camping|Outdoors|Rec” (cut off), and New Text is “Outdoors.” In the third row, Function is “Map: Contains substring,” Existing Text is “tutor|training|higher ed,” and New Text is “Education.” Below the rows are an Add Field button and a yellow Apply button.

In the Data Preparation step of Magical Import, the Map function you select determines how Existing Text is matched against each Industry value. Here are several functions that may help you clean up the data:

For the complete list of Map functions, see the Function Catalog in Transform Data.