International Bank Account Number (IBAN) is an internationally agreed system of identifying bank accounts across national borders. IBAN consists of up to 34 alphanumeric characters comprising a country code; two check digits; and a number that includes the domestic bank account number, branch identifier, and potential routing information.
With that in mind, you can use Importacular to map a field containing an IBAN, and by way of a regular expression and a Data Transformation, derive and import an account number and/or Sortcode (branch identifier) on to a Constituent's record.
Before we show you how to do this, please note:
1. IBAN structures can vary depending on the country of origin. For more details on IBAN structures by country, go to https://www.iban.com/structure
2. The Regular Expressions used here, like all Regular Expressions, are not supported by Zeidman Development. They are presented here a suggestion. Please visit http://www.regular-expressions.info/ to learn how to understand and write them. Go to https://regexr.com/63f90 to test them.
Data Transformation
If your data source does not contain a field for Bank Account numbers or Bank Identifiers (Sort Code), but if contains IBANs, you can map your Importacular template to the IBAN data source and then use a regular expression (RegEx) to extract just the account number or just the bank identifier (Sort Code).
Start by opening up the field setting and creating a new Data Transformation.
For Bank Identifier (Sort Code), keep 'Source' field = empty, put the Regex formula (see below) in 'Change to Target', 'Match Type' = Exact and 'Replace Type' = Regex. Do not check/tick 'Catch All'.
For Account Number, keep 'Source' field = empty, put the Regex formula (see below) in 'Change to Target', 'Match Type' = Exact and 'Replace Type' = Regex. Do not check/tick 'Catch All'.
What is the Data Transformation doing?
For regular expression data transformations, we are telling Importacular to look in the 'From Source' field and to look for an 'exact' match (via the Match Type). Since we have left the field blank, it is looking for exactly what is in the 'Source' field, not a partial or fragment of that field.
Next, we are telling Importacular to take a section of the 'Source' field and change it as it is imported. The Replace Type field is what tells Importacular that is an Regular Expression and the 'Change to Target', holding the RegEx formula, is telling Importacular which section of the 'Source' field will be imported.
In short,
From Source pulls from the field you have assign in the mapping
Match Type relates to the 'From Source' field and describes the criteria of the matching to the source data
Change to Target is what the source field/data is being transformed into
Replace Type relates to the 'Change to Target' field and describes the nature of the transformation
Regular Expressions (RegEx) To Consider
Bank Identifier (Sort Code)
If you are importing IBANs where the Bank Identifier (Sort Code) always starts at the same position, you might want to use the RegEx of (?<=^.{8})[0-9]{6}
To explain, using this UK IBAN (indicated by the GB), GB15HBUK40127612345678 the 8th Position {8} is the start of the Bank Identifier (Sort Code) which is 6 positions long {6}. The [0-9] between the {8} and the {6} indicate that it is a numerical value. If the IBAN in the data source is different, for example, it has spaces between its component parts = GB 15 HBUK 401276 12345678, you would alter the RegEx to (?<=^.{11})[0-9]{6} as the start position of the Bank Identifier (Sort Code) starts at the 11th position {11} instead of the 8th position {8}.
Account Number
If you are importing IBANs where the Account Number always starts at the same position, you might want to use the RegEx of (?<=^.{14})[0-9]{8}
To explain, using this UK IBAN (indicated by the GB), GB15HBUK40127612345678 the 14th Position {14} is the start of the Account Number, which is 8 positions long {8}. The [0-9] between the {14} and the {8} indicate that it is a numerical value.
If the IBANs in your data source always has the account number starting from the same position and finishing off the IBAN number, but the account number length changes, you might want to use this RegEx instead (?<=^.{14})[0-9].*
The .* indicatespulling in everything after the 14th position, including any spaces or dashes (if present).