Excel · Power Query · API IBAN-Test

Valider des IBAN dans Excel avec Power Query

Envoyez une liste IBAN d'Excel à l'API IBAN-Test et obtenez le résultat du contrôle, le nom de la banque et le BIC sous forme de tableau - sans macros VBA.

Ce guide utilise Excel pour Microsoft 365 sous Windows. Vous configurez une requête Power Query une fois, puis mettez à jour vos résultats à l'aide du menu Données. Un compte IBAN-Test avec des jetons API et un quota suffisant est requis.

État des tests : La structure XLSX et la syntaxe M sont vérifiées et l'appel API est aligné sur la description de l'interface publiée. Un test complet avec Microsoft Excel et des requêtes API authentifiées est toujours en attente. Vérifiez d'abord le processus avec quelques données de test.

Ce que vous devez préparer

• Excel pour Microsoft 365 sous Windows avec Power Query. Ce processus n'est pas garanti ici pour Excel dans le navigateur et Excel pour Mac.
• Un jeton API : Connectez-vous à IBAN-Test. Des informations sur l'accès peuvent être trouvées dans la Documentation API ; le quota approprié sous Tarifs.
• Autorisation pour traitement : Les IBAN sont transférés sur IBAN-Test. Utilisez uniquement les données que vous êtes autorisé à traiter de cette manière.

1. Préparez le classeur et la liste IBAN

Décompressez le ZIP et ouvrez iban-test-excel-en.xlsx. Dans la feuille Input, remplacez les exemples de données par votre liste. La colonne Reference est destinée à votre propre affectation ; seul l'IBAN est envoyé à l'API.

Laissez les noms de colonnes Reference et IBAN ainsi que le nom de table IBAN_Input inchangés. Les nouvelles lignes doivent figurer dans ce tableau Excel. Enregistrez les IBAN sous forme de texte. La requête supprime les espaces et convertit les lettres en majuscules.

Dans la feuille Settings, remplacez YOUR_API_TOKEN par votre token. Le nom de la table est IBAN_Settings et la colonne est ApiToken. Le jeton est enregistré dans le fichier. Ne partagez pas ce classeur avec votre jeton.

2. Ajouter le code Power Query

1. Dans Excel, ouvrez Données → Obtenir des données → Depuis d'autres sources → Requête vide.
2. Dans l’éditeur Power Query, ouvrez Accueil → Éditeur avancé.
3. Ouvrez iban-validation.pq à partir du ZIP dans un éditeur de texte. Copiez l'intégralité du contenu et utilisez-le pour remplacer le code existant dans l'éditeur avancé.
4. Confirmez avec Terminé et nommez la requête IBAN_Results.

Même l'aperçu peut déclencher des requêtes API. Le modèle est donc initialement limité à 10 IBAN différents non vides par évaluation. Il ne s'agit pas d'une promesse concernant le nombre de requêtes HTTP par clic : Power Query peut évaluer les requêtes plusieurs fois.

3. Définir l'accès à l'API et la protection des données

Lorsqu'Excel demande des informations d'identification pour https://www.iban-test.eu, sélectionnez Anonyme. Le jeton API est toujours envoyé : le code lui-même le saisit dans l'en-tête HTTP sous la forme Authorization: Bearer …. Le type de connexion API Web n'est pas utilisé pour ce modèle.

Le code envoie un appel POST à /api/v2/iban/validate. Selon Documentation Microsoft sur Web.Contents, le POST dans ce flux n'est possible qu'avec le type de connexion à la source de données Anonyme.

Définissez les niveaux de protection des données du classeur et de la source Web en fonction de vos besoins opérationnels. Un message Formula.Firewall concerne la combinaison de sources de données. Vérifiez le Niveaux de confidentialité de Power Query ; Ne désactivez pas la protection des données de manière généralisée.

4. Charger les résultats et mettre à jour plus tard

Sélectionnez Fermer et charger dans... → Tableau → Nouvelle feuille de calcul. N'écrasez pas la table d'entrée ou les paramètres. Plus tard, modifiez votre liste dans la feuille Input et sélectionnez Données → Actualiser tout.

Les IBAN nettoyés identiques sont combinés dans une évaluation et les résultats sont ensuite attribués à toutes les lignes associées. Votre ordre de saisie est conservé.

Gérer correctement les erreurs

• Jeton ou connexion : Vérifiez le jeton et les autorisations enregistrées sous Données → Récupérer les données → Paramètres de la source de données. Le type de connexion Anonyme doit s'appliquer à l'adresse API.
• Quota, accès désactivé ou erreur de serveur : La requête est abandonnée. De tels problèmes ne devraient pas produire un résultat INVALID.
• Anciens résultats après une erreur : Excel peut conserver le dernier statut de table réussi. Vérifiez l'état de la mise à jour et CheckedAtUTC avant de réutiliser les résultats.
• Plus de dix IBAN différents : Tout d’abord, vérifiez le quota d’API disponible. Ce n'est qu'alors que vous incrémentez MaxUniqueIbans dans le code. Envisagez des demandes supplémentaires de prévisualisation et de relecture.

Ce qu'un IBAN valide ne prouve pas

La validation vérifie l'IBAN et fournit les coordonnées bancaires disponibles. Il ne confirme pas l'existence d'un compte spécifique, son propriétaire, son solde ou la bonne exécution d'un paiement. Même un chiffre de contrôle valide ne remplace pas la vérification du titulaire du compte.

Questions fréquemment posées

Puis-je également vérifier l’IBAN à l’aide d’une formule Excel ?

