Returns the absolute value of a numeric value

Absolute value of the number referred in the expressions

Error: Incorrect argument count

Abs(number)

This function has the following parameters:

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:

Fix Function, Int Function, and Sgn Function

Each of the following expressions evaluate the value to 33:

Formats (cleans) street address data to USPS standards

Address field string formatted according to Post Office standards (U.S.)

Error: Incorrect argument count.

Add1Fmt(string)

This function has the following parameters:

Example: Add1Fmt("12 Hall St")

Add1Part Function, Add2Part Function, NamePart Function, Parse Function

The following example displays how to format the data in the source field Address1 to USPS standards:

Applied to a field containing:

Returns:

Splits (parses) a street address field

Address parts, such as number, street name, suffix, etc., to a different field or fields

Error: Incorrect argument count.

Add1Part(part,string)

This function has the following parameters. If the string is a string literal, enclose the string in quotation marks.

Example: Add1Part("NU", FieldAt("/SOURCE/R1/Address1"))

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.

Add1Fmt Function, Add2Part Function, NamePart Function, Parse Function

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:

Applied to a Address1 containing:

Returns:

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

Splits (parses) an address field

Address parts, such as city, state, ZIP, to a different field or fields

Error: Incorrect argument count.

Add2Part(part,string)

This function has the following parameters.

Example: Add2Part("c" "Austin, TX, 78727")

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.

Add1Fmt Function, Add1Part Function, NamePart Function, Parse Function

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:

Applied to Address2 containing:

Returns:

City

"c" places the city in the City field

State

"s" places the state in the State field

ZIP

"z" places the zip code in the ZIP field

Finds the first character in a string.

ASCII code (not hexadecimal code) for the first character in a string

Error: Incorrect argument count.

Asc(string)

This function has the following parameters:

Example: Asc("string")

Locate the first letter of the source field "FirstName" and return the ASCII code for that character:

Converts a Base64 string

Binary value when supplied with a Base64 string

Error: Too few arguments.

B64Decode(src, [encoding])

This function has the following parameters:

To convert a string of binary values into Base64 format, use the B64 Encode function.

B64Encode Function, UTF8Encode Function, UTF8Decode Function

This expression converts "Field1" contents from Unicode UTF-8 encoding into its original format:

Another example with harcoded expression (encoded value) and it gives "DataConnect Engineering" as decoded value in return

Converts a string of binary values

Base64 format when supplied with binary values

Error: Too few arguments.

B64Encode(src, [encoding])

This function has the following parameters:

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.

B64Decode Function, UTF8Encode Function, UTF8Decode Function

This expression converts "Field1" into Base64 format using Unicode UTF-8 encoding (encodes string of values in Field1 into 8-bit characters):

Converts a decimal into a binary string

Equivalent binary string when supplied with a decimal value

Error: Incorrect argument count.

Bin(number)

This function has the following parameters:

Hex Function, Oct Function

The following expression returns 1001001:

Converts the result of a string, number, or expression to a Boolean data type.

Boolean data type when supplied with a string, number, or expression

Error: Invalid use of NULL.

CBool(string)

This function has the following parameters:

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.

CByte Function, CDate Function, CDbl Function, CInt Function, CLng Function, and CSng Function

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.

Converts a number or numeric string into a Byte data type

Byte data type when supplied with a number or numeric string

Error: Invalid use of NULL.

CByte(number)

This function has the following parameters:

CBool Function, CDate Function, CDbl Function, CInt Function, CLng Function, and CSng Function

Converts the result of a valid date string or number to a Date data type

Date data type when supplied with a date string or number

Error: Invalid use of NULL.

CDate(datestring)

This function has the following parameters:

If the string is a string literal, enclose the datestring in quotation marks.

Example: CDate("datestring")

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.

CBool Function, CByte Function, CDbl Function, CInt Function, CLng Function, CSng Function, and DateValMask Function

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:

Converts the result of a number or numeric string to a Double data type

Double date type when supplied with a number or numeric string

