User Guide > Scripting > Script Functions > Script Functions A-F
Was this helpful?
Script Functions A-F
This section includes information on setting the scope of functions, as well as details on each of the available functions.
Abort Function
 
Description
Causes the transformation to stop based on a specified condition.
Return
Not applicable; stops the transformation
Return (Null)
Not applicable; stops the transformation.
Syntax
Abort(number)
Parameters
This function has the following parameters:
number - Any valid numeric expression.
Remarks
Used in a conditional expression. If the number parameter is nonzero (or if no number is supplied), this function sets a flag to stop the transformation.
When an Abort is reached, no changes are applied to the target. Use the Terminate Function instead if you want to apply changes to the target and then continue with the process.
See Also
Example
The following expression converts the data in the ("Account_No") field (along with the remainder of the mapped data. If the rightmost character (digit) of the value in the ("Account_No") field is zero (0), then the flag is reset and stop does not occur:
If Right(FieldAt("/SOURCE/R1/Account_No"),1) == 0 Then
 Abort()
Else
 FieldAt("/SOURCE/R1/Account_No")
End If
Abs Function
 
Description
Returns the absolute value of a numeric value
Return
Absolute value of the number referred in the expressions
Return (Null)
Error: Incorrect argument count
Syntax
Abs(number)
Parameters
This function has the following parameters:
number - Any valid numeric expression.
Remarks
The absolute value of a number is the unsigned magnitude of the number. The abs function returns a double value, if the argument is a floating point value. If the argument is not a floating point value, then the return value is an integer, and in that case is constrained by the bounds below:
Lower bounds: -2147483648
Upper bounds: 2147483647
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also
Example
Each of the following expressions evaluate the value to 33:
Abs(-33)
Abs(33)
Add1Fmt Function
 
Description
Formats (cleans) street address data to USPS standards
Return
Address field string formatted according to Post Office standards (U.S.)
Return (Null)
Error: Incorrect argument count.
Syntax
Add1Fmt(string)
Parameters
This function has the following parameters:
string - Any valid source field name, or any valid string. If it is a string literal, enclose in quotation marks.
Example: Add1Fmt("12 Hall St")
Remarks
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also
Example
The following example displays how to format the data in the source field Address1 to USPS standards:
Add1Fmt(FieldAt("/SOURCE/R1/Address1"))
Applied to a field containing:
1340 N 42nd Street, #2506 or 1555 Meadow Oak Drive
Returns:
1340 N 42ND ST # 2506 and 1555 MEADOW OAK DR
Add1Part Function
 
Description
Splits (parses) a street address field
Return
Address parts, such as number, street name, suffix, etc., to a different field or fields
Return (Null)
Error: Incorrect argument count.
Syntax
Add1Part(part,string)
Parameters
This function has the following parameters. If the string is a string literal, enclose the string in quotation marks.
part - Denotes which part of the address will be returned:
NU: Street number
PRD - Pre-directional
SN - Street name
SU - Suffix
POD - Post-directional
SAU - Secondary address unit
SAR - Secondary address range
REM - Remainder
string - The search string from the source file. It can be entered either as a specific string, or as a field name. If it is a string literal, enclose in quotation marks.
Example: Add1Part("NU", FieldAt("/SOURCE/R1/Address1"))
Remarks
When a field in the source file contains a street address, it is sometimes desirable to split (parse) the full address into parts and write each part to a separate field in the target file. See the Parameters table for a list of all supported parts.
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also
Example
To parse a street address field into separate fields in your target file, where the entire street address is in one source field called "Address1", each of the following line is added in a separate target field expression:
Add1Part("NU", FieldAt("/SOURCE/R1/Address1"))
Add1Part("PRD", FieldAt("/SOURCE/R1/Address1"))
Add1Part("SN", FieldAt("/SOURCE/R1/Address1"))
Add1Part("SU", FieldAt("/SOURCE/R1/Address1"))
Applied to a Address1 containing:
5653 S Blackstone Avenue, #3E
Returns:
For NU: 5653
For PRD: S
For SN: BLACKSTONE
For SU: AVE
Instructions
When your source data file contains one field that includes that includes the street address, it is often necessary to write each part of the address to a separate field in your target data file.
To parse a street address field
1. Open a map.
2. Insert one or more additional fields (depending on how you want the information to be stored in the target file) in the desired position.
3. In this example, the source field that contains the whole street address is ("Address1"). The three fields in the target file that contain the address parts have been given field names: Direction, Number, and Street. The target field expression in each of the three fields has been written to instruct a map to place a specific address "part" in that field in the target data file.
Target Field Name
Target Field Expression
Notes
Direction
Add1Part("PRD", FieldAt("/SOURCE/R1/Address1"))
"PRD" places the pre-directional in the Direction field
Number
Add1Part("NU", FieldAt("/SOURCE/R1/Address1"))
"NU" places the street number in the Number field
Street
Add1Part("SN", FieldAt("/SOURCE/R1/Address1"))
"SN" places the street name in the Street field
4. After the transformation runs, the street address in every record is parsed into the three separate fields in the target data file.
Add2Part Function
 
Description
Splits (parses) an address field
Return
Address parts, such as city, state, ZIP, to a different field or fields
Return (Null)
Error: Incorrect argument count.
Syntax
Add2Part(part,string)
Parameters
This function has the following parameters.
part - Denotes which part of the address will be returned:
c - City
s - State
z - ZIP code
Note:  The ZIP code must be supplied in the standard 5-digit or 5-4 digit format (such as 78727 or 78727-6418). Data in any other format is ignored.
string - This is the search string from the source file. It can be entered either as a specific string, or as a field name: "Field1".If it is a string literal, enclose in quotation marks.
Example: Add2Part("c" "Austin, TX, 78727")
Remarks
When a field in the source file contains a full city-state-ZIP address, it is sometimes desirable to split (parse) the full address into parts (city, state, ZIP) and write each part to a separate field in the target file.
Caution
This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also
Example
To parse an address field into separate fields in your target file, where the city, state, and ZIP are in one field, called "Address2" in the source file. Each of the following lines is entered in a separate target field expression:
Add2Part("c", FieldAt("/SOURCE/R1/Address2"))
Add2Part("s", FieldAt("/SOURCE/R1/Address2"))
Add2Part("z", FieldAt("/SOURCE/R1/Address2"))
Applied to Address2 containing:
Austin, TX, 78727, 334 Hilltop Dr
Returns:
For c: Austin
For s: 334 Hilltop Dr, TX,
For z: 78727
Instructions
When your source data file contains one field that includes the city, state, and ZIP code, it is often necessary to write each part of the address to a separate field in your target data file.
To parse an address field
1. Open your map.
2. Insert an additional field or two (depending on how you want the information to be stored in the target file) in the desired position.
3. In this example, the source field contains the whole address "Address". The three fields in the target file contain the address parts and have been given field names: City, State, and ZIP. The target expression in each of the three fields is written to instruct a map to place a specific address part of the address in that field in the target data file.
Target Field Name
Target Field Expression
Notes
City
Add2Part("c", FieldAt("/SOURCE/R1/Address"))
"c" places the city in the City field
State
Add2Part("s", FieldAt("/SOURCE/R1/Address"))
"s" places the state in the State field
ZIP
Add2Part("z", FieldAt("/SOURCE/R1/Address"))
"z" places the zip code in the ZIP field
4. After the map is run, the city, state, and ZIP code in every record are parsed into the three separate fields in the target data file.
Asc Function
 
Description
Finds the first character in a string.
Return
ASCII code (not hexadecimal code) for the first character in a string
Return (Null)
Error: Incorrect argument count.
Syntax
Asc(string)
Parameters
This function has the following parameters:
string - Any valid string expression. If the string is a string literal, enclose the string in quotation marks.
Example: Asc("string")
Remarks
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also
Example
Locate the first letter of the source field "FirstName" and return the ASCII code for that character:
Asc(FieldAt("/SOURCE/R1/FirstName"))
B64Decode Function
 
Description
Converts a Base64 string
Return
Binary value when supplied with a Base64 string
Return (Null)
Error: Too few arguments.
Syntax
B64Decode(src, [encoding])
Parameters
This function has the following parameters:
src - Required. Contains the Base64-formatted string to be decoded.
encoding - Optional. If you use the encoding parameter, you are limited to DJENC_xxx values. If the parameter is not set, DJENC_OEM is used (uses the current CodePage).
For a list of all of the encoding constants available for this parameter, see Constant Values for Encoding Parameters in EZscript.
Remarks
To convert a string of binary values into Base64 format, use the B64 Encode function.
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also
Example
This expression converts "Field1" contents from Unicode UTF-8 encoding into its original format:
B64Decode(FieldAt("/SOURCE/R1/Field1"), ENC_UTF8)
Another example with harcoded expression (encoded value) and it gives "DataConnect Engineering" as decoded value in return
B64Decode("RGF0YUNvbm5lY3QgRW5naW5lZXJpbmc=")
B64Encode Function
 
Description
Converts a string of binary values
Return
Base64 format when supplied with binary values
Return (Null)
Error: Too few arguments.
Syntax
B64Encode(src, [encoding])
Parameters
This function has the following parameters:
src - Required. Contains the string of binary values to be encoded.
encoding - Optional. If you use the encoding parameter, you are limited to DJENC_xxx values. If the parameter is not set, ENC_OEM is used (uses the current CodePage).
For a list of all of the encoding constants available for this parameter, see Constant Values for Encoding Parameters in EZscript.
Remarks
To convert the Base64 string back to the original binary string, use the B64 Decode function.
After conversion, the new Base64 string can be assigned to a target field in an XML document, for example. B64 Encode/Decode functions are useful for manipulating binary objects such as icons, bitmaps, and audio files, etc.
Caution
This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also
Example
This expression converts "Field1" into Base64 format using Unicode UTF-8 encoding (encodes string of values in Field1 into 8-bit characters):
B64Encode(FieldAt("/SOURCE/R1/Field1"), ENC_UTF8)
Bin Function
 
Description
Converts a decimal into a binary string
Return
Equivalent binary string when supplied with a decimal value
Return (Null)
Error: Incorrect argument count.
Syntax
Bin(number)
Parameters
This function has the following parameters:
number - Required. Decimal to be converted into binary string.
Remarks
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also
Example
The following expression returns 1001001:
Bin(73)
CBool Function
 
Description
Converts the result of a string, number, or expression to a Boolean data type.
Return
Boolean data type when supplied with a string, number, or expression
Return (Null)
Error: Invalid use of NULL.
Syntax
CBool(string)
Parameters
This function has the following parameters:
string - A number or an expression. If the string is a string literal, enclose the string in quotation marks. CBool("string").
Note:  This function supports only true, false, yes, no, and numeric values.
Remarks
If the string expression evaluates to a nonzero value, CBool returns True with a value of -1; otherwise, it returns False with a value of 0.
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also
Example
If Field1 contains data, this expression returns a value of –1 to indicate it is True; if Field1 is blank, this expression returns a value of 0 to indicate it is False:
CBool(Trim(FieldAt("/SOURCE/R1/Field1")))
Best Practice — Add the Trim function to remove any spaces that might be in the field. Without Trim, the expression would return True (-1) if there were one or more spaces in Field1. See Trim Function.
CByte Function
 
Description
Converts a number or numeric string into a Byte data type
Return
Byte data type when supplied with a number or numeric string
Return (Null)
Error: Invalid use of NULL.
Syntax
CByte(number)
Parameters
This function has the following parameters:
number - Any valid integer expression with a value between 0 (zero) and 255.
Remarks
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow. It may also return stack underflow errors.
See Also
Examples
'This example converts all numbers in source ("Field1") into a Byte data type:
CByte(FieldAt("/SOURCE/R1/Field1"))

'This example returns 255 to the target as a Byte data type:
CByte(255)
CDate Function
 
Description
Converts the result of a valid date string or number to a Date data type
Return
Date data type when supplied with a date string or number
Return (Null)
Error: Invalid use of NULL.
Syntax
CDate(datestring)
Parameters
This function has the following parameters:
datestring - Any valid date string.
If the string is a string literal, enclose the datestring in quotation marks.
Example: CDate("datestring")
Remarks
Use the DateValMask Function to define the date mask in your expressions.
The date values "00/00/0000" or "00/00/00" are not valid for this function. These values cause a type mismatch error.
Salesforce.com users: When connecting to Salesforce.com, this function returns local time instead of GMT. If needed, create another script (DateAdd for example) to shift the CDate return value. A map can call the second script.
See Also
Example
This expression converts the source field ("Date") into a Date data type.
The DateValMask function is used to define the date mask of the source:
CDate(DateValMask(FieldAt("/SOURCE/R1/Date"), "mm/dd/yyyy"))
Example:
CDate("1971/02/28") this returns 28-02-1971 in a Date data type
CDbl Function
 
Description
Converts the result of a number or numeric string to a Double data type
Return
Double date type when supplied with a number or numeric string
Return (Null)
Error: Invalid use of NULL.
Error: Type Mismatch
Note:  This error is produced when the number format is incorrect.
Syntax
CDbl(number)
Parameters
This function has the following parameters:
number - Any number between
-1.79769313486232E308 to -4.94065645841247E-324 for negative values, and 4.940645841247E-324 to 1.7976931348632E308 for positive values. Enclose in quotation marks.
Remarks
This function may throw errors for invalid characters, stack overflows, and stack underflows.
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also
Example
This expression converts numeric strings in the source field ("Field1") into Double data types:
CDbl(FieldAt("/SOURCE/R1/Field1"))
CDec Function
 
Description
Converts the result of a number or numeric string to a Decimal data type
Return
Decimal data type when supplied with a number or numeric string
Return (Null)
Error: Invalid use of NULL
Error: Type Mismatch
Note:  This error is produced when the number format is incorrect.
Syntax
CDec(number)
Parameters
This function has the following parameters:
number - Any number between -10^58 to 10^58.
Remarks
This function may throw an error for invalid input.
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also
Example
Example 1:
This expression coerces numeric string in the source field ("Field1") to the Decimal data type:
CDec(FieldAt("/SOURCE/R1/Field1"))
 
Example 2:
Multiply two long data type values and convert the returned value to Decimal data type. If you covert the returned value into long datatype, then it returns Numeric overflow error.
=CDec(CLng(2147483647)*CLng(2147483647))
 
This expression returns 4611686014132420609 in Decimal data type.
ChangeDirectory Function
 
Description
Changes the current working directory from the original directory name to the new directory name
Return
Not applicable for this function
Return (Null)
Not applicable for this function
Syntax
ChangeDirectory(directoryname)
Parameters
This function has the following parameters:
directoryname - Name of the directory that you want to designate as the current working directory. If the string is a string literal, enclose the string in quotation marks.
Example: ChangeDirectory("directoryname")
Note:  Use MacroExpand() while using macros to pass the parameters.
Remarks
When using DOS commands, you may also include the drive letter and change the drive as well as the directory winnt, as in the following:
ChangeDirectory("c:\winnt")
Enclose directory names and paths in quotation marks.
See Also
Example
This expression changes the current directory to TestFiles:
ChangeDirectory("C:\TestFiles")
ChangeFromUnicode Function
 
Description
Converts a string from Unicode using the specified encoding
Return
String value of a supplied Unicode value
Return (Null)
Error: Incorrect argument count.
Syntax
ChangeFromUnicode(string, encoding)
Parameters
This function has the following parameters:
string - The string to convert. If the string is a string literal, enclose the string in quotation marks.
Example: ChangeFromUnicode("string", encoding)
encoding - The value of the desired encoding.
Remarks
If you do not specify an encoding, the default of ENC_OEM is used.
For a list of supported constant names, encodings, values and descriptions, see Constant Values for Encoding Parameters in EZscript.
See Also
Example
This expression changes the string "Test123" from Unicode to ANSI (default) encoding:
ChangeFromUnicode("Test123")
ChangeToUnicode Function
 
Description
Converts a string to Unicode using the specified encoding
Return
Unicode, when supplied with a string
Return (Null)
Error: Incorrect argument count.
Syntax
ChangeToUnicode(string, encoding)
Parameters
This function has the following parameters:
string - The string to convert. If the string is a string literal, enclose the string in quotation marks.
Example: ChangeToUnicode("string" encoding)
encoding - The value of the desired encoding.
Remarks
If you do not specify an encoding, the default of ENC_OEM is used.
For a list of supported constant names, encodings, values and descriptions, see Constant Values for Encoding Parameters in EZscript.
See Also
Example
This expression changes the string "Test123" from ANSI to UTF-8 encoding:
ChangeToUnicode("Test123", UTF-8)
CharCount Function
 
Description
Counts the occurrences of a particular character in a string
Return
Number of occurrences of a particular character in a string
Return (Null)
Error: Incorrect argument count
Syntax
CharCount(character, string)
Parameters
This function has the following parameters:
character - Specify the character that you want to count. If the string is a string literal, enclose the string in quotation marks.
string - Specify a string that you want to count. If the string is a string literal, enclose it in quotation marks.
Example: CharCount("a", "string")
Remarks
This function allows you to count the number of occurrences of a particular character in a string. This is very useful in the Count parameter of events, and also in conjunction with other functions.
The CharCount function is case-sensitive. Hence, if the specified character is in lower case, then the count of occurrence will be of lowercase character.
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also
Example 1
This example returns the number of spaces in the first source field:
CharCount(" ",FieldAt("/SOURCE/R1/Field1"))
The name "John Thompson Reynolds" would return 2.
Example 2
This example returns the number of slashes ( / ) in a source field named "Date":
CharCount("/",FieldAt("/SOURCE/R1/Date"))
The value "6/9/98" would return 2.
Example 3
This example uses CharCount combined with the Parse Function to divide a field into its component parts based on dashes ( - ), and return the last part. If the index is less than 1 or greater it returns a null value.
Parse(CharCount("-", FieldAt("/SOURCE/R1/Field8"))+1, FieldAt("/SOURCE/R1/Field8"), "-")
"78765-2356" would return 2356.
Choose Function
 
Description
Selects a value from a list of values
Return
Value specified from a list of values of a specific type
Return (Null)
Error: Too few arguments.
Syntax
Choose(index, value1[, value2] . . .)
Parameters
This function has the following parameters:
index (required) - An integer value between 1 and the number of value parameters.
value1 (required) - Specify the value you want to translate.
value2 (optional) - Additional value to translate. You can add value3, value4, etc. See Remarks.
Remarks
The Choose function takes a variable number of parameters consisting of an index parameter and a list of values as additional parameters. The maximum number of values is 13.
Choose returns the parameter corresponding to the index as the function result.
Choose can be used to select items from a predefined list or to translate numeric codes into textual values.
Each value parameter passed to the Choose function is evaluated, regardless of whether or not it is selected as the result. Care should be taken not to use expressions that might cause undesirable side effects.
The Choose function returns null if index is less than 1, or if index is greater than the number of value parameters in the parameter list.
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also
Examples
The following example translates color codes in the ("Color") field to color names:
Choose(FieldAt("/SOURCE/R1/Color"), "Red", "Blue", "Green")
This example uses the Weekday function to translate the contents of the field ("Date") into the names of the days of the week. The source file contains the dates 12/01/03 through 12/05/03. The corresponding days Monday through Friday are returned to the target:
Choose(Weekday(FieldAt("/SOURCE/R1/Date")), "Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday")
Chr Function
 
Description
Translates a single numeric character code to a Unicode character value.
Return
Unicode equivalent of a one-character ASCII string
Return (Null)
Error: Incorrect argument count.
Syntax
When number is a decimal value:
Chr(number)
When number is a hexadecimal value:
Chr(&h41)
Parameters
This function has the following parameters:
number - Any valid integer expression with a value 0 to 65,535 (inclusive).
Remarks
Chr(034) in the first example below represents literal quotation marks.
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also
Examples
To remove quotation marks in the source field named "FIELDNAME" write the following expression:
GSub(Chr(034), "", FieldAt("/SOURCE/R1/FIELDNAME"))
To get the character value of the hex number 41 use the following expression. (This returns "A" minus the quotes):
Chr(&h41)
CInt Function
 
Description
Coerces the result of a number or numeric string to an Integer data type
Return
Integer data type equivalent of a supplied number or numeric string
Return (Null)
Error: Invalid use of NULL.
Syntax
CInt(number)
Parameters
This function has the following parameters:
number - Any number between -32,768 and 32,767.
Remarks
Fractions are rounded. If the number happens to be exactly .05, then it is rounded to the nearest even number.
Example: 0.5 is rounded to 0; 1.5 is rounded to 2.
Limitation: CInt supports values of 4 bytes or less.
This function differs from the Fix function or the Int function in that it rounds rather than truncating the data.
This function may throw errors for invalid characters, stack overflows, and stack underflows.
Caution
This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also
Examples
This expression returns 234 in an Integer data type:
CInt(233.6557)
The expression below calculates phone call duration in hours, minutes and seconds and determines the difference in seconds between the start and end times 0217094349 and 0217094433. This expression is in total number of seconds. To calculate that field into hours, minutes, and seconds, you can write another expression in a separate target field:
CInt(FieldAt("/SOURCE/R1/Field10")) + 60 * CInt(FieldAt("/SOURCE/R1/Field9")) + 3600 * CInt(FieldAt("/SOURCE/R1/Field8"))) - CInt(FieldAt("/SOURCE/R1/Field5")) + 60 * CInt(FieldAt("/SOURCE/R1/Field4")) + 3600 * CInt(FieldAt("/SOURCE/R1/Field3")))
Clean Function
 
