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.

The fields use calculation expressions which can create variables, read values of other fields, use functions and logical evaluation. The result of the expression will be stored on the calculated field. Click on Add New Field button to create a new field on the calculated field dialog.

The expression builder interface for the field will show functions and operators that can be used to generate expressions and the fields on the sources and previous calculated fields available to read values. By clicking on a given function, a small description about it appears and double clicking a function will add it to the expression.

Click on Evaluate! button to view a preview of the field result, based on values of the first row from the imported file.

Complex expressions can be created, using temporary variables and IF conditionals. For these cases, use special variable Result to return the value of the calculated field.

Enclose text values (string) with doublequotes: “Text Sample”. The Plus operator can be used to add numbers or concatenate string values:
Formula: 1 + 1 returns 2
Formula: “a” + “b” returns ‘ab’

Text Functions

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

With the fields Source1_First with value “John” and Source1_Last with value “Doe”:

Function COMPARESTR
Compares if two strings are exactly the same (case sensitive).
Formula: COMPARESTR(Source1_First, “John”) returns TRUE
Formula: COMPARESTR(Source1_First, “JOHN”) returns FALSE

Function COMPARETEXT
Compares if two strings are the same (without case sensitivity).
Formula: COMPARETEXT(Source1_First, “John”) returns TRUE
Formula: COMPARETEXT(Source1_First, “JOHN”) returns TRUE

Function CONCAT
Concatenates two or more strings into a single one.
Formula: CONCAT(Source1_First, ” “, Source1_LastName) returns “John Doe”

Function COPY
Returns a specified number of characters from a string starting at a specified position.
Formula: COPY(Source1_First, 2, 3) returns “ohn”

Function LENGTH
Returns the number of characters in a string.
Formula: LENGTH(Source1_First) returns 4

Function LOWERCASE
Returns a string converted to all lowercase characters.
Formula: LOWERCASE(Source1_First) returns “john”

Function POS
Finds one string within another string and returns the number of the start position of the found string (case sensitive). Returns 0 if did not find the string.
Formula: POS(“o”, Source1_First, 0) returns 2
Formula: POS(“O”, Source1_First, 0) returns 0 (not found)
Formula: POS(“z”, Source1_First, 0) returns 0 (not found)

Function STRINGREPLACE
Returns a string with occurrences of one substring replaced by another substring.
Formula: STRINGREPLACE(“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 UPPERCASE
Returns a string converted to all uppercase characters.
Formula: UPPERCASE(Source1_First) returns “JOHN”

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

Logical Functions

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

Arithmetic Operators

Operator + (plus sign) – Addition
Adds arguments or concatenate strings
Formula: 3 + 2 returns 5
Formula: “Blue” + “Moon” returns “BlueMoon”

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

Operator MOD  – Remainder
Returns the remainder of a division between two supplied numbers
Formula: 13 mod 3 returns 1

Operator DIV – Quotient
Returns the integer quotient of a division between two supplied numbers
Formula: 13 div 3 returns 4

Comparison Operators

Operator = (equal sign) – Equal to
Compares if two arguments are equal, numbers or strings
Formula: 3 = 2 returns 0 – False
Formula: 3 = 3 returns 1 – 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 1 – True
Formula: 3 > 1 returns 0 – False

Operator < (less than sign) – Less than
Compares if first argument is less than second argument
Formula: 2 < 3 returns 1 – True
Formula: 3 < 1 returns 0 – 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 1 – True
Formula: 3 >= 2 returns 1 – True
Formula: 3 >= 4 returns 0 – 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 1 – True
Formula: 3 <= 3 returns 1 – True
Formula: 3 <= 2 returns 0 – False

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

Logical Operators

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

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

Using Previously Calculated Fields

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

Calculated fields are processed in the order listed in the Transform tab. So if a calculated field needs to use the result from another calculated field, its order must be after the original calculated field.

Commands

The expression can contain commands to create complex logical statements. These commands follow Pascal syntax, currently the supported commands are FOR, IF, REPEAT and WHILE.

Command FOR
Starts a loop that executes a number of times.

strAux := “”;
for i := 1 to 5 do
begin
strAux := strAux + ” nr: ” + IntToStr(i);
end;
Result := strAux;

Returns ” nr: 1 nr: 2 nr: 3 nr: 4 nr: 5″

Command IF
Starts a conditional expression to determine what to do next.

strAux := “M”;
if strAux = “M” then
Result := “Male”
else
Result := “Female”;

Returns “Male”

Command REPEAT
Repeat statements until a termination condition is met.

strAux := “”;
repeat
strAux := strAux + “.B”;
until length(strAux) > 10;
Result := strAux;

Returns “.B.B.B.B.B.B”

Command WHILE
Repeats statements whilst a continuation condition is met.

strSource := “John”;
strDest := “”;
while length(strSource) > 0 do
begin
strDest := strDest + copy(strSource, 1, 1) + “.”;
strSource := delete(strSource, 1, 1);
end;
Result := strDest;

Returns “J.o.h.n.”

Download Sample Project