Error: Invalid use of NULL.

Error: Type Mismatch

CDbl(number)

This function has the following parameters:

This function may throw errors for invalid characters, stack overflows, and stack underflows.

CBool Function, CByte Function, CDate Function, CInt Function, CLng Function, and CSng Function

This expression converts numeric strings in the source field ("Field1") into Double data types:

Converts the result of a number or numeric string to a Decimal data type

Decimal data type when supplied with a number or numeric string

Error: Invalid use of NULL

Error: Type Mismatch

CDec(number)

This function has the following parameters:

This function may throw an error for invalid input.

CBool Function, CByte Function, CDate Function, CInt Function, CLng Function, and CSng Function

Example 1:

This expression coerces numeric string in the source field ("Field1") to the Decimal data type:

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.

This expression returns 4611686014132420609 in Decimal data type.

Changes the current working directory from the original directory name to the new directory name

Not applicable for this function

Not applicable for this function

ChangeDirectory(directoryname)

This function has the following parameters:

Example: ChangeDirectory("directoryname")

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:

Enclose directory names and paths in quotation marks.

MakeDirectory Function and GetDirectory Function

This expression changes the current directory to TestFiles:

Converts a string from Unicode using the specified encoding

String value of a supplied Unicode value

Error: Incorrect argument count.

ChangeFromUnicode(string, encoding)

This function has the following parameters:

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.

ChangeToUnicode Function, Unicode (Fixed), Unicode (Delimited)

This expression changes the string "Test123" from Unicode to ANSI (default) encoding:

Converts a string to Unicode using the specified encoding

Unicode, when supplied with a string

Error: Incorrect argument count.

ChangeToUnicode(string, encoding)

This function has the following parameters:

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.

ChangeFromUnicode Function, Unicode (Fixed), Unicode (Delimited)

This expression changes the string "Test123" from ANSI to UTF-8 encoding:

Counts the occurrences of a particular character in a string

Number of occurrences of a particular character in a string

Error: Incorrect argument count

CharCount(character, string)

This function has the following parameters:

Example: CharCount("a", "string")

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.

This example returns the number of spaces in the first source field:

The name "John Thompson Reynolds" would return 2.

This example returns the number of slashes ( / ) in a source field named "Date":

The value "6/9/98" would return 2.

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.

"78765-2356" would return 2356.

Selects a value from a list of values

Value specified from a list of values of a specific type

Error: Too few arguments.

Choose(index, value1[, value2] . . .)

This function has the following parameters:

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.

IIf Function and Switch Function

The following example translates color codes in the ("Color") field to color names:

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:

Translates a single numeric character code to a Unicode character value.

Unicode equivalent of a one-character ASCII string

Error: Incorrect argument count.

When number is a decimal value:
Chr(number)

When number is a hexadecimal value:
Chr(&H41)

This function has the following parameters:

Chr(034) in the first example below represents literal quotation marks.

Asc Function and Val Function

To remove quotation marks in the source field named "FIELDNAME" write the following expression:

To get the character value of the hex number 41 use the following expression. (This returns "A" minus the quotes):

Coerces the result of a number or numeric string to an Integer data type

Integer data type equivalent of a supplied number or numeric string

Error: Invalid use of NULL.

CInt(number)

This function has the following parameters:

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.

CBool Function, CByte Function, CDate Function, CDbl Function, CLng Function, CSng Function, Fix Function, and Int Function

This expression returns 234 in an Integer data type:

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:

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.

Specified string with all non-printable characters removed

Clean(string)

This function has the following parameters:

Clean("string")

Clears the value that has been previously defined for a macro

Not applicable for this function

Error: Incorrect argument count.

ClearMacro(macroname)

This function has the following parameters:

Example: ClearMacro("macroname")

DefineMacro Function, IsMacroDefined Function, MacroExpand Function, and MacroValue Function

Clears the value has been previously defined for the macro named mymacro:

Convert the result of a numeric string to a Long data type