Description
Removes all non-printable characters from a user-specified string. The current language of the system determines what is non-printable. For example, a system that uses USA English ASCII characters designates printable characters as between 32 and 126 Decimal. Anything outside of this range would be considered non-printable on that system.
Return
Specified string with all non-printable characters removed
Syntax
Clean(string)
Parameters
This function has the following parameters:
string - The string from which you want to remove all non-printable characters. If the string is a string literal, enclose the string in quotation marks.
See Also
Example
Clean("string")
ClearMacro Function
 
Description
Clears a macro that has been previously defined
Return
Not applicable for this function
Return (Null)
Error: Incorrect argument count.
Syntax
ClearMacro(macroname)
Parameters
This function has the following parameters:
macroname - Name of the macro you want to undefine. If a string literal, enclose in quotation marks.
Example: ClearMacro("macroname")
See Also
Example
Clears the definition of a macro previously defined as mymacro:
ClearMacro("mymacro")
CLng Function
 
Description
Convert the result of a numeric string to a Long data type
Return
Long data type equivalent of a supplied number or numeric string
Return (Null)
Error: Invalid use of NULL.
Syntax
CLng(number)
Parameters
This function has the following parameters:
number - Any number between -2,147,483,648 and 2,147,483,647.
Remarks
Fractions are rounded. If the number is exactly .05, then it is rounded to the nearest even number.
Example: 0.5 is rounded to 1; 1.5 is rounded to 2.
This function differs from the Fix and Int functions, because it rounds rather than truncates the data.
This function may throw errors for invalid characters, stack overflows, and stack underflows.
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also
Example
This expression converts numeric strings in source ("Field1") into Long data types:
CSng(FieldAt("/SOURCE/R1/Field1"))
Example:
CLng(879.7554) this returns 880 in a Long data type
Compare Function
 
