The Advanced ETL Processor has a robust validation process built-in. The types and nature of the validations taking place can be tweaked and configured by the user. A full range of validation functions is included. Validations can be performed on the basis of data type, lists of values, and regular expressions, which can be individually changed according to requirements.
Summary of Validation Processes:
Advanced ETL Processor users regular expressions data, for example, date formats, postcodes, phone numbers etc. It is also possible to write your own Regular expressions
What are the regular expressions?
A regular expression is a string that is used to describe or match a set of strings, according to certain syntax rules. Regular expressions are used by many text editors, utilities, and programming languages to search and manipulate text based on patterns. For example, Perl and Tcl have a powerful regular expression engine built directly into their syntax. Several utilities provided by Unix distributions—including the editor ed and the filter grep—were the first to popularize the concept of regular expressions.
"Regular expression" is often shortened to regex or regexp (singular), or regexes, regexps, or regexen (plural). Some authors distinguish between regular expression and abbreviated forms such as regex, restricting the former to true regular expressions, which describe regular languages, while using the latter for any regular expression-like pattern, including those that describe languages that are not regular. As only some authors observe this distinction, it is not safe to rely upon it. As an example of the syntax, the regular expression \bex can be used to search for all instances of the string "ex" that occur at word boundaries (signified by the \b). Thus in the string, "Texts for experts," \bex matches the "ex" in "experts," but not in "Texts" (because the "ex" occurs inside the word there and not immediately after a word boundary).
Note:
Most of the regular expressions were taken from www.regexlib.com. We are not able to include a full list of contributors because it is too big. We did our best to test and modify if necessary pattern strings however as time goes by standards changes and some of them could become no longer valid. Therefore Regular Expression Validation Rules should be used with caution.
If you have any useful Regular expression please let us know we would be more than happy to include it in the next release of Advanced ETL Processor
Below is a list of Regular expression Transformation Functions supported by Advanced ETL Processor:
| Image | Data Validation Function | Description |
|---|---|---|
| Regular Expression | Validates data against a regular expression | |
| Is Algerian Post Code | Validates Algerian Post Code Format | |
| Is Andorran IBAN | Validates Andorran International Bank Account Number | |
| Is Andorran Post Code | Validates Andorran Post Code format | |
| Is Argentinean Post Code | Validates Argentinean Post Code | |
| Is Australian Mobile Number | Validates Australian Mobile Number | |
| Is Australian Phone Number | Validation of Australian phone number. Accepts all forms of Australian phone numbers in different formats (area code in brackets, no area code, spaces between 2-3 and 6-7th digits, +61 international dialling code). Checks that area codes are valid (when entered). | |
| Is Australian Post Code | Australian postal code verification. Australia has 4-digit numeric postal codes with the following state-based specific ranges. ACT: 0200-0299 and 2600-2639. NSW: 1000-1999, 2000-2599 and 2640-2914. NT: 0900-0999 and 0800-0899. QLD: 9000-9999 and 4000-4999. SA: 5000-5999. TAS: 7800-7999 and 7000-7499. VIC: 8000-8999 and 3000-3999. WA: 6800-6999 and 6000-6799 | |
| Is Austrian Mobile Phone Number | Validates Austrian Mobile Phone Number | |
| Is Azerbaijani Post Code | Validates Azerbaijani Post Code format | |
| Is Bahraini Post Code | Validates Bahraini Post Code format | |
| Is Bangladeshi Post Code | Validates Bangladeshi Post Code format | |
| Is Belarusian Post Code | Validates Belarusian Post Code format | |
| Is Belgian Post Code | Validates Belgian Post Code | |
| Is Belgian VAT Number | Belgian VAT Numbers format verification with support for optional member state definition. | |
| Is Brunei Post Code | Validates Brunei Post Code format | |
| Is Canadian Branch-Transit/ Bank code | Validates Canadian Branch-Transit number. The branch number must be 3 or 4 digits then '-' then five digits | |
| Is Canadian Provincial Code | Validates Canadian provincial codes | |
| Is Canadian Social Insurance Number | Validates Canadian Social Insurance Number | |
| Is Canadian Zip Code | Canadian postal code format verification. The format of a Canadian postal code is LDL DLD where L are alpha characters and D are numeric digits. But there are some exceptions. The letters D, F, I, O, Q and U never appear in a postal code because of their Advanced similarity to 0, E, 1, 0, 0, and V respectively. In addition to avoiding the six "forbidden" letters W and Z also do not appear as the first letter of a postal code (at least not at present). | |
| Is Chilean Post Code | Validates Chilean Post Code format | |
| Is Credit Card | Validation of all major credit cards formats | |
| Is Croatian Post Code | Validates Croatian Post Code format | |
| Is Cyprus Post Code | Validates Cyprus Post Code format | |
| Is Cyprus VAT Number | Cyprus VAT Numbers format verification with support for optional member state definition | |
| Is Czech Republic IBAN | Validates Czech International Bank Account Number | |
| Is Czech Republic VAT Number | Czech Republic VAT Numbers format verification with support for optional member state definition. | |
| Is Danish Civil Registration Number | Validates Danish civil registration number, which consists of 10 numeric characters and one special character '-'. The format is DDMMYY-XXXX | |
| Is Danish Phone Number | Validates Danish Phone Number | |
| Is Danish Post Code | Validates Danish Post Code | |
| Is Danish VAT Number | Validates Danish VAT Numbers format with support for optional member state definition. | |
| Is Dutch Car Number Plate | Validates Dutch Car Number Plate | |
| Is Dutch Phone Number | Validates Dutch Phone Number | |
| Is Dutch Post Code | Validates Dutch Postal Code | |
| Is Egyptian Post Code | Validates Egyptian Post Code format | |
| Is Email | Email Address validation that allows both IP addresses and regular domains. In the case of an IP address, it makes sure that it is no more than 255 for each part. | |
| Is Estonian Post Code | Validates Estonian Post Codes | |
| Is Estonian VAT Number | Estonian VAT Numbers format verification with support for optional member state definition. | |
| Is Ethiopian Post Code | Validates Ethiopian Post Code format | |
| Is Faroe Islands Post Code | Validates Faroe Islands Post Code format | |
| Is File Name | Validation of filename | |
| Is Finnish VAT Number | Finnish VAT Numbers format verification with support for optional member state definition. | |
| Is French Phone Number | French Phone Number Validation | |
| Is French Polynesia Post Code | Validates French Polynesia Post Code format | |
| Is French Post Code | French Post Code Validation | |
| Is French VAT Number | French VAT Numbers format verification with support for optional member state definition. | |
| Is German Car License Plate | German Car License Plate Validation | |
 | Is German IBAN | German International Bank Account Number Validation |