Long data type equivalent of a supplied number or numeric string

Error: Invalid use of NULL.

CLng(number)

This function has the following parameters:

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.

CBool Function, CByte Function, CDate Function, CDbl Function, CInt Function, CSng Function, Fix Function, and Int Function

This expression converts numeric strings in source ("Field1") into Long data types:

Example:

Compare two string arguments

Value that represents the result of a comparison of two string arguments

Error: Too few arguments for function

Compare(strexpr1, strexpr2 [,ignoreCase])

This function has the following parameters:

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.

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.

StrReplace Function and StrComp Function

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:

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

Converts the result of a number or numeric string to a Single data type

Single data type equivalent of a supplied number or numeric string

Error: Invalid use of NULL.

CSng(number)

This function has the following parameters:

CBool Function, CByte Function, CDate Function, CDbl Function, CInt Function, and CLng Function

This example converts numeric strings in source ("Field2") into Single data types:

Applied to a Field2 containing:

Returns: 1536.752635 in to single data type.

Converts a number or date expression to a String representation

Type string when supplied with string, long, double, decimal or date

Error: Invalid use of NULL.

CStr(expr)

This function has the following parameters:

If the parameter is not one of the types listed in Return above, a ERR_TYPEMISMATCH error is returned.

CBool Function, CByte Function, CDate Function, CInt Function, CLng Function, and CSng Function

This expression coerces decimals in the source field ("Field1") into a string data type:

This expression converts date into string data type and returned string can be used in SubString function.

This expression returns 28-02 in a String data type.

Requests the current directory path

String representing the current path

String representing the current path, current working directory

CurDir[(drive)]

This function has the following parameters:

Example: CurDir("string")

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.

Returns the current working directory on the C drive:

Requests the current system date

Current system date

Current system date

Date()

No required parameters, however the parentheses after the function name are required.

The Date function returns the date as a formatted string, formatted based on the current locale settings for the machine.

Format Function, Now Function, Time Function

This expression returns the current system date:

Add or subtract a time interval to or from a date value

Date representing original date with the interval added or subtracted

Error: Incorrect argument count.

DateAdd(interval, number, date)

This function has the following parameters:

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.

DateDiff Function, DatePart Function, Now Function

The following example shows the behavior the DateAdd function when months are added to a date:

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.

Changes the format of a date string

User-specified format of a supplied date string

Error: Incorrect argument count.

DateConvert("sourceFormat", "targetFormat", "sourceString")

This function has the following parameters:

For the format parameters, the following intervals are valid:

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.

DateValMask Function and DateValue Function

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:

The following example converts a string date of the format 19981214 to a date of the format 12/14/1998:

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:

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:

Determine the number of time intervals between two dates

Number of time intervals (i.e., day, week) between two supplied dates

Error: Incorrect argument count.

DateDiff("interval", "date1", "date2")

This function has the following parameters:

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.

DateAdd Function, DatePart Function, Now Function

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:

Extracts a specified part of a Date string

Part of a date based on a user-specified time interval

Error: Incorrect argument count.

DatePart("interval", "date")

The DatePart function uses the following parameters:

DateAdd Function, DateDiff Function, Day Function, Hour Function, Minute Function, Month Function, Now Function, Second Function, Weekday Function, Year Function

The following example writes the day of the year for source dates ("Date") to the target file:

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.

Constructs a date value

Date value from user-specified year, month, and day values

Error: Incorrect argument count.

DateSerial(year, month, day)

This function has the following parameters:

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.

DatePart Function, DateValue Function, Day Function, Month Function, Now Function, Weekday Function, Year Function

This example creates a serial date value for January 1, 2003:

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:

Converts formatted date strings into real date values based on a date edit mask

Date value from user-specified source date mask and supplied date string

Error: Incorrect argument count.

DateValMask(dateString, sourceMask)

This function has the following parameters:

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.

DateValue Function, DateConvert Function, Setting Up Picture Masks

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:

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"):

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:

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:

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:

Convert a character string representation of a date into a date value

