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 formulas like in Excel, using functions and operators to create expressions that will be evaluated to generate a value. Click on Add New Field button to create a new field on the calculated field dialog.

You can see the 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 a wizard can be used to input values or select fields.

A preview of the field result is shown based on the formula and values of the first row from the imported files.

You can also click on Edit Formula button to edit the field formula directly, then click Save button to view the results.

The formulas support mathematical operations:
Formula: 2 * 2 returns 4
Formula: 10 / 5 returns 2

Enclose text values (string) with simple quotes: ‘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_FirstName with value ‘John’ and Source1_LastName with value ‘Doe’:

Function CHAR
Returns a character for a specified ASCII value.
Formula: Source1_FirstName + CHAR(37) returns ‘John%’

Function CODE
Returns the ASCII value of the first character in a string.
Formula: CODE(‘J’) returns 74

Function CONCATENATE
Concatenates two or more strings into a single one.
Formula: CONCATENATE(Source1_FirstName, ‘ ‘, Source1_LastName) returns ‘John Doe’

Function EXACT
Compares if two strings are exactly the same (case sensitive).
Formula: EXACT(Source1_FirstName, ‘John’) returns TRUE
Formula: EXACT(Source1_FirstName, ‘JOHN’) returns FALSE

Function FIND
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: FIND(‘o’, Source1_FirstName, 0) returns 2
Formula: FIND(‘O’, Source1_FirstName, 0) returns 0 (not found)
Formula: FIND(‘z’, Source1_FirstName, 0) returns 0 (not found)

Function LEFT
Returns part of the string, with a defined length starting from left.
Formula: LEFT(Source1_FirstName, 2) returns ‘Jo’

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

Function LOWER
Returns a string converted to all lowercase characters.
Formula: LOWER(Source1_FirstName) returns ‘john’

Function MID
Returns a specified number of characters from a string starting at a specified position.
Formula: MID(Source1_FirstName, 2, 3) returns ‘ohn’

Function PROPER
Changes the first character in each word to uppercase and the rest to lowercase.
Formula: PROPER(‘TEST test TeSt’) returns ‘Test Test Test’

Function REPT
Returns a repeated string a specified number of times.
Formula: REPT(‘x’, 10) returns ‘xxxxxxxxxx’

Function RIGHT
Returns part of the string, with a defined length starting from right.
Formula: RIGHT(Source1_FirstName, 2) returns ‘hn’

Function SEARCH
Finds one string within another string and returns the number of the start position of the found string (case insensitive). Returns 0 if did not find the string.
Formula: SEARCH(‘o’, Source1_FirstName, 0) returns 2
Formula: SEARCH(‘O’, Source1_FirstName, 0) returns 2
Formula: SEARCH(‘z’, Source1_FirstName, 0) returns 0 (not found)

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(Source1_FirstName) 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: UPPER(LEFT(TRIM(‘     test        ‘), 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’

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: MOD(13, 3) returns 1

Operator DIV – Quotient
Returns the integer quotient of a division between two supplied numbers
Formula: DIV(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 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

Logical Operators

Operator AND
Returns TRUE if ALL of the arguments evaluate to True
Formula: 3 > 1 AND 5 > 2 returns True
Formula: ‘a’ = ‘b’ AND 1 = 1 returns False

Operator OR
Returns TRUE if ANY of the arguments evaluate to False
Formula: 3 > 1 OR 5 > 2 returns True
Formula: ‘a’ = ‘b’ OR 1 = 1 returns True
Formula: ‘a’ <> ‘a’ OR 1 <> 1 returns 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.