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.”