Data Transformation Functions

This article describes Transformation functions supported by Visual Importer ETL (all versions), VImpX , Data Exchange Wizard and DEWizardX.

String Functions

Trim

Trim(String):string
Trims leading and trailing spaces from a string.

TrimLeft

TrimLeft(String):string
Trims leading spaces from a string.

TrimRight

TrimRight(String):string
Trims trailing spaces from a string.

UpperCase

UpperCase(String):string
UpperCase returns a string with the same text as the string passed in, but with all letters converted to Uppercase

LowerCase

LowerCase(String):string
LowerCase returns a string with the same text as the string passed in, but with all letters converted to LowerCase

Replace

Replace(String,OldPattern,NewPattern):string
Replace replaces all occurrences of the OldPattern by NewPattern within the String

SubString

SubString(String,Index,Count):string
SubString returns a substring containing Count characters or elements starting from Index.

RightString

RightString(String,Count):string
RightString returns the trailing characters of String up to a length of Count characters

LeftString

LeftString(String,Count):string
LeftString returns the leading characters of String up to a length of Count characters

AddCharLeft

AddCharLeft(Char,String,Count):string
AddCharLeft returns a string left-padded to Length with characters Char

AddCharRight

AddCharRight(Char,String,Count):string
AddCharRight returns a string right-padded to Length with characters Char

MakeString

MakeString(Char,Count):string
MakeString returns a string of Count filled with character Char.

DelSpaces

DelSpaces(String):string
DelSpaces returns string with all spaces deleted except one.

"two spaces"->"two spaces

Delete

Delete(String,Index,Count):String
DelSpaces returns string with count characters deleted starting from index.

Insert

Insert(Source,S,Index):String
Insert returns string with s string inserted in index.

ProperCase

ProperCase(String):string
ProperCase returns string, with the first letter of each word in uppercase and all other letters in lowercase "proper case"->"Proper Case"

Numeric Functions

Abs

Abs(Integer):Integer
Abs returns the absolute value of the argument

Round

Round(Float,Integer):Float
Use Round to round Value to a specified power of ten.

The following examples illustrate the use of Round:

Expression Value

Round(1234567, 3) 1234000 Round(1.234, -2) 1.23 Round(1.235, -2) 1.24 Round(1.245, -2) 1.24

Sign

Sign(Integer):Integer

Use Sign to test the sign of a numeric value.

Sign returns

0 if AValue is zero. 1 if AValue is greater than zero. -1 if AValue is less than zero.

Date Functions

Day

Day(Date,Format):Integer.
Use Day to get the day part of a date value. Day('01012003','DDMMYYYY')

Hour

Hour(Date,Format):Integer.
Use Hour to get the hour part of a date value. Hour('01012003','DDMMYYYY')

Minute

Minute(Date,Format):Integer.
Use Minute to get the minute part of a date value. Minute('01012003','DDMMYYYY')

Month

Month(Date,Format):Integer.
Use Month to get the month part of a date value. Month('01012003','DDMMYYYY')

Second

Second(Date,Format):Integer.
Use Second to get the second part of a date value. Second('01012003','DDMMYYYY')

Year

Year(Date,Format):Integer.
Use Year to get the year part of a date value. Year('01012003','DDMMYYYY')

DayS

DayS(Date,Format):String.
Use DayS to get the day part of a date value as string. DayS('01012003','DDMMYYYY')

HourS

HourS(Date,Format):String.
Use HourS to get the hour part of a date value as string. HourS('01012003','DDMMYYYY')

MinuteS

MinuteS(Date,Format):String.
Use MinuteS to get the minute part of a date value as string. MinuteS('01012003','DDMMYYYY')

MonthS

MonthS(Date,Format):String.
Use MonthS to get the month part of a date value as string. MonthS('01012003','DDMMYYYY')

SecondS

SecondS(Date,Format):String.
Use SecondS to get the second part of a date value as string. SecondS('01012003','DDMMYYYY')

YearS

YearS(Date,Format):String.
Use YearS to get the year part of a date value as string. YearS('01012003','DDMMYYYY')

IncDateS

IncDateS(Date,Format,ChangeType,Increment):String. ChangeType: YEAR,MONTH,WEEK,DAY,HOUR,MINUTE,SECOND Use IncDateS to Increase ChangeType part of a date value by an Increment. IncDateS ('01012003','DDMMYYYY', 'YEAR',1)

DecDateS

DecDateS(Date,Format,ChangeType,Decrement):String. ChangeType: YEAR,MONTH,WEEK,DAY,HOUR,MINUTE,SECOND Use DecDateS to Decrease ChangeType part of a date value by an Decrement. DecDateS ('01012003','DDMMYYYY', 'YEAR',1)

Conversion Functions

IntegerToString

IntegerToString(Integer):String
IntegerToString converts integer value to string value.

NumberToString

NumberToString(Float):String
NumberToString converts float value to string value.

StringToInteger

StringToInteger(String):Integer
StringToInteger converts string value to integer value.

StringToNumber

StringToNumber(String):Float
StringToNumber converts string value to float value.

Miscellaneous Functions

Iif

Iif(expr1==expr2;expr3;expr4)
Iif function returns expr3 or expr4 depending on expr1==expr2

GetSystemVariable

GetSystemVariable('VARIABLENAME'):string
GetSystemVariable returns value of 'VARIABLENAME'.

Possible values for 'VARIABLENAME' are:

COMPUTERNAME, OSUSERNAME, DBUSERNAME, BLOCKNUMBER, LINENUMBER, RECORDNUMBER, SYSTEM_DATE

Pos

Pos(Substr,String): Integer
Pos searches for Substr within String and returns an integer value that is the index of the first character of Substr within String. Pos is case-sensitive. If Substr is not found, Pos returns zero.

GetFileHeaderLine

GetFileHeaderLine(LineNumber): String