| Is German Post Code | Validates German Post Code format | |
| Is German VAT Number | German VAT Numbers format verification with support for optional member state definition. | |
| Is Greek VAT Number | Greek VAT Numbers format verification with support for optional member state definition. | |
| Is Guadeloupe Post Code | Validates Guadeloupe Post Code format | |
| Is Guinea-Bissau Post Code | Validates Guinea-Bissau Post Code format | |
| Is Guinean Post Code | Validates Guinean Post Code format | |
| Is Hungarian VAT Number | Hungarian VAT Numbers format verification with support for optional member state definition | |
| Is Indian Mobile Number | Validates Indian Mobile Phone Number | |
| Is Indian Phone Number | Validates Indian Phone Number | |
| Is Indian Post Code | Validates Indian Post Code | |
 | Is Indian Vehicle Registration Number | Validates Indian Vehicle Registration Number |
| Is IP Address V4 | Validates IP addresses in the dotted quad form | |
| Is IP Address V6 | Validates all IPv6 text representations as defined within RFC 2373 | |
| Is Irish VAT Number | Irish VAT Numbers format verification with support for optional member state definition. | |
| Is ISBN 10 | Validation of 10 digits ISBN. The ISBN number must be preceded by the text "ISBN:" or "ISBN-10:", the colon is optional. | |
| Is ISBN 13 | Validation of new 13 digits ISBN. The ISBN number must be preceded by the text "ISBN:" or "ISBN-13:", the colon is optional. | |
| Is Israeli Mobile Phone Number | Validates Israeli Mobile Phone Number | |
| Is Israeli Phone Number | Validates Israeli Phone Number Format | |
| Is Italian IBAN | Italian International Bank Account Number Validation | |
| Is Italian Mobile Number | Validates Italian mobile phone number with (or without) the International code | |
| Is Italian Phone Number | Validates Italian phone number | |
| Is Italian Post Code | Postcode check for Italy (including possible Vatican/Italy indications) | |
| Is Italian VAT Number | Italian VAT Numbers format verification with support for optional member state definition. | |
| Is Latvian VAT Number | Validates Latvian VAT Number | |
| Is Lithuanian VAT Number | Lithuanian VAT Numbers format verification with support for optional member state definition. | |
 | Is Luxembourg VAT Number | Luxemburg VAT Numbers format verification with support for optional member state definition |
| Is Maltese VAT Number | Maltese VAT Numbers format verification with support for optional member state definition | |
 | Is Netherlands VAT Number | The Netherlands VAT Numbers format verification with support for optional member state definition. |