Date value from user-specified date string

Error: Incorrect argument count.

DateValue("dateString")

This function has the following parameters:

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

DateAdd Function, DateDiff Function, DateSerial Function, DateValMask Function, Day Function, Month Function, Now Function, Weekday Function, Year Function, Trim Function, and Val Function

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:

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.

Extracts the day value

Day of the month from a supplied date value

Error: Incorrect argument count.

Day(dateString)

This function has the following parameters:

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.

DatePart Function, Hour Function, Minute Function, Month Function, Now Function, Second Function, Weekday Function, Year Function

This example returns the day of the month for the current date. (7/13/2000 returns "13" to the target:

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):

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:

Creates a new macro definition or set the value of an existing macro

Not applicable for this function

Error: Too few arguments.

DefineMacro(macroName, value [, desc][, type])

This function has the following parameters:

 

ClearMacro Function, IsMacroDefined Function, MacroExpand Function, MacroValue Function

Create a new macro definition called "mymacro" (or set the value of existing macro to 100):

Deletes a message object.

Not applicable for this function.

Not applicable for this function.

DeleteMessage(messageName [, errNotFound] )

This function has the following parameters:

Deletes a message object to reclaim the memory used by the object.

DJMessage Object Type, DJComponent Object Type, Using DJMessage and DJComponent Objects in Expressions

Delete a message object called mymsg:

Discards a record from a transformation

Not applicable for this function

No error.

Discard([booleanExpression])

This function has the following parameters:

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.

Stop Function and Reject Function

The following examples show two ways to discard records where the value in the ("Account No") field is greater than 10025:

Returns the string associated with an operating system environment variable

Value of the specified environment variable

Error: Incorrect argument count

Environ(envString|number)

This function has the following parameters:

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.

If you specify 1 as the number parameter, the Environ function returns the string occupying the first position in the environment string table:

Evaluates a string expression

Value of a supplied string expression

Error: Incorrect argument count

Eval(expression)

This function has the following parameters:

Use this function to evaluate a string as if the string were an actual expression.

This function may return a compile error.

Choose Function, IIf Function, Switch Function

The following example returns 50:

You can also use field values. This example will return multiplication of numbers present in source field "Field1" and "Field2".

Raises e (the base of natural logarithms) to the Nth power

The constant e raised to the Nth power

Error: Incorrect argument count.

Exp(number)

This function has the following parameters:

The constant e is approximately 2.718282. This function complements the action of the Log function and is sometimes referred to as the antilogarithm.

Log Function and Log10 Function

This example returns source ("Field1") as a Double data type, specifying e (the base of natural logarithms) raised to a power:

Extracts a specified character or string

All alpha, all numeric, or alphanumeric strings from a supplied string

Error: Incorrect argument count.

Extract ("string", "what")

This function has the following parameters:

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.

Translates a field address to a DJField object reference

DJField object

 

FieldAt(path)

This function has the following parameters:

You can use any of the DJField variable properties with FieldAt, as follows:

RecordAt Function and DJField Object Type

This example returns the value of Field3:

Appends a string to an existing file and creates a new file if it does not exist

Number of characters written to the file

Error: Too few arguments.

FileAppend(filename,string[,encoding])

This function has the following parameters:

For a list of all of the encoding constants available for this parameter, see Constant Values for Encoding Parameters in EZscript.

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.

FileCopy Function, FileDelete Function, FileList Function, FileRead Function, FileRename Function, and FileWrite Function

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:

Copies the contents of the source file to the target file

Not applicable for this function

Error: Incorrect argument count.

FileCopy(srcfile,trgfile)

This function has the following parameters:

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.

FieldAt Function, FileDelete Function, FileList Function, FileRead Function, FileRename Function and FileWrite Function

Make a copy of output.xls (an existing file) and name it output.bak:

Make a copy of data.txt (an existing file), compress it, and name it data.gz:

File date was last modified or date created

Variant (Date) and time that file was created or last modified

Error: Incorrect argument count.

