Excel

When working with SSMT, Microsoft Excel is commonly the tool used to manage the data outside the database. As noted, it is critical to format the spreadsheet to text before loading the SSMT extract, but following that Excel offers many techniques to manipulate and work within the data.

Formulas

When inserting a row to create a formula, change the format of the row to 'General'. Otherwise, the formula will not execute and will only display the actual, literal formula.

Trim()

Trim() will removes all blank space from each end of the field. This is useful when two or more fields need to be mapped, and the values are the same except for blank characters. By inserting a column and creating a Trim(<cell reference or value>) formula, the new column will be populated with the field without blank characters.

vLookup()

=vlookup(reference_value,lookup_matrix,col_index_number,range_lookup)
or practically
=vlookup(A3 (referencing the value in A3),B:C (looking thru columns B & C for the value of A3),2 (if found, pull the corresponding value from the 2nd column of the array, in this case C),FALSE) (if no exact match is found, return '#N/A') or TRUE) (pull the closest match).

• the lookup values must be sorted by Ascending
  

This is a complex formula, but is extremely useful. When working with values that need to be mapped, vlookup plays this role: 1. Looks at a reference value. This value can be either a literal or reference.(examples: 'Chicago' or A3) 2. vlookup then scans the indicated second column, searching for the same value referenced in step #1 (scans for 'Chicago' or the literal value within A3). 3a. If the exact value is found, a third field that is indicated in the formula, is called and populates the original field containing the formula. 3b. If the exact value is not found, depending on the parameters, either the closest value is pulled or '#N/A' populates the original formula field.