Description
Compare two string arguments
Return
Value that represents the result of a comparison of two string arguments
Return (Null)
Error: Too few arguments for function
Syntax
Compare(strexpr1, strexpr2 [,ignoreCase])
Parameters
This function has the following parameters:
strexp1 - The first string expression.
strexp2 - The second string expression.
ignoreCase - Optional. Specifies the string comparison method. This value must be 0 or 1.
If set to 0, case is not ignored (string comparison is case-sensitive).
If set to 1, case is ignored (string comparison is not case-sensitive).
Remarks
This function returns a value to indicate the relationship between the string expressions. If strexpr1 is less than strexpr2, -1 is returned. If strexpr1 equals strexpr2, 0 is returned. If strexpr1 is greater than strexpr2, 1 is returned.
Tip...  Compare and StrComp are interchangeable and identical in syntax.
Be careful to observe case when using a function that performs a string-search. String-search Functions are case-sensitive, literal searches. A Function that searches for "x" disregards "X". If the function has an optional parameter "compare", and you do not use the optional compare parameter, the expression is case-sensitive by default.
Caution!  This function returns a value. The value must be used or stored. Failure to do so causes a stack overflow. See Stack Overflow.
See Also
Example
This example compares "zip" and "ZIP" and requires the string comparison to be case-sensitive by setting "0" for the ignoreCase parameter. It returns "1", since the text strings are mixed case:
Compare("zip", "ZIP", 0)
If the ignoreCase parameter is set to 1 in the expression above, a 0 is returned to the target, indicating that the text strings are the same (case-sensitivity is ignored).
CreateObject Function
 
Description
Creates and returns a reference to an ActiveX object
Return
Reference to the ActiveX object created
Return (Null)
Error: Incorrect argument count.
Syntax
CreateObject("objectname")
Parameters
This function has the following parameters:
objectname - Required. Name of the object to create. Enclose in quotation marks.
Remarks
Every application that supports Automation provides at least one type of object. For example, a word processing application may provide an Application object, a Document object and a Toolbar object. Once an object is created, you reference it in code using the object variable you defined.
Use the CreateObject function when there is no current instance of the object. If an instance of the object is already running, a new instance is started, and an object of the specified type is created. To use the current instance, or to start the application and have it load a file, use the GetObject function. If an object has registered itself as a single-instance object, only one instance of the object is created, no matter how many times CreateObject is executed.
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also
Example
Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application")
xlApp.Application.Workbooks.Open "c:\tutor1.xls"
xlApp.visible = True
x = xlApp.Application.Cells(1,1).Value
FileWrite("X: " & x)
"This is from Excel Cell (1,1): " & x
xlApp.Quit
Set xlApp = Nothing
Return
End If
CSng Function
 