| Is Peru Cellular Number | Validates Peru Cellular Number format | |
| Is Peru Phone Number | Validates Peru Phone Number format | |
| Is Polish IBAN | Validates Polish International Bank Account Number | |
| Is Polish VAT Number | Polish VAT Numbers format verification with support for optional member state definition | |
| Is Portuguese VAT Number | Portuguese VAT Numbers format verification with support for optional member state definition. | |
| Is Russian Phone Number | Validates Russian Phone Number | |
| Is Russian Post Code | Validates Russian Post Code | |
| Is Slovakian IBAN | Validates Slovakian International Bank Account Number | |
| Is Slovakian VAT Number | Slovakian VAT Numbers format verification with support for optional member state definition | |
| Is Slovenian VAT Number | Slovenian VAT Numbers format verification with support for optional member state definition | |
| Is South African Mobile Number | only allows valid cell phones from South African service providers | |
| Is South African Phone Number | Validates South African Phone Number | |
| Is South African VAT Number | Validates South African VAT Number | |
| Is Spanish IBAN | Spanish IBAN Validation | |
| Is Spanish Phone Number | Spanish Phone Number Validation | |
| Is Spanish Post Code | Spanish Post Code Validation | |
| Is Spanish VAT Number | Spanish VAT Numbers format verification with support for optional member state definition. | |
| Is Swedish IBAN | Swedish International Bank Account Number IBAN | |
| Is Swedish Person Number | Validates Swedish Person Number | |
| Is Swedish Phone Number | Validates Swedish Phone Number | |
| Is Swedish Post Code | Validates Swedish Post Code Format | |
| Is Swedish VAT Number | Swedish VAT Numbers format verification with support for optional member state definition. | |
| Is UK Bank Sort Code | Validation of United Kingdom Bank Sort code | |
| Is UK Driver Licence | Validates the United Kingdom Drivers License format as described by the DVLA.See http://www.govtalk.gov.uk/gdsc/html/frames/default.htm. Matches: 1. Must be 16 characters 2. First 5 characters are alphanumeric. 3. Next 6 characters must be numeric 4. Next 3 characters are alphanumeric 5. Last 2 characters are alpha 6. Second character of numeric section can only be 0, 1, 5 or 6. 7. Fourth and fifth characters of the numeric section must be in the range 01 to 31 | |
| Is UK Mobile Number | United Kingdom mobile phone number validation with optional +44 national code, also allows optional brackets and spaces at appropriate positions | |
| Is UK National Insurance Number | Validation of the United Kingdom National Insurance Number | |
| Is UK Phone Number | Validation of United Kingdom phone number also allows +44 national code in addition to extension numbers of 3 or 4 digits | |
| Is UK Post Code | Validates United Kingdom Post Code | |
| Is UK VAT Number | United Kingdom VAT Numbers format verification with support for optional member state definition. | |
| Is UK Vehicle Registration Number | Validation of United Kingdom Vehicle Registration Number | |
| Is UNC | Validation of UNC | |
| Is URL | Validation of URL | |
| Is US or CA Zip code | Validates US or CA Zip code format | |
| Is US Phone Number | US Phone Number Validation | |
| Is US Social Security Number | Validates US Social security number | |
| Is US State | Validates US State 2 letter abbreviations | |
| Is US Zip Code | Validates US Zip Code |
;
Advanced ETL Processor is designed to automate extracting data from ANY database, transform, validate it and load into ANY database. Typical usage of it would be extract data from Excel File, Validate Date Formats, Sort data, deduplicate it and load it into Oracle database, run stored procedure or Sql script, once loading is completed. Unlike Oracle SQL loader, BCP, DTS or SSIS Advanced ETL Processor can also add new and update old records based on the primary key
User Rating: 5 / 5
Below is a list of Date Validation Functions supported by Advanced ETL Processor
| Image | Data Validation Function | Description |
|---|---|---|
| Is Date | Checks if the data is a Date | |
| Is Date Between | Checks if the data is a Date between two values | |
| Is Date Less Than | Checks if the data is a Date less than the value | |
| Is Date More Than | Checks if the data is a Date more than the value | |
| Is Monday, Is Tuesday, Is Wednesday, Is Thursday, Is Friday, Is Saturday, Is Sunday, Is Today, Is Yesterday, Is Tomorrow, Is Weekend, Is Weekday | Checks if the data is a Date value that belongs to a specific day of a week | |
| Is January, Is February, Is March, Is April, Is May, Is June, Is July, Is August, Is September, Is October, Is November, Is December, Is Current Month, Is Last Month, Is Next Month | Checks if the data is a Date value that belongs to a specific month of a year | |
| Is 1st Quarter, Is 2nd Quarter, Is 3rd Quarter, Is 4th Quarter, Is Current Quarter,Is Last Quarter, Is Next Quarter | Checks if the data is a Date value that belongs to a specific quarter of a year | |
| Is Leap Year, Is Current Year, Is Last Year, Is Next Year | Checks if the data is a Date value that belongs to a specific year | |
| Is Current Week, Is Last Week, Is Next Week | Checks if the data is a Date value that belongs to a specific week | |
| Is Within Past Minutes, Is Within Past Hours, Is Within Past Days, Is Within Past Weeks, Is Within Past Months | Checks if the data is a Date value within in past minutes etc. |
Advanced ETL Processor has a robust data validation and transformation process built-in.
The types and nature of the transformations taking place can be tweaked and configured by the user. A full range of data transformation functions is included. Transformations can be performed on the basis of data type, lookups and regular expressions, which can be individually changed according to the requirements.
More transformation functions
Below is a list of Number Validation Functions supported by Advanced ETL Processor
| Image | Data Validation Function | Description |
|---|---|---|
| Is Number | Checks if the data is a Number | |
| Is Integer | Checks if the data is an Integer | |
| Is Positive | Checks if the data is a Positive Number | |
| Is Negative | Checks if the data is a Negative Number | |
| Is Number Between | Checks if the data is a number between two values | |
| Is Number Less Than | Checks if the data is a number less than the value | |
| Is Number More Than | Checks if the data is a number more than the value |
Advanced ETL Processor has a robust data validation and transformation process built-in.
The types and nature of the transformations taking place can be tweaked and configured by the user. A full range of data transformation functions is included. Transformations can be performed on the basis of data type, lookups and regular expressions, which can be individually changed according to the requirements.
More transformation functions
Below is a list of String Validation Functions supported by Advanced ETL Processor
| Image | Data Validation Function | Description |
|---|---|---|
| Is Null | Checks if the data is equal to null. | |
| Is Empty String | Checks if the data is Empty String. (Length of the string is zero). | |
| Is Alpha | Checks if the data contains only Alpha characters. | |
| Is Alpha Numeric | Checks if the data contains only Alpha Numeric characters. | |
| Is Hex | Checks if the data contains a valid HEX string | |
| Is Equal To | Checks if the data is equal to the supplied value | |
| Is Starts With | Checks if the data starts with the supplied value | |
| Is Ends With | Checks if the data ends with the supplied value | |
| Contains | Checks if the data contains supplied value | |
| In List | Checks if the data exists within supplied list values |
Advanced ETL Processor has a robust data validation and transformation process built-in.
The types and nature of the transformations taking place can be tweaked and configured by the user. A full range of data transformation functions is included. Transformations can be performed on the basis of data type, lookups and regular expressions, which can be individually changed according to the requirements.
More transformation functions
Below is a list of Time Validation Functions supported by Advanced ETL Processor
a
| Image | Data Validation Function | Description |
|---|---|---|
| Is Time | Checks if the data is a time value | |
| Is Time Between | Checks if the data is a time between two values | |
| Is Time Less Than | Checks if the data is a time less than the value | |
| Is Time More Than | Checks if the data is a time more than the value | |
| Is Second | Checks if data is a number between 0 and 59 | |
| Is Minute | Checks if data is a number between 0 and 59 | |
| Is Hour 24 | Checks if data is a number between 0 and 23 | |
| Is Hour 12 | Checks if data is a number between 0 and 12 | |
| Is PM | Checks if the data is a PM Date | |
| Is AM | Checks if the data is an AM Date |
Advanced ETL Processor has a robust data validation and transformation process built-in.
The types and nature of the transformations taking place can be tweaked and configured by the user. A full range of data transformation functions is included. Transformations can be performed on the basis of data type, lookups and regular expressions, which can be individually changed according to the requirements.
More transformation functions
Watch this video to get started with Advanced ETL Processor. Our WIKI has more detailed information if you are stuck post your question on our support forum and we will do our best to assist you.
- For all new users, we recommend watching 02 Getting familiar with the user interface
- If your main goal is transforming the data watch 10 Creating basic ETL Transformation.
- If you want to start automating your business watch 11 Creating Packages
Please also watch the repository videos. Understanding working with the repository is extremely important.
Transformation example
Testimonials
What customers say about us
I used Advanced ETL Processor in 2 Enterprises for many business processes and Business automation (outside finance department). I did not find any other tool with so many functions and broad flexibility for that Price! If you need support for bugs or solution design you will get it very fast. Best Support I have ever seen.
Lionel Albrecht
IQVIA and DB Software Laboratory (DBSL) partnered in 2010 and have been working in close cooperation ever since. Over this period of time, DBSL software components formed an integral part of a large number of IQVIA applications currently used by over 20 UK NHS Trusts (Hospitals).
Dmitry Dorsky,
Director
The product is easy to learn and once a developer understands the ETL way for solving the problem at hand, the developer's productivity will increase. Even our DBAs now uses the ETL software to quickly create solutions instead of SSIS or SQL jobs.
Daniel Fung
Solutions Architect