Les vérifications du format et des chiffres de contrôle peuvent généralement être mises en œuvre sous forme de formule. Une simple vérification de la longueur ne suffit pas. Ces instructions utilisent l'API pour lire les informations bancaires et les codes d'erreur différenciés qui y sont disponibles.

Le modèle Excel est-il gratuit ?

Le téléchargement est gratuit. L'utilisation de l'API dépend de votre forfait et du quota disponible. Chaque mise à jour et même un aperçu peuvent consommer du quota.

Comment partager les résultats en toute sécurité ?

Copiez uniquement les valeurs de résultat requises dans un nouveau fichier. Supprimez les jetons, les requêtes et les IBAN inutiles. Le simple fait de masquer la feuille Settings ne protège pas le jeton.

let
    // Read only the two named tables supplied with the starter workbook.
    Settings = Excel.CurrentWorkbook(){[Name="IBAN_Settings"]}[Content],
    RawToken = Text.Trim(Text.From(Settings{0}[ApiToken])),
    ApiToken = if RawToken = "" or RawToken = "YOUR_API_TOKEN" then
        error Error.Record("Configuration", "Enter your API token in IBAN_Settings before refreshing.", null)
        else RawToken,
    Input = Excel.CurrentWorkbook(){[Name="IBAN_Input"]}[Content],
    AsText = Table.TransformColumnTypes(Input, {{"Reference", type text}, {"IBAN", type text}}),
    Normalized = Table.AddColumn(AsText, "IBAN_Normalized", each
        if [IBAN] = null then "" else Text.Upper(Text.Remove([IBAN], {" ", "#(tab)", "#(cr)", "#(lf)", Character.FromNumber(160)})), type text),
    UniqueIbans = List.Distinct(List.Select(Normalized[IBAN_Normalized], each _ <> "")),
    // Explicit cap for the starter example. Increase only after checking your quota.
    MaxUniqueIbans = 10,
    ToCheck = if List.Count(UniqueIbans) > MaxUniqueIbans then
        error Error.Record("Quota protection", "This example checks at most 10 distinct IBANs per refresh. Check your API quota before increasing MaxUniqueIbans.", null)
        else UniqueIbans,
    CheckIban = (iban as text) as record =>
        let
            Response = Web.Contents("https://www.iban-test.eu", [
                RelativePath = "api/v2/iban/validate",
                Headers = [Authorization = "Bearer " & ApiToken, #"Content-Type" = "application/json", Accept = "application/json"],
                Content = Json.FromValue([iban = iban]),
                Timeout = #duration(0, 0, 0, 30),
                ManualStatusHandling = {400, 404, 408, 422, 429, 500, 502, 503, 504, 509}
            ]),
            HttpStatus = Record.FieldOrDefault(Value.Metadata(Response), "Response.Status", 200),
            Body = if HttpStatus <> 200 then
                error Error.Record("API request failed", "HTTP " & Text.From(HttpStatus) & ": check authentication, quota or service availability. Refresh was stopped.", null)
                else Json.Document(Response),
            CheckedBody = if not Value.Is(Body, type record) then
                error Error.Record("Unexpected API response", "The response was not a JSON object.", null)
                else Body,
            Code = try Number.From(Record.FieldOrDefault(CheckedBody, "code", null)) otherwise null,
            IsValid = Code = 2100 and Record.FieldOrDefault(CheckedBody, "error", true) = false,
            IsInvalid = List.Contains({3100, 3101, 3102}, Code),
            Status = if IsValid then "VALID" else if IsInvalid then "INVALID" else
                error Error.Record("Validation unavailable", "API code " & (if Code = null then "missing" else Text.From(Code)) & ". Check your token, quota and API documentation. This is not an invalid-IBAN result.", null),
            RawDetails = Record.FieldOrDefault(CheckedBody, "details", []),
            Details = if Value.Is(RawDetails, type record) then RawDetails else [],
            // Force status evaluation before returning metadata.
            Result = if Status = "VALID" or Status = "INVALID" then [
                Status = Status,
                Code = Code,
                Message = Record.FieldOrDefault(CheckedBody, "message", ""),
                Bank = Record.FieldOrDefault(Details, "bankName", null),
                BIC = Record.FieldOrDefault(Details, "bic", null)
            ] else error "Unexpected validation status"
        in
            Result,
    // Evaluate distinct IBANs in sequence. An operational error aborts the refresh.
    CheckedRecords = List.Accumulate(ToCheck, {}, (state, iban) =>
        let
            PreviousReady = List.Count(state) = 0 or List.Last(state)[Status] <> "",
            Current = if PreviousReady then CheckIban(iban) else error "Previous check failed",
            Ready = Current[Status]
        in
            if Ready = "VALID" or Ready = "INVALID" then
                state & {Record.Combine({[IBAN_Normalized = iban], Current})}
            else error "Check failed"),
    Checked = Table.Buffer(Table.FromRecords(CheckedRecords,
        type table [IBAN_Normalized = text, Status = text, Code = nullable number, Message = nullable text, Bank = nullable text, BIC = nullable text])),
    Joined = Table.NestedJoin(Normalized, {"IBAN_Normalized"}, Checked, {"IBAN_Normalized"}, "Check", JoinKind.LeftOuter),
    Expanded = Table.ExpandTableColumn(Joined, "Check", {"Status", "Code", "Message", "Bank", "BIC"}),
    MissingMarked = Table.ReplaceValue(Expanded, null, "MISSING", Replacer.ReplaceValue, {"Status"}),
    CheckedAt = Table.AddColumn(MissingMarked, "CheckedAtUTC", each
        if [Status] = "MISSING" then null else DateTimeZone.RemoveZone(DateTimeZone.FixedUtcNow()), type nullable datetime)
in
    CheckedAt