Description
Converts the result of a number or numeric string to a Single data type
Return
Single data type equivalent of a supplied number or numeric string
Return (Null)
Error: Invalid use of NULL.
Syntax
CSng(number)
Parameters
This function has the following parameters:
number - Any number between -3.402823E38 and -1.401298E45 for negative values, and 1.401298E045 to 3.402823E38 for positive values.
See Also
Example
This example converts numeric strings in source ("Field2") into Single data types:
CSng(FieldAt("/SOURCE/R1/Field2"))
 
Applied to a Field2 containing:
1536.75263541
 
Returns: 1536.752635 in to single data type.
CStr Function
 
Description
Converts a number or date expression to a String representation
Return
Type string when supplied with string, long, double, decimal or date
Return (Null)
Error: Invalid use of NULL.
Syntax
CStr(expr)
Parameters
This function has the following parameters:
expr - Expression to evaluate.
Remarks
If the parameter is not one of the types listed in Return above, a ERR_TYPEMISMATCH error is returned.
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also
Example
This expression coerces decimals in the source field ("Field1") into a string data type:
CStr(FieldAt("/SOURCE/R1/Field1"))
This expression converts date into string data type and returned string can be used in SubString function.
Example:
var1 = CStr(CDate("28-02-1971"))
SubString(var1, 1, 5)
This expression returns 28-02 in a String data type.
CurDir Function
 