FileDateTime(pathname)

This function has the following parameters:

Include the directory and drive in the path name string (see example below).

FieldAt Function, FileCopy Function, FileLen Function, FileList Function, FileRead Function, FileRename Function, and FileWrite Function

Deletes the specified file

Not applicable for this function

Error: Incorrect argument count

FileDelete(filename)

This function has the following parameters:

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.

FieldAt Function, FileCopy Function, FileList Function, FileRead Function, FileRename Function, and FileWrite Function

Tests for the existence of a file

True (-1) if the specified file name exists

False (0) if the file does not exist

 

FileExists(filename)

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.

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.

FileList Function, FileRead Function, FileRename Function and FileWrite Function

File length (bytes) as a Long

Long specifying the length of a file in bytes

Error: Incorrect argument count.

FileLen(pathname)

This function has the following parameters:

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.

This expression returns 11455 (11,455 bytes, 11.1kb):

Populates an array with the names of files matching the prototype. Returns the number of matching files.

Number of files in the results list

Error: Incorrect argument count.

FileList(prototype, results)

This function has the following parameters:

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.

IsFile Function, FieldAt Function, FileCopy Function, FileDelete Function, FileRead Function, FileRename Function and FileWrite Function

Return a list of all of the script files in the current directory:

Returns a list of files in a specified directory:

Return the number of files in a specified prototype:

Returns a string containing the contents of a file.

Contents of the specified file.

Error: Too few arguments.

FileRead(filename [,encoding] [,password])

This function has the following parameters:

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.

FieldAt Function, FileCopy Function, FileDelete Function, FileList Function, FileRename Function, FileWrite Function, FileReadLine Function

This expression reads the file tutor1.asc:

Reads a user specified line from a file

One user-specified line from a file

Error: Too few arguments

FileReadLine(filename,line [,linesep] [,encoding])

This function has the following parameters:

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.

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.

FieldAt Function, FileCopy Function, FileDelete Function, FileList Function, FileRename Function, FileWrite Function, FileRead Function

Using a file named "thisfile.txt" as source, with the following content:

The following expression returns: "New, Mitch, 456 Tequey":

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.

Not applicable for this function

Error: Incorrect argument count.

FileRename(oldname, newname)

This function has the following parameters:

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

FieldAt Function, FileCopy Function, FileDelete Function, FileList Function, FileRead Function, and FileWrite Function

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.

Number of characters written

Error: Incorrect argument count

FileWrite(file, string, encoding)

This function has the following parameters:

For a list of all of the Encoding Constants available for this parameter, see Constant Values for Encoding Parameters in EZscript.

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.

FieldAt Function, FileCopy Function, FileDelete Function, FileList Function, FileRead Function, and FileRename Function

This example writes a date to the file Tutor1:

Searches an array for string match

Zero-based array containing subset of a string array based on a specified filter criteria

Error: Invalid expression syntax.

Filter(sourceArray,match[, include][, compare])

This function has the following parameters:

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.

Searches for a DJMessage object

Reference to the DJMessage object

Error: Invalid expression syntax.

FindMessage(messageName)

This function has the following parameters:

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.

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:

Find the integer part of a number

Integer portion of a number.

Error: Invalid expression syntax.

Fix(number)

This function has the following parameters:

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.

Abs Function, Int Function, Sgn Function, CInt Function, and CLng Function

The following example converts -98.6 to -98:

The following example applies the Fix function to each source field "Field1":

Allows you to flush the log to disk anytime you choose

Not applicable for this function

Not applicable for this function

FlushLog()

No parameter is required; however, FlushLog must be followed by open and close parentheses ().

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.

Formats a number, date, time, or string according to instructions contained in a format expression.

Data in user-specified format. See Symbol below.

Error: Incorrect argument count

Format(expression [,fmt])

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.

You can use any of the symbols listed below to create a format expression for numbers.

and GSub Function

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:

Formats a 10-digit phone number.

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.

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.

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

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.

@

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.