How to Create Calculated Fields Using FileSculptor

To process a CSV or Excel file and create new fields, use the Transform tab on FileSculptor to create calculated fields.

Calculated fields use a formula expression like in Excel. It can read data from other columns and use logical, text and date functions. The formula result will be stored on the calculated field. Click on Add New Field button to create a new field on the calculated field dialog. Leave the formula text empty, as it is used in those cases where one wants to write a formula on the destination spreadsheet.

The expression editor will display functions and operators and the fields on the source. By clicking on a given function, a small description about it appears and double clicking a function will add it to the expression.

Enclose text values (string) with double quotes: “Text Sample”. Use the & operator to concatenate text values:
Formula: “a” & “b” returns ‘ab’

Text Functions

Text functions can be used in the formula to process the values of fields:

For example, the fields [First] with value “David” and [Last] with value “Barnard”:

Function EXACT
Compares if two strings are exactly the same (case sensitive).
Formula: EXACT([First], “David”) returns TRUE
Formula: EXACT([First], “DAVID”) returns FALSE

Function CONCATENATE
Concatenates two or more strings into a single one.
Formula: CONCATENATE([First], ” “, [Last]) returns “David Barnard”

Function MID
Returns a specified number of characters from a string starting at a specified position.
Formula: MID([First], 2, 3) returns “avi”

Function LEN
Returns the number of characters in a string.
Formula: LEN([First]) returns 5

Function LOWER
Returns a string converted to all lowercase characters.
Formula: LOWER([First]) returns “david”

Function FIND
Locates one string within another string and returns the number of the start position of the found string (case sensitive). Returns #VALUE! when it does not find the string.
Formula: FIND(“a”, [First], 1) returns 2
Formula: FIND(“A”, [First], 1) returns VALUE! (not found)
Formula: FIND(“z”, [First], 1) returns VALUE! (not found)

Function SUBSTITUTE
Returns a string with occurrences of one substring replaced by another substring.
Formula: SUBSTITUTE(“This is a way to live a big life”, ” a “, ” THE “, 1) returns “This is THE way to live THE big life”

Function TRIM
Returns a string trimming leading and trailing spaces.
Formula: TRIM(” test “)returns “test”

Function UPPER
Returns a string converted to all uppercase characters.
Formula: UPPER([First]) returns “DAVID”

Combine functions in any order to create powerful formulas
The return of a function can be used as input to another function:
Formula: UPPER(MID(TRIM(” test “), 1, 2)) returns “TE”

Logical Functions

Function IF
Returns one value if a condition specified is TRUE and another vale if it is FALSE
Formula: IF(“a”=”a”,”IsTrue”, “IsFalse”) returns “IsTrue”
Formula: IF(“a”=”b”,”IsTrue”, “IsFalse”) returns “IsFalse”
Formula: IF(3 > 2,”IsTrue”, “IsFalse”) returns “IsTrue”
Formula: IF(3 < 2,”IsTrue”, “IsFalse”) returns “IsFalse”

Function AND
Returns TRUE if ALL of the arguments evaluate to True
Formula: AND(3 > 1, 5 > 2) returns True
Formula: AND(“a” = “b”, 1 = 1) returns False

Function OR
Returns TRUE if ANY of the arguments evaluate to False
Formula: OR(3 > 1, 5 > 2) returns True
Formula: OR(“a” = “b”, 1 = 1) returns True
Formula: OR(“a” <> “a”, 1 <> 1) returns False

Arithmetic Operators

Operator + (plus sign) – Addition
Adds arguments
Formula: 3 + 2 returns 5

Operator – (minus sign) – Subtraction or Negation
Subtracts the second argument from the first argument
Formula: 3 – 2 returns 1
Formula: 3 – 10 returns -7

Operator * (asterisk) – Multiplication
Multiplies arguments
Formula: 3 * 2 returns 6
Formula: 1.5 * 10 returns 15

Operator / (forward slash) – Division
Divides arguments
Formula: 6 / 2 returns 3
Formula: 15 / 10 returns 1.5

Function MOD  – Remainder
Returns the remainder of a division between two supplied numbers
Formula: MOD(13, 3) returns 1

Function QUOTIENT – Quotient
Returns the integer quotient of a division between two supplied numbers
Formula: QUOTIENT(13, 3) returns 4

Comparison Operators

Operator = (equal sign) – Equal to
Compares if two arguments are equal, numbers or strings
Formula: 3 = 2 returns False
Formula: 3 = 3 returns True
Formula: “Blue” = “Moon” returns 0 – False

Operator > (greater than sign) – Greater than
Compares if first argument is greater than second argument
Formula: 3 > 2 returns True
Formula: 3 > 1 returns False

Operator < (less than sign) – Less than
Compares if first argument is less than second argument
Formula: 2 < 3 returns True
Formula: 3 < 1 returns False

Operator >= (greater than or equal to sign) – Greater than or equal to
Compares if first argument is greater than or equal to second argument
Formula: 3 >= 3 returns True
Formula: 3 >= 2 returns True
Formula: 3 >= 4 returns False

Operator <= (less than or equal to sign) – Less than or equal to
Compares if first argument is less than or equal to second argument
Formula: 3 <= 4 returns True
Formula: 3 <= 3 returns True
Formula: 3 <= 2 returns False

Operator <> (not equal to sign) – Not equal to
Compares if first argument is not equal to second argument
Formula: 3 <> 4 returns True
Formula: 3 <> 3 returns False
Formula: “a” <> “b” returns True

Using Previously Calculated Fields

FileSculptor can use previously calculated fields as arguments. This helps create cleaner formulas.

Download Sample Project