Description
Requests the current directory path
Return
String representing the current path
Return (Null)
String representing the current path, current working directory
Syntax
CurDir[(drive)]
Parameters
This function has the following parameters:
drive - Optional. A string expression that specifies an existing drive. If a string literal, enclose in quotation marks.
Example: CurDir("string")
Remarks
If no drive is specified, or if drive is a zero-length string (""), CurDir returns the path for the current drive.
This function may throw errors for invalid characters, stack overflows, and stack underflows.
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
Example
Returns the current working directory on the C drive:
CurDir("C:\")
Date Function
 
Description
Requests the current system date
Return
Current system date
Return (Null)
Current system date
Syntax
Date()
Parameters
No required parameters, however the parentheses after the function name are required.
Remarks
The Date function returns the date as a formatted string, formatted based on the current locale settings for the machine.
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also
Example
This expression returns the current system date:
Date()
DateAdd Function
 
Description
Add or subtract a time interval to or from a date value
Return
Date representing original date with the interval added or subtracted
Return (Null)
Error: Incorrect argument count.
Syntax
DateAdd(interval, number, date)
Parameters
This function has the following parameters:
interval - A string expression describing the type of time interval to add to the date argument. If a string literal, enclose in quotation marks. DateAdd("string") A list of valid time interval strings is given below.
Time Period - Interval Value
Year - yyyy
Quarter - q
Month - m
Day of year - y
Day of month - d
Day of the week - w
Week - ww
Hour - h
Minute - n
Second - s
The intervals for Day of Year, Day, and Weekday (y, d, and w) can be used interchangeably to add days to a date.
number - numeric expression specifying the number of time intervals to add to the date argument. Positive values add time to the date and negative values subtract time from the date.
date - A date expression that represents any date or string value. A specified number of time intervals can be added to the base date. If a string literal, enclose in quotation marks.
Remarks
This function returns a date value. Each part of the date is adjusted according to the rules of the Gregorian calendar. This includes adjustments made for leap years and the different number of days in each month.
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also
Example
The following example shows the behavior the DateAdd function when months are added to a date:
DateAdd("m", 7, "31-Jul-1999")
The function returns a result of 29-Feb-2000. The year 2000 was a leap year, so the last day of February was the 29th. After the months are added to the original date, the day value is adjusted backward in time to ensure the resultant date is valid.
DateConvert Function
Use the DateConvert mask as the source format, not as the target format.
Description
Changes the format of a date string
Return
User-specified format of a supplied date string
Return (Null)
Error: Incorrect argument count.
Syntax
DateConvert("sourceFormat", "targetFormat", "sourceString")
Parameters
This function has the following parameters:
sourceFormat - Format of the date string. Enclose in quotation marks.
targetFormat - Format of the date string to return. Enclose in quotation marks.
sourceString - This is a string representing a date in the format specified by sourceFormat. Enclose in quotation marks.
For the format parameters, the following intervals are valid:
Time Period - Interval Value
Year - y
Month - m
Day - d
Remarks
If you are using ASCII as both your source and target connector, use this function. But if you are converting dates from an ASCII file to a SQL database date field, use the DateValMask Function instead. See DateValMask Function.
If your source data contains two-digit year values, this function assumes and appends the workstation's system century digits when the data is converted to an application or format where four-digit year values are used. For information on specifying with an expression exactly which two century digits you want to have appended, see Using Scripts to Perform Specific Tasks.
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also
Examples
The following example shows how to convert a 5-digit Julian date where the year is represented by two digits and the number of days in the year is represented by three digits. It is converted to an 8-character date string:
DateConvert("yyddd", "mm/dd/yy", FieldAt("/SOURCE/R1/Field1"))
The following example converts a string date of the format 19981214 to a date of the format 12/14/1998:
DateConvert("yyyymmdd", "mm/dd/yyyy", FieldAt("/SOURCE/R1/Field1"))
The following example specifies that all of the dates are between 1900/01/01 and 1999/12/31. The base century is 1900 and all two-digit years are mapped into the base century:
DateConvert("mmddYY1900", "yyyymmdd", ("091703"))
The following example indicates that all two-digit years starting with 55 are mapped into the 1900s. The two-digit years that are less than 55 are mapped into the next century:
DateConvert("mmddYY1955", "yyyymmdd", ("091726"))
DateDiff Function
 
Description
Determine the number of time intervals between two dates
Return
Number of time intervals (i.e., day, week) between two supplied dates
Return (Null)
Error: Incorrect argument count.
Syntax
DateDiff("interval", "date1", "date2")
Parameters
This function has the following parameters:
interval - A string expression describing the type of time interval used to calculate the difference between the two date arguments. A list of valid time interval strings is given below. Enclose in quotation marks.
Time Period - Interval Value
Year - yyyy
Quarter - q
Month - m
Day of year - y
Day of month - d
Day of the week - w
Week - ww
Hour - h
Minute - n
Second - s
date1 - A date expression representing the first date used in the difference calculation. Enclose in quotation marks.
date2 - A date expression representing the second date used in the difference calculation. Enclose in quotation marks.
Remarks
To calculate the number of days between date1 and date2, you can use either Day of year ("y") or Day ("d"). When interval is Weekday ("w"), DateDiff returns the number of weeks between the two dates. If date1 falls on a Monday, DateDiff counts the number of Mondays until date2. It counts date2 but not date1.
If interval is Week ("ww"), however, DateDiff returns the number of calendar weeks between the two dates. It counts the number of Sundays between date1 and date2. DateDiff counts date2 if it falls on a Sunday; but it does not count date1, even if it does fall on a Sunday.
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also
Example
This example writes the difference in number of days between the dates in the expression.
After running the transformation, the number 41 is returned to the target field:
DateDiff("d", "02/02/01", "03/15/01")
DatePart Function
 
Description
Extracts a specified part of a Date string
Return
Part of a date based on a user-specified time interval
Return (Null)
Error: Incorrect argument count.
Syntax
DatePart("interval", "date")
Parameters
The DatePart function uses the following parameters:
interval - A string expression describing the type of time interval to extract from the date argument. A list of valid time interval strings is given below.
Time Period - Interval Value
Year - yyyy
Quarter - q
Month - m
Day of year - y
Day of month - d
Day of the week - w
Week - ww
Hour - h
Minute - n
Second - s
date - A date expression representing the whole date from which the specified part is to be extracted.
Remarks
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also
Example
The following example writes the day of the year for source dates ("Date") to the target file:
DatePart ("d", DateValMask(FieldAt("/SOURCE/R1/Date"),"mm/dd/yyyy"))
The DateValMask function is also used here. The mask, "mm/dd/yyyy", indicates the format of dates in the source file (not the desired target date format). All source field dates must be in this format for the function to return the appropriate data to the target. See DateValMask Function.
DateSerial Function
 
Description
Constructs a date value
Return
Date value from user-specified year, month, and day values
Return (Null)
Error: Incorrect argument count.
Syntax
DateSerial(year, month, day)
Parameters
This function has the following parameters:
year - A number or numeric expression representing the year part of the date. Acceptable values for year are integers between 1 to 25000, inclusive. Negative year values are not supported.
month - A number or numeric expression specifying the month part of the date. The supported range of values for month is 1 to 12, inclusive.
day - An integer value or numeric expression in the range of 1 to 31.
Remarks
Some other implementations of DateSerial support the use of argument values outside of the normal allowable ranges for specifying relative dates. This implementation only supports absolute dates.
You may also specify a number or numeric expression to determine a number of days, months and years before or after a date. See the examples below.
Values for the year parameter are taken literally. Two digit values cannot be used to specify years in the current century.
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also
Examples
This example creates a serial date value for January 1, 2003:
DateSerial(2003, 1, 1)
This example creates a serial date value for six years, three months and three days BEFORE the date Dec. 28, 2000. The result returned to the target is 9/25/1994:
DateSerial(2000-6, 12-3, 28-3)
DateValMask Function
 
Description
Converts formatted date strings into real date values based on a date edit mask
Return
Date value from user-specified source date mask and supplied date string
Return (Null)
Error: Incorrect argument count.
Syntax
DateValMask(dateString, sourceMask)
Parameters
This function has the following parameters:
dateString - Any valid string expression representing a formatted date value. It may be entered as a field name. If a string literal, enclose in quotation marks.
sourceMask - The string expression representing the source date mask. If a string literal, enclose in quotation marks.
Remarks
If you are converting dates from an ASCII file to a SQL database date field, use this function. But if you are using ASCII as both your source and target connector, use the DateConvert Function.
If your source data contains two-digit year values, this function assumes and appends the workstation's system century digits when the data is converted to an application or format where four-digit year values are used. For information on specifying with an expression exactly which two century digits you want to have appended, see Using Scripts to Perform Specific Tasks.
If the format of the date in the source does not match the mask, a null is returned.
No Millisecond Support: Mapping does not support milliseconds; since thousandths of seconds are not stored, zeros are displayed. When dealing with milliseconds, check the Size field property to ensure the field is large enough to avoid a data truncation error.
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also
Examples
The following example illustrates how to use this function where ("FIELD1") in the source file contains a date string such as 03231995, and the data in this field is being converted to a "date" field type in the target application:
DateValMask(FieldAt("/SOURCE/R1/FIELD1"), "mmddyyyy")
The following example concatenates two source fields ("Date Run") and ("Time Run") into a standard date and time format in the target. ("11/22/99 13:52:25"):
DateValMask(Trim(FieldAt("/SOURCE/R1/Date run") ) & Trim(FieldAt("/SOURCE/R1/Time run") ), "mm/dd/yyyyHH:MMAm")
This example standardizes data from the source ("DateTime") field. Some records have a dash between the date and time while others do not. The mask must be changed for both source formats as follows:
DateValMask(FieldAt("/SOURCE/R1/DateTime"), "yyyy-mm-dd H24:MM") and DateValMask(FieldAt("/SOURCE/R1/DateTime"), "yyyy-mm-dd- H24:MM")
The following example specifies that all of the dates are between 1900/01/01 and 1999/12/31. The base century is 1900 and all two-digit years are mapped into the base century:
DateValMask("091703","mmddYY1900")
The following example indicates that all two-digit years starting with 55 are mapped into the 1900s. The two-digit years that are less than 55 are mapped into the next century:
DateValmask("091703","mmddYY1955")
DateValue Function
 
Description
Convert a character string representation of a date into a date value
Return
Date value from user-specified date string
Return (Null)
Error: Incorrect argument count.
Syntax
DateValue("dateString")
Parameters
This function has the following parameters:
dateString - Any valid string expression representing a formatted date value. It may be entered as a field name.
Remarks
This function is used to convert formatted date strings into real date values. These date values can then be used in date calculations or formatted differently.
If your source data contains two-digit year values, this function assumes and appends the workstation's system century digits when the data is converted to an application or format where four-digit year values are used. There are different conventions for converting two-digit year values to four-digit year values. The convention used in Dataconnect—if the year is less than 70, then it is converted to 20xx. Else, it is converted to 19xx. For information on how to specify (in an expression) which two century digits you want to have appended, see Using Scripts to Perform Specific Tasks.
The results from the DateValue function depend on how your system displays dates. For example, if you are not getting the expected result on a Windows machine, check the Regional and Language Options.
If no year is specified, the DateValue function defaults to the current year.
The date values "00/00/0000" or "00/00/00" are not valid for this function. These values cause a type mismatch error.
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also
Example
This example converts text date strings in the source field ("Date") to numeric formats. Use the Trim function to remove leading and trailing spaces in the data so the function works as expected:
DateValue(Trim(FieldAt("/SOURCE/R1/Date")))
Formats
The DateValue function understands dates in the formats shown in the following table. For each format shown, an example and its associated result is provided.
Format
Example
Result (yyyymmdd)
mm-dd-yyyy
07-12-1994
19940712
mm-dd-yy
12-03-94
19941203
mm-dd
11-25
19941125
mm/dd/yyyy
01/01/1955
19550101
 
mm/dd/yy
09/23/47
19470923
mm/dd
08/03
19940803
dd-mon-yyyy
12-Jun-1983
19830612
dd-mon-yy
15-Mar-01
19010315
dd-mon
30-Sep
19940930
dd-month-yyyy
31-October-2315
23151031
dd-month-yy
10-August-58
19580810
dd-month
01-April
19940401
month dd, yyyy
September 26, 1979
19790926
mm.dd.yyyy
09.26.1979
19790926
Day Function
Description
Extracts the day value
Return
Day of the month from a supplied date value
Return (Null)
Error: Incorrect argument count.
Syntax
Day(dateString)
Parameters
This function has the following parameters:
dateString - Any value or expression that can represent a date.
Remarks
Numeric values are treated as date/time values, where the fractional part of the number represents time and the whole part represents a Julian date. Dates formatted as text strings are interpreted in the same way the DateValue function interprets date strings.
Null values are treated as a numeric zero.
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also
Examples
This example returns the day of the month for the current date. (7/13/2000 returns "13" to the target:
Day(Now())
This example returns the day of the month for the current system date and adds 10 (days) to the day of the month value (6/22/2003 returns "22" to the target):
Day(Now()) + 10
The example below returns the day portion from the source ("ShipDate"). The DateValue function defines each date to be date values, while the Day function defines those values as days:
a = DateValue(Trim(FieldAt("/SOURCE/R1/ShipDate")))
Day(a)
DefineMacro Function
Note:  The DefineMacro function can be used to define a new macro or set the value of an existing macro.
 
Description
Creates a new macro definition or set the value of an existing macro
Return
Not applicable for this function
Return (Null)
Error: Too few arguments.
Syntax
DefineMacro(macroName, value [, desc][, type])
Parameters
This function has the following parameters:
macroName - String that defines macro macname to the value parameter. If a string literal, enclose in quotation marks.
value - Stores a value. If a string literal, enclose in quotation marks.
desc - String that contains the description of the macro. If a string literal, enclose in quotation marks.
type - The numeric value is used as the 'type' of the macro.
Remarks
 
See Also
Example
Create a new macro definition called "mymacro" (or set the value of existing macro to 100):
DefineMacro("mymacro", "100")
DeleteMessage Function
 
Description
Deletes a message object.
Return
Not applicable for this function.
Return (Null)
Not applicable for this function.
Syntax
DeleteMessage(messageName [, errNotFound] )
Parameters
This function has the following parameters:
messageName - tring that contains the name of the message object to delete. If a string literal, enclose in quotation marks.
errNotFound - Boolean setting that determines whether to generate a run-time error if the specified message is not found.
Remarks
Deletes a message object to reclaim the memory used by the object.
See Also
Example
Delete a message object called mymsg:
DeleteMessage("mymsg")
Discard Function
 
Description
Discards a record from a transformation
Return
Not applicable for this function
Return (Null)
No error.
Syntax
Discard([booleanExpression])
Parameters
This function has the following parameters:
booleanExpression - Optional. A boolean or numeric value that determines whether to execute the function.
If this parameter is set to True or any nonzero integer, the function discards the current record.
If this parameter is set to False or 0, the function is not executed
If no value is provided, the function defaults to True.
Remarks
Use Discard to leave a record out of a transformation. Discarding is the same as rejecting, except that discarded records are not written anywhere.
Both the Reject and Discard functions take an optional numeric argument.
See Also
Examples
The following examples show two ways to discard records where the value in the ("Account No") field is greater than 10025:
Discard(FieldAt("/SOURCE/R1/Account No") > 10025)
If FieldAt("/SOURCE/R1/Account No") > 10025 Then
  Discard()
Else
  FieldAt("/SOURCE/R1/Account No")
End If
Environ Function
 
Description
Returns the string associated with an operating system environment variable
Return
Value of the specified environment variable
Return (Null)
Error: Incorrect argument count
Syntax
Environ(envString|number)
Parameters
This function has the following parameters:
envString - String expression containing the name of an environment variable.
number - Numeric expression corresponding to the numeric order of the environment string in the environment-string table. The number argument can be any numeric expression, but is rounded to a whole number before it is evaluated.
Remarks
If envString cannot be found in the environment-string table, a zero-length string ("") is returned. Otherwise, Environ returns the text assigned to the specified envString; that is, the text following the equal sign (=) in the environment-string table for that environment variable.
If you specify number, the string occupying that numeric position in the environment-string table is returned. In this case, Environ returns all of the text, including envString. If there is no environment string in the specified position, Environ returns a zero-length string.
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
Example
If you specify 1 as the number parameter, the Environ function returns the string occupying the first position in the environment string table:
Environ(1)
Eval Function
 
Description
Evaluates a string expression
Return
Value of a supplied string expression
Return (Null)
Error: Incorrect argument count
Syntax
Eval(expression)
Parameters
This function has the following parameters:
expression - Any string expression. If a string literal, enclose in quotation marks.
Remarks
Use this function to evaluate a string as if the string were an actual expression.
This function may return a compile error.
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also
Example
The following example returns 50:
Eval("20 + 30")
You can also use field values. This example will return multiplication of numbers present in source field "Field1" and "Field2".
Eval(FieldAt("/SOURCE_1/R1/Field1") * FieldAt("/SOURCE_1/R1/Field2"))
Exp Function
 
Description
Raises e (the base of natural logarithms) to the Nth power
Return
The constant e raised to the Nth power
Return (Null)
Error: Incorrect argument count.
Syntax
Exp(number)
Parameters
This function has the following parameters:
number - Any valid numeric expression greater than zero and less than 709.782712893.
Remarks
The constant e is approximately 2.718282. This function complements the action of the Log function and is sometimes referred to as the antilogarithm.
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also
Example
This example returns source ("Field1") as a Double data type, specifying e (the base of natural logarithms) raised to a power:
Exp(FieldAt("/SOURCE/R1/Field1"))
Extract Function
 
Description
Extracts a specified character or string
Return
All alpha, all numeric, or alphanumeric strings from a supplied string
Return (Null)
Error: Incorrect argument count.
Syntax
Extract ("string", "what")
Parameters
This function has the following parameters:
string - The source string.
what - String "a" to extract alpha characters, "n" to extract numeric characters (digits), or "an" to extract alphanumeric.
Remarks
If the what parameter gives the wrong value, a type mismatch error occurs (i.e., if an "a" string is written to extract alpha characters and the source string is numeric, etc.)
Be careful to observe case when using a function that performs a string-search. String-search Functions are case-sensitive, literal searches. A Function that searches for "x" disregards "X". If the function has an optional argument "compare", and you do not use the optional compare argument, the expression is case-sensitive by default.
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.The return is a concatenated string.
Examples
Extract("This is example 437.", "a")
'Returns: Thisisexample
Extract("This is example 437.", "n")
'Returns: 437
Extract("This is example 437.", "an")
'Returns: Thisisexample437
FieldAt Function
 
Description
Translates a field address to a DJField object reference
Return
DJField object
Return (Null)
 
Syntax
FieldAt(path)
Parameters
This function has the following parameters:
path - Path to a DJField object.
Remarks
You can use any of the DJField variable properties with FieldAt, as follows:
FieldAt("/SOURCE/R1/fieldname").property
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also
Example
This example returns the value of Field3:
FieldAt("/SOURCE/R1/Field3").Value
FileAppend Function
 
Description
Appends a string to an existing file and creates a new file if it does not exist
Return
Number of characters written to the file
Return (Null)
Error: Too few arguments.
Syntax
FileAppend(filename,string[,encoding])
Parameters
This function has the following parameters:
filename (required) - A string containing the name of the file to append. If a string literal, enclose in quotation marks.
string (required) - The string to append to the file. If a string literal, enclose in quotation marks.
encoding (optional) - The encoding you want to use on the string to be appended.
For a list of all of the encoding constants available for this parameter, see Constant Values for Encoding Parameters in EZscript.
Remarks
The difference between this function and FileWrite: FileWrite always overwrites an existing file, while FileAppend creates a new file, or appends to an existing file.
See Also
Example
This function creates a new file (if tutor1.asc does not exist) or append a string to an existing file (if tutor1.asc exists). This expression appends the string "Add new text" to the end of the file:
FileAppend("C:\tutor1.asc", "Add new text")
FileCopy Function
 
Description
Copies the contents of the source file to the target file
Return
Not applicable for this function
Return (Null)
Error: Incorrect argument count.
Syntax
FileCopy(srcfile,trgfile)
Parameters
This function has the following parameters:
srcfile (required) - A string containing the name of the source file. If a string literal, enclose in quotation marks.
trgfile (required) - A string containing the name of the target file. If a string literal, enclose in quotation marks.
Remarks
A "File not found" exception error occurs if the source file does not exist. A "File access" exception error also occurs if the file is in use, if there are permissions to create the file, or if there is already a file named trgfile.
See Also
Example
Make a copy of output.xls (an existing file) and name it output.bak:
FileCopy("C:\output.xls", "C:\output.bak")
FileDateTime Function
 
Description
File date was last modified or date created
Return
Variant (Date) and time that file was created or last modified
Return (Null)
Error: Incorrect argument count.
Syntax
FileDateTime(pathname)
Parameters
This function has the following parameters:
pathname (required) - A string expression that specifies a file name. If a string literal, enclose in quotation marks.
Note:  Use MacroExpand() while using macros to pass the parameters.
Remarks
Include the directory and drive in the path name string (see example below).
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also
Example
FileDateTime("C:\testfile.asc")
FileDelete Function
 
Description
Deletes the specified file
Return
Not applicable for this function
Return (Null)
Error: Incorrect argument count
Syntax
FileDelete(filename)
Parameters
This function has the following parameters:
filename (required) - A string containing the name of the file to delete. If a string literal, enclose in quotation marks.
Note:  Use MacroExpand() while using macros to pass the parameters.
Remarks
A "File not found" exception error occurs if the file does not exist. A "File access" exception error occurs if the file is in use or if there are no permissions to delete the file.
See Also
Example
FileDelete("C:\temp.log")
FileExists Function
 
Description
Tests for the existence of a file
Return
True (-1) if the specified file name exists
False (0) if the file does not exist
Return (Null)
 
Syntax
FileExists(filename)
Parameters
This function has the following parameters:
filename (required) - A string containing the name of the file to be found. A complete path specification (either absolute or relative) must be provided if the file is not expected to exist in the current directory. If a string literal, enclose in quotation marks.
Note:  Use MacroExpand() while using macros to pass the parameters.
Remarks
The IsFile Function and IsDirectory Function check for three possible conditions (True, False, Error). Use the FileExists function first if you want to check for a file or directory without the possibility of returning an error. If it exists, you can then test with IsFile or IsDirectory.
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also
Example
FileExists("C:\temp.log")
FileLen Function
 
Description
File length (bytes) as a Long
Return
Long specifying the length of a file in bytes
Return (Null)
Error: Incorrect argument count.
Syntax
FileLen(pathname)
Parameters
This function has the following parameters:
pathname (required) - A string expression that specifies a file. The path name may include the directory and the drive. If a string literal, enclose in quotation marks.
Note:  Use MacroExpand() while using macros to pass the parameters.
Remarks
If the specified file is open when the FileLen function is called, the value returned represents the size of the file immediately before it was opened.
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also
Example
This expression returns 11455 (11,455 bytes, 11.1kb):
FileLen("C:\Tutor1.asc")
FileList Function
 
Description
Populates an array with the names of files matching the prototype. Returns the number of matching files.
Return
Number of files in the results list
Return (Null)
Error: Incorrect argument count.
Syntax
FileList(prototype, results)
Parameters
This function has the following parameters:
prototype (required) - A string used as a pattern for matching file names. The string can contain literal characters and the metacharacters '*' and '?'. If a string literal, enclose in quotation marks.
results (required) - An array variable for storing the list of matching file names.
Note:  Use MacroExpand() while using macros to pass the parameters.
Remarks
Returns a 'Type Mismatch' error if the results parameter is not an array.
The FileList function returns both file and directory names within a given directory. If you want to work only with file names, you must test the return names using the IsFile function to determine which files to use.
Note:  The Option Base statement has no effect on the FileList Function. The FileList Function always sets the array lower bound to 0.
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also
Examples
Return a list of all of the script files in the current directory:
Dim file_list()
FileList("*.ezscript", file_list)
 
Returns a list of files in a specified directory:
Dim file_list()
FileList("C:\path_to_files*.ext", file_list)
 
Return the number of files in a specified prototype:
Dim file_list()
FileList(MacroExpand("$(FILE_PATH)*.ext"), file_list)
 
Note:  The file_list array can further be used for the required operations.
FileRead Function
 
Description
Returns a string containing the contents of a file.
Return
Contents of the specified file.
Return (Null)
Error: Too few arguments.
Syntax
FileRead(filename [,encoding] [,password])
Parameters
This function has the following parameters:
filename (required) - Name of file to read. If a string literal, enclose in quotation marks.
encoding (optional) - The encoding you want to use on the file to be read.
For a list of the encoding constants available for this parameter, see Constant Values for Encoding Parameters in EZscript.
password (optional) - For password-protected .jar or .zip file.
Remarks
The filename parameter must include the entire path name unless you specify a relative path (the file resides in the current directory).
You can work with files using the Internet with URI support on File functions.
Caution
This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also
Example
This expression reads the file tutor1.asc:
FileRead("C:\tutor1.asc")
FileReadLine Function
 
Description
Reads a user specified line from a file
Return
One user-specified line from a file
Return (Null)
Error: Too few arguments
Syntax
FileReadLine(filename,line [,linesep] [,encoding])
Parameters
This function has the following parameters:
filename (required) - Name of file to read with full path. If a string literal, enclose in quotation marks.
line (required) - The line number to read (beginning with 1 as the first line). If the line number specified is less than 1, it is treated as set to 1.
linesep (optional) - The line separator that identifies the end of each line in the file.
By default, the line separator (if not included in the argument list) is CR-LF (0x0d 0x0a) on Windows systems, and LF (0x0a) on Unix systems.
encoding (optional) - The encoding to use on the file to be read. The encoding default is ISO8859-1.
For a list of all of the encoding constants available for this parameter, see Constant Values for Encoding Parameters in EZscript.
Remarks
The filename parameter must include the entire path to the file, unless you specify a relative path (the file resides in the current directory).
The returned line does not include the line separator.
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.Generates a TYPEMISMATCH error if the line separator is empty or the encoding is invalid.
Generates a FILEOPEN error if the file cannot be opened.
Generates an EOF error if the end of file is reached before the specified line is found.
See Also
Example
Using a file named "thisfile.txt" as source, with the following content:
"Jones, Mary,1234 First St."
"Smith, Joe, 910 11th Ave."
"New, Mitch, 456 Tequey"
The following expression returns: "New, Mitch, 456 Tequey":
FileReadLine("C:\thisfile.txt", 3)
FileRename Function
 
Description
Moves files or a directory from one location to another location. If both filenames are in the same location, it results in file rename. Otherwise, the file content is copied to the new location and the old file is removed.
Return
Not applicable for this function
Return (Null)
Error: Incorrect argument count.
Syntax
FileRename(oldname, newname)
Parameters
This function has the following parameters:
oldname - String containing old name of file. If a string literal, enclose in quotation marks.
newname - String containing the new name of the file. If a string literal, enclose in quotation marks.
Note:  Use MacroExpand() while using macros to pass the parameters.
Remarks
A "File not found" exception error occurs if the file does not exist. A "File access" exception error occurs if the file is in use, if there are no permissions to rename the file, or if the newname file already exists.
A directory cannot be renamed across different drives (as in from drive C to drive D).
A file or directory cannot be renamed from one platform to another (from local PC to server).
See Also
Example
FileRename("C:\output.xls", "C:\output.bak")
FileWrite Function
 
Description
Writes a string to a file. Overwrites an existing file. The function's behavior is similar to that of the Replace Output mode in the Map window.
Return
Number of characters written
Return (Null)
Error: Incorrect argument count
Syntax
FileWrite(file, string, encoding)
Parameters
This function has the following parameters:
filename (required) - A string containing the name of the file to write. If a string literal, enclose in quotation marks.
string (required) - A string containing the data to write to the file. If a string literal, enclose in quotation marks.
encoding (optional) - The encoding you want to use on the value to be written.
For a list of all of the Encoding Constants available for this parameter, see Constant Values for Encoding Parameters in EZscript.
Remarks
The difference between FileAppend function and this function is that FileWrite always overwrites an existing file. FileAppend creates a new file, or appends to an existing file.
You can work with files using the Internet with URI support on File functions.
See Also
Example
This example writes a date to the file Tutor1:
FileWrite("C:\Tutor1.asc", "07/13/2004")
Filter Function
 
Description
Searches an array for string match
Return
Zero-based array containing subset of a string array based on a specified filter criteria
Return (Null)
Error: Invalid expression syntax.
Syntax
Filter(sourceArray,match[, include][, compare])
Parameters
This function has the following parameters:
sourceArray (required) - One-dimensional array of strings to be searched. If a string literal, enclose in quotation marks.
match (required) - Search string.
include (optional) - Boolean value indicating whether to return substrings that include or exclude match. If include is True, Filter returns the subset of the array that contains match as a substring. If include is False, Filter returns the subset of the array that does not contain match as a substring.
compare (optional) - Numeric value indicating the kind of string comparison to use. A value of 0 performs a binary comparison (default). A value of 1 performs a textual comparison.
Remarks
If no matches of match are found within sourceArray, Filter returns an empty array. An error occurs if sourceArray is null or is not a one-dimensional array.
The array returned by the Filter function contains only enough elements to contain the number of matched items.
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
Example
myarray = Split("Dish Dist dishes Distribution Distributor", " ")
resultarray = Filter(myarray, "Dist", 1)
Print("result array:'" & join(resultarray,"' '") & "'")
'Returns: 'Dist Distribution Distributor'
FindMessage Function
 
Description
Searches for a DJMessage object
Return
Reference to the DJMessage object
Return (Null)
Error: Invalid expression syntax.
Syntax
FindMessage(messageName)
Parameters
This function has the following parameters:
messageName (required) - The name of the message to find. If a string literal, enclose in quotation marks.
Remarks
If the message is not found, it returns nothing.
This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also
Example
You have a process with a map step that puts its output into a DJMessage object using the URI djmessage:///output1.
In this case, you can look up and use the message in an expression step:
Dim msgout As DJMessage
Set msgout = FindMessage("output1")
If msgout Is Nothing Then
  LogMessage("INFO","Can't find the message output1")
Else
  LogMessage("INFO","Message output1 has " & Len(msgout.Body) & " characters")
End If
Fix Function
 
Description
Find the integer part of a number
Return
Integer portion of a number.
Return (Null)
Error: Invalid expression syntax.
Syntax
Fix(number)
Parameters
This function has the following parameters:
number - Any valid numeric expression.
Remarks
This function removes the fractional part of number and returns the resulting integer value. If number is negative, Fix returns the first negative integer greater than or equal to number. The data type of the return value is the same as that of the number argument.
The Int function and Fix function behave identically unless you are working with negative numbers. The Int function rounds to the lower negative integer while the Fix function rounds to the higher one.
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also
Examples
The following example converts -98.6 to -98:
Fix(-98.6)
The following example applies the Fix function to each source field "Field1":
Fix(FieldAt("/SOURCE/R1/Field1"))
FlushLog Function
 
Description
Allows you to flush the log to disk anytime you choose
Return
Not applicable for this function
Return (Null)
Not applicable for this function
Syntax
FlushLog()
Parameters
No parameter is required; however, FlushLog must be followed by open and close parentheses ().
Remarks
If while processing an expression you want to log a warning message and want to make sure that it was written to disk, you can flush the log after logging the warning. This immediately writes the log from the memory buffer to the disk.
This function does not clear messages.
During execution, the log is written to the job log directory at <job working directory>/log/ec. The file name is <job ID>.log.
Use FlushLog() to write the log just in case the map or process fails, or if you need to read from the log file in a subsequent process step. FlushLog() enables you to preserve the activity log up to that point.
Example
'The following script flushes the log and then reads it back into a variable
FlushLog() 'write the log to disk immediately
Dim theLogFile 'create new variable
'The next line uses the JOB_ID global macro to determine the name of the job log file
theLogFile = FileRead(MacroExpand("log/ec/$(com.pervasive.di.execution.JOB_ID).log"))
Format Function
 
Description
Formats a number, date, time, or string according to instructions contained in a format expression.
Return
Data in user-specified format. See Symbol below.
Return (Null)
Error: Incorrect argument count
Syntax
Format(expression [,fmt])
Parameters
Format() returns a String. This function uses these parameters:
expression - Numeric or string expression to be formatted. If a string literal, enclose in quotation marks.
fmt - Format expression, a string of display-format characters that specify how the expression is to be displayed. Different types of format expressions (numeric, date/time, or string) can be mixed in a single fmt argument.
If fmt is omitted or is zero-length and expression is a numeric expression, Format() provides the same functionality as the Str() function by converting the numeric value to the appropriate return data type. Positive numbers converted to strings using Format () lack the leading space reserved for displaying the sign of the value, whereas those converted using Str() retain the leading spaces.
You can use any of the symbols listed below to create a format expression for numbers.
Remarks
Caution!  This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also
Examples
Formats today's date as a complete date (including day, month, and year) according to the Short Date setting in the International section of the Windows Control Panel. If today's date is January 14, 2004, the result is 1/14/04:
Format(Now(),"ddddd")
Formats a 10-digit phone number.
'First, use the GSub Function to take out all nonalphanumeric characters, such as parentheses and dashes.
'GSub works well if you have data entered in different formats.
A=GSub("[^A-Za-z0-9n]","",FieldAt("Field1"))
'Next, use Format to put the 10 digits into a standard (@@@)- @@@-@@@@ format.
Format(Trim(A),"(@@@) @@@-@@@@")
Symbols for Formatting Numbers
 
Symbol
Description
Null String
Displays the number with no formatting.
0
Digit placeholder - Displays a digit or a zero. If there is a digit in the expression being formatted in the position where the 0 appears in the format string, displays it; otherwise, displays a zero in that position.
#
Digit placeholder - Displays a digit or nothing. If there is a digit in the expression being formatted in the position where the # appears in the format string, display it; otherwise, display nothing in that position.
. (period)
Decimal placeholder - Determines how many digits are displayed to the left and right of the decimal separator. If the format expression contains only number sign to the left of this symbol, numbers smaller than 1 begin with a decimal separator. If you want a leading zero to always be displayed with fractional numbers, use 0 as the first digit placeholder to the left of the decimal separator instead. The actual character used as a decimal placeholder in the formatted output depends on the Number Format specified in the International section of the Microsoft Windows Control Panel. For some countries, a comma is used as the decimal separator.
%
Percentage placeholder - Expression multiplied by 100. The percent character (%) is inserted in the position where it appears in the format string.
, (comma)
Thousands separator - Separates thousands from hundreds within a number that has four or more places to the left of the decimal separator. Standard use if the thousand separator is specified if the format contains a comma surrounded by digit placeholders (0 or #). Two adjacent commas or a comma immediately to the left of the decimal separator means scale the number by dividing it by 1000, rounded as needed. You may scale large numbers using this technique. For example, you may use the format string "##0", to represent 100 million as 100. Numbers smaller than 1 million are displayed as 0. The actual character used as the thousand separator in the formatted output depends on the Number Format specified in the International section of the Control Panel. For some countries, a period is used as the thousand separator
E- E+ e- e+
Scientific format - If the format expression contains at least one digit placeholder (0 or #) to the right of E-, E+, e-, or e+, the number is displayed in scientific format and E or e is inserted between the number and its exponent. The number of digit placeholders to the right determines the number of digits in the exponent. Use E- or e- to place a minus sign next to negative exponents. Use E+ or e+ to place a minus sign next to negative exponents and a plus sign next to positive exponents.
: (colon)
Time separator - Separates hours, minutes, and seconds when time values are formatted. The actual character used as the time separator depends on the Time Format specified in the International section of the Control Panel.
/
Date Separator - Separates the day, month, and year when date values are formatted. The actual character used as the date separator in the formatted output depends upon the Date Format specified in the Windows Control Panel in Regional and Language Options.
+ $ ( ) space
Displays a literal character. To display a character other than one of those listed, precede it with a backslash ( \ ) or enclose it in double quotation marks (" ").
\
Displays the next character in the format string, Many characters in the format expression have a special meaning and cannot be displayed as literal characters unless they are preceded by a backslash. The backslash itself is not displayed. Using a backslash is the same as enclosing the next character in double quotation marks. To display a backslash, use two backslashes (
). Examples of characters that cannot be displayed as literal characters are the date- and time-formatting characters (a, c, d, h, m, n, p, q, s, t, w, y, /, and :), the numeric-formatting characters (#, 0, %, E, e, comma, and period),
"ABC"
Displays the string inside the quotation marks
*
Displays the next character as the fill character. Any empty space in a field is filled with the character following the asterisk.
This table outlines how the formats are applied and contains several examples.
If You Use...
The Result Is...
One Section Only
The format applies to all values.
Two Sections
The first section applies to positive values and zeros, the second section applies to negative values.
Three Sections
The first section applies to positive values, the second section applies to negative values, and the third section applies to zeros.
Four Sections
The first section applies to positive values, the second section applies to negative values, the third section applies to zeros, and the fourth section applies to null values.
Note:  Unless the fmt argument contains one of the named formats, a format expression for numbers can have one to four sections separated by semicolons.
Some format examples for numbers are shown below using a positive integer, negative integer, and positive decimal. These examples assume the International Country code is set for the United States. The first column contains specific format strings. The other columns contain the output that results if the formatted data has the value given in the column heading.
Format (fmt)
Positive 5
Negative 5
Decimal 0.5
Null string
5
-5
0.5
0
5
-5
1
0.00
5.00
-5.00
0.50
#,##0
5
-5
1
#,##0.00;;;Nil
5.00
-5.00
0.50
$#,##0;($#,##0)
$5
($5)
$1
$#,##0.00;($#,##0.00)
$5.00
($5.00)
$0.50
0%
500%
-500%
50%
0.00%
500.00%
-500.00%
50.00%
0.00E+00
5.00E+00
-5.00E+00
5.00E-01
0.00E-00
5.00E00
-5.00E00
5.00E-01
Symbols for Formatting Dates and Times
You may format date and time serial numbers using date and time or number formats (because date/time serial numbers are stored as floating-point values.) You may use any of the following symbols to create a format expression for dates and times:
Symbol
Description
c
Displays the date as ddddd and display the time as t t t t t, in that order. Only date information is displayed if there is no fractional part to the date serial number; only time information is displayed if there is no integer portion.
d
Displays the day as a number without a leading zero (1 - 31).
dd
Displays the day as a number with a leading zero (01 - 31).
ddd
Displays the day as an abbreviation (Sun - Sat).
dddd
Displays the day as a full name (Sunday - Saturday).
ddddd
Displays a date serial number as a complete date (including day, month, and year) formatted according to the Short Date setting in the International section of the Windows Control Panel. The default Short Date format is m/d/yy.
dddddd
Displays a date serial number as a complete date (including day, month, and year) formatted according to the Long Date setting in the International section of the Windows Control Panel. The default Long Date format is mmmm dd, yyyy.
w
Displays the day of the week as a number (1 for Sunday through 7 for Saturday).
ww
Displays the week of the year as a number (1 - 53).
m
Displays the month as a number without a leading zero (1-12). If m immediately follows h or hh, the minute rather than the month is displayed.
mm
Displays the month as a number with a leading zero (01-12). If m immediately follows h or hh, the minute rather than the month is displayed.
mmm
Displays the month as an abbreviation (Jan - Dec).
mmmm
Displays the month as a full month name (January - December).
q
Displays the quarter of the year as a number (1-4).
y
Displays the day of the year as a number (1-366).
yy
Displays the year as a two-digit number (00-99).
yyyy
Displays the year as a four-digit number (100-9999).
h
Displays the hour as a number without leading zeros (0 - 23).
hh
Displays the hour as a number with leading zeros (00 - 23).
n
Displays the minute as a number without leading zeros (0 - 59).
nn
Displays the minute as a number with leading zeros (00 - 59).
s
Displays the seconds as a number without leading zeros (0 - 59).
ss
Displays the seconds as a number with leading zeros (00 - 59).
ttttt
Displays a time serial number as a complete time (including hour, minute, and seconds) formatted using the time separator defined by the Time Format in the International section of the Windows Control Panel. A leading zero is displayed if the Leading Zero option is selected and the time is before 10:00 A.M. or P.M. The default time format is h:nn:ss.
AM/PM
Uses the 12-hour clock and displays an uppercase AM for any hour before noon; displays an uppercase PM for any hour between noon and 11:59 PM.
am/pm
Uses the 12-hour clock and displays a lowercase AM for any hour before noon; displays a lowercase PM for any hour between noon and 11:59 PM.
A/P
Uses the 12-hour clock and displays an uppercase A for any hour before noon; displays an uppercase P for any hour between noon and 11:59 PM.
a/p
Uses the 12-hour clock and displays a lowercase A for any hour before noon; displays a lowercase P for any hour between noon and 11:59 PM.
AMPM
Uses the 12-hour clock and displays the contents of the 1159 string (s1159) in the WIN.INI file for any hour before noon; displays the contents of the 2359 (s2359) for any hour between noon and 11:59 PM. AMPM can be either uppercase or lowercase, but the case of the string displayed matches the string as it exists in the WIN.INI file. The default format is AM/PM.
Symbols for Formatting Strings
Use any of the following symbols to create a format expression for strings.
Symbol
Meaning
@
Character placeholder - Displays a character or a space. If there is a character in the string being formatted in the position where the @ appears in the format string, display it; otherwise, display a space in that position. Placeholders are filled from right to left unless there is an ! character in the format string. See below.
&
Character placeholder - Displays a character in the string being formatted in the position where the & appears, display it; otherwise, display nothing. Placeholders are filled from right to left unless there is an ! character in the format string. See below.
<
Force lowercase – All characters are displayed in lowercase format.
>
Force uppercase – All characters are displayed in uppercase format.
!
Force placeholders to fill from left to right, instead of from right to left.
Last modified date: 06/13/2022