User Guide : Scripting : Script Functions : Script Functions A-F

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: 2147483647Caution!  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)

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
"PRD" places the pre-directional in the Direction field
Number
"NU" places the street number in the Number field
Street
"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.

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.
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 Chr Function 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 returnB64Decode("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.CautionThis 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 MismatchNote:  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 NULLError: Type MismatchNote:  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 Parse Function 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.CautionThis 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 Add1Fmt Function 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 GetObject Function Example Dim xlApp As ObjectSet xlApp = CreateObject("Excel.Application")xlApp.Application.Workbooks.Open "c:\tutor1.xls"xlApp.visible = Truex = xlApp.Application.Cells(1,1).ValueFileWrite("X: " & x)"This is from Excel Cell (1,1): " & xxlApp.QuitSet xlApp = NothingReturnEnd 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()

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)

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.FormatExampleResult (yyyymmdd)• mm-dd-yyyy07-12-199419940712• mm-dd-yy12-03-9419941203• mm-dd11-2519941125• mm/dd/yyyy01/01/195519550101 • mm/dd/yy09/23/4719470923• mm/dd08/0319940803• dd-mon-yyyy12-Jun-198319830612• dd-mon-yy15-Mar-0119010315• dd-mon30-Sep19940930• dd-month-yyyy31-October-231523151031• dd-month-yy10-August-5819580810• dd-month01-April19940401• month dd, yyyySeptember 26, 197919790926• mm.dd.yyyy09.26.197919790926
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()) + 10The 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")

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: ThisisexampleExtract("This is example 437.", "n")'Returns: 437Extract("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 existsFalse (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 FileDateTime Function 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.

 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.CautionThis 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")

 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 DJMessage Object Type 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 DJMessageSet 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 /log/ec. The file name is .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 variableFlushLog() 'write the log to disk immediatelyDim theLogFile 'create new variable'The next line uses the JOB_ID global macro to determine the name of the job log filetheLogFile = 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.