Note for Omatic Users: In SmartIMPORT, we use the term Mappings. These are the functional equivalent of the Dictionaries you may be used to in other import tools.
External data is rarely perfect or formatted exactly as your database requires.
A spreadsheet might list a source as "web", "online", or "website".
Your database might require a single, standardised code, i.e. WEB-DONATION.
Mappings allow you to automatically clean, standardise, and translate incoming data before it enters your system.
To set up a simple map:
Identify the column in your spreadsheet you wish to transform (e.g., "Source").
You can select the appropriate Mapping list for that column.
SmartIMPORT will compare each incoming value with your list and replace it with the standardised system value.
When SmartIMPORT encounters a value in your spreadsheet that does not exist in your mapping list, it follows a strict logic hierarchy:
Match Found: If the incoming value exists in the mapping, the system uses the mapped value.
No Match + DEFAULT: If the value is not found, but your mapping sheet contains a row with the keyword DEFAULT in the first column, the system uses the value defined there.
No Match + No DEFAULT: If the value is not found and no DEFAULT row exists, the system leaves the incoming value exactly as it was.
SmartIMPORT includes a powerful Multi-Column Mapping feature. This allows you to reference values from any column in a spreadsheet row while configuring a mapping for a single field.
This is particularly useful when a single "Source" column needs to determine the values for multiple related fields (like Campaign, Fund, and Appeal) simultaneously, rather than mapping them individually.
Assume we now apply a mapping to the 'source' field, and the mapping sheet we use has many columns. The first contains the value we are mapping from, and the other columns are the values you'd like to use in our import. To reference those other columns, we add _a, _b, _c to the source, i.e. the field becomes: [COLUMN: source_b] or [COLUMN: source_e].
The syntax in more detail is:
[COLUMN: {ColumnName}_{IndexLetter}]
{ColumnName}: The header name of the column you are currently applying the mapping to (e.g., source).
{IndexLetter}: The letter representing the column's position in the spreadsheet:
_a = 1st Column (Column A)
_b = 2nd Column (Column B)
_c = 3rd Column (Column C)
_d = 4th Column (Column D)
(and so on)
Imagine you are importing a spreadsheet with the following structure. You are applying a mapping to the Source column (Column A), but you also need to capture the Campaign, Appeal, and Fund from the adjacent columns to populate different fields in your system.
Incoming Spreadsheet Data:
From | Campaign | Appeal | Fund |
web | Annual 26 | Xmas email | Fund A |
(Column A) | (Column B) | (Column C) | (Column D) |
| Tag | Result | Explanation |
[COLUMN: source_b] | Annual 26 | Retrieves the value from the 2nd column (Column B), which is the Campaign. |
[COLUMN: source_c] | Xmas email | Retrieves the value from the 3rd column (Column C), which is the Appeal. |
[COLUMN: source_d] | Fund A | Retrieves the value from the 4th column (Column D), which is the Fund. |
[COLUMN: source]: Returns the standard mapped/transformed value - the value in Column B, so the Campaign in this example. [COLUMN: source_a]: If the mapping is found, it returns the original source value - the first column (e.g., "web").
When mapping fields, use this advanced feature to define how your Incoming Value (the data being processed) matches against the Spreadsheet Column (the reference data).
Use these options when matching names, codes, emails, or text descriptions.
| Mode | Definition | Example Scenario |
| Exact (Default) | The values must be identical, character for character | Input "Apple" matches Ref "Apple" |
Begins | The spreadsheet value starts with the incoming value | Input '0723423232' matches Ref "07" |
Ends | The spreadsheet value ends with the incoming value | Input '0723423232' matches Ref "232" |
| Begins With | The incoming value matches if it is the start of the reference text. | Input "App" matches Ref "Apple" |
| Ends With | The incoming value matches if it is the end of the reference text. | Input "ple" matches Ref "Apple" |
| Contains | Match if the incoming value is found inside the reference column text. | Input "ppl" matches Ref "Apple" |
| Contained | Match if the reference column text is found inside your incoming value. | Input "Apple Pie" matches Ref "Apple" |
Tip: The difference between Contains and Contained is direction.
Use Contains when your input is a fragment (e.g., search term).
Use Contained when your input is a full sentence or string that holds the keyword.
078665673453.| Starts | Phone Type |
| 07 | Mobile |
| 00447 | Mobile |
| +447 | Mobile |
| Default | Home |
Incoming Value:
078665673453
Analysis: The SmartImport tool analyzes the incoming string 078665673453.
Comparison: It compares the start of the string against the defined mapping rules:
Rule 1 (07): Does 078665673453 start with 07? YES.
Since a match is found, the system applies the mapping immediately.
Assignment: Because the value begins with 07, the system maps the Phone Type to Mobile.
(Note: The rules for 00447 and +447 are skipped because the match was already found, and the Default value (Home) is not used because the value satisfied a specific rule.)
Phone Type: Mobile
Use these options when filtering by price, quantity, date, or score. The incoming value is checked against your spreadsheet row by row until a match is found.
| Mode | Definition | Example Scenario |
| Greater Than | Matches if your input is strictly larger than the reference. | Input 10 matches Ref 5 |
| Greater Than or Equal | Matches if your input is larger than or exactly the same as the reference. | Input 10 matches Ref 10 |
| Less Than | Matches if your input is strictly smaller than the reference. | Input 5 matches Ref 10 |
| Less Than or Equal | Matches if your input is smaller than or exactly the same as the reference. | Input 5 matches Ref 5 |
Here is the step-by-step breakdown of how the logic processes this specific scenario.
Incoming Value: 88
Selected Mode: Less than
Mapping spreadsheet:
| Row # | Column A (Level) | Column B (Range) |
| 1 | 10 | Low |
| 2 | 75 | Medium |
| 3 | 100 | High |
| 4 | Default | Extreme |
The system reads the spreadsheet from top to bottom, checking each row in turn. It stops immediately once a condition is met.
1. Check Row 1
Comparison: Is 88 less than 10?
Result: False
Action: The condition is not met. Move to the next row.
2. Check Row 2
Comparison: Is 88 less than 75?
Result: False
Action: The condition is not met. Move to the next row.
3. Check Row 3
Comparison: Is 88 less than 100?
Result: True
Action: MATCH FOUND. The system stops processing here
The system selects Row 3 and returns the value: High.
105, it would have failed Row 3 and moved to Row 4.