Excel · Power Query · API IBAN-Test

Validare gli IBAN in Excel con Power Query

Invia un elenco IBAN da Excel all'API IBAN-Test e ottieni il risultato della verifica, il nome della banca e il BIC sotto forma di tabella, senza macro VBA.

Questa guida utilizza Excel per Microsoft 365 su Windows. Puoi impostare una query Power Query una volta e quindi aggiornare i risultati utilizzando il menu Dati. È richiesto un account IBAN-Test con token API e quota sufficiente.

Stato dei test: La struttura XLSX e la sintassi M vengono controllate e la chiamata API è allineata alla descrizione dell'interfaccia pubblicata. Un test completo con Microsoft Excel e query API autenticate è ancora in sospeso. Per prima cosa controlla il processo con alcuni dati di prova.

Cosa devi preparare

• Excel per Microsoft 365 su Windows con Power Query. Questo processo non è garantito qui per Excel nel browser ed Excel per Mac.
• Un token API: Accedi a IBAN-Test. Le informazioni sull'accesso si trovano nello Documentazione dell'API; la quota appropriata sotto Prezzi.
• Rilascio al trattamento: Gli IBAN vengono trasferiti su IBAN-Test. Utilizza solo i dati che sei autorizzato a trattare in questo modo.

1. Preparare la cartella di lavoro e l'elenco IBAN

Decomprimi lo ZIP e apri iban-test-excel-en.xlsx. Nel foglio Input, sostituisci i dati di esempio con il tuo elenco. La colonna Reference è per la tua assegnazione; all'API viene inviato solo l'IBAN.

Lasciare invariati i nomi delle colonne Reference e IBAN e il nome della tabella IBAN_Input. Le nuove righe devono trovarsi all'interno di questa tabella Excel. Salva gli IBAN come testo. La query rimuove gli spazi e converte le lettere in maiuscolo.

Nel foglio Settings, sostituisci YOUR_API_TOKEN con il tuo token. Il nome della tabella è IBAN_Settings e la colonna è ApiToken. Il token viene salvato nel file. Non condividere questa cartella di lavoro con il tuo token.

2. Aggiungere il codice Power Query

1. In Excel, apri Dati → Ottieni dati → Da altre fonti → Query vuota.
2. Nell’editor di Power Query, apri Home → Editor avanzato.
3. Apri iban-validation.pq dallo ZIP in un editor di testo. Copia l'intero contenuto e utilizzalo per sostituire il codice esistente nell'editor avanzato.
4. Confermare con Fatto e denominare la richiesta IBAN_Results.

Anche l'anteprima può attivare richieste API. Il modello è quindi inizialmente limitato a 10 IBAN diversi e non vuoti per valutazione. Questa non è una promessa sul numero di richieste HTTP per clic: Power Query può valutare le query più volte.

3. Imposta l'accesso API e la protezione dei dati

Quando Excel richiede le credenziali per https://www.iban-test.eu, selezionare Anonimo. Il token API viene comunque inviato: il codice stesso lo inserisce nell'intestazione HTTP come Authorization: Bearer …. Il tipo di accesso API Web non viene utilizzato per questo modello.

Il codice invia una chiamata POST a /api/v2/iban/validate. Secondo Documentazione Microsoft su Web.Contents, il POST in questo flusso è possibile solo con il tipo di accesso all'origine dati Anonimo.

Imposta i livelli di protezione dei dati della cartella di lavoro e dell'origine Web in base alle tue esigenze operative. Un messaggio Formula.Firewall riguarda la combinazione di origini dati. Controllare Livelli di privacy di Power Query; Non disattivare la protezione dei dati su tutta la linea.

4. Carica i risultati e aggiornali in seguito

Seleziona Chiudi e carica... → Tabella → Nuovo foglio di lavoro. Non sovrascrivere la tabella di input o le impostazioni. Successivamente, modifica l'elenco nel foglio Input e seleziona Dati → Aggiorna tutto.

Gli IBAN identici puliti vengono combinati all'interno di una valutazione e i risultati vengono quindi assegnati a tutte le righe associate. L'ordine di input viene mantenuto.

Gestire gli errori correttamente

• Token o accesso: Controlla il token e le autorizzazioni salvate in Dati → Recupera dati → Impostazioni origine dati. Il tipo di accesso Anonimo deve essere applicato all'indirizzo API.
• Quota, accesso disabilitato o errore del server: La query viene interrotta. Tali problemi non dovrebbero produrre un risultato INVALID.
• Vecchi risultati dopo un errore: Excel può mantenere l'ultimo stato della tabella riuscita. Controllare lo stato dell'aggiornamento e CheckedAtUTC prima di riutilizzare i risultati.
• Più di dieci IBAN diversi: Innanzitutto, controlla la quota API disponibile. Solo allora si incrementa MaxUniqueIbans nel codice. Prendi in considerazione ulteriori richieste di anteprima e riproduzione.

Ciò che un IBAN valido non dimostra

La verifica controlla l'IBAN e fornisce le coordinate bancarie disponibili. Non conferma l'esistenza di un conto specifico, il suo proprietario, il saldo o l'esecuzione corretta di un pagamento. Anche una cifra di controllo valida non sostituisce la verifica del titolare del conto.

Domande frequenti

Posso verificare l'IBAN anche tramite formula Excel?

I controlli del formato e delle cifre di controllo possono generalmente essere implementati come una formula. Un semplice controllo della lunghezza non è sufficiente. Queste istruzioni utilizzano l'API per leggere le informazioni bancarie e i codici di errore differenziati disponibili lì.

Il modello Excel è gratuito?

Il download è gratuito. L'utilizzo dell'API dipende dal piano e dalla quota disponibile. Ogni aggiornamento e persino un'anteprima possono consumare quota.

Come posso condividere i risultati in modo sicuro?

Copia solo i valori dei risultati richiesti in un nuovo file. Rimuovi token, query e IBAN non necessari. Nascondere semplicemente il foglio Settings non protegge il token.

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