Excel · Power Query · API IBAN-Test

Weryfikacja numerów IBAN w Excelu za pomocą Power Query

Wyślij listę IBAN z Excela do API IBAN-Test i uzyskaj wynik weryfikacji, nazwę banku i BIC z powrotem w postaci tabeli - bez makr VBA.

W tym przewodniku używany jest program Excel dla Microsoft 365 w systemie Windows. Konfigurujesz jednorazowo zapytanie Power Query, a następnie aktualizujesz wyniki za pomocą menu Dane. Wymagane jest konto IBAN-Test z tokenami API i wystarczającym limitem.

Stan testów: Sprawdzana jest struktura XLSX i składnia M, a wywołanie API jest zgodne z opublikowanym opisem interfejsu. Pełny test z użyciem programu Microsoft Excel i uwierzytelnionymi zapytaniami API nadal trwa. Najpierw sprawdź proces za pomocą kilku danych testowych.

Co musisz przygotować

• Excel dla Microsoft 365 w systemie Windows z Power Query. Ten proces nie jest gwarantowany w przypadku programu Excel w przeglądarce i programu Excel dla komputerów Mac.
• Token API: Zaloguj się do IBAN-Test. Informacje o dostępie można znaleźć w Dokumentacja API; odpowiedni limit w ramach Ceny.
• Zwolnienie do przetworzenia: Numery IBAN są przesyłane do IBAN-Test. Korzystaj wyłącznie z danych, które możesz przetwarzać w ten sposób.

1. Przygotuj skoroszyt i listę IBAN

Rozpakuj ZIP i otwórz iban-test-excel-en.xlsx. W arkuszu Input zastąp przykładowe dane swoją listą. Kolumna Reference przeznaczona jest do samodzielnego przypisania; do API przesyłany jest jedynie numer IBAN.

Nazwy kolumn Reference i IBAN oraz nazwę tabeli IBAN_Input pozostaw bez zmian. Nowe wiersze muszą znajdować się w tej tabeli programu Excel. Zapisz numery IBAN jako tekst. Zapytanie usuwa spacje i konwertuje litery na wielkie.

W arkuszu Settings zamień YOUR_API_TOKEN na swój token. Nazwa tabeli to IBAN_Settings, a kolumna to ApiToken. Token zostanie zapisany do pliku. Nie udostępniaj tego skoroszytu swojemu tokenowi.

2. Dodaj kod Power Query

1. W Excelu otwórz Dane → Pobierz dane → Z innych źródeł → Puste zapytanie.
2. W edytorze Power Query otwórz Narzędzia główne → Edytor zaawansowany.
3. Otwórz plik iban-validation.pq z pliku ZIP w edytorze tekstu. Skopiuj całą treść i użyj jej do zastąpienia istniejącego kodu w edytorze zaawansowanym.
4. Potwierdź za pomocą Gotowe i nazwij zapytanie IBAN_Results.

Nawet podgląd może wywołać żądania API. Dlatego szablon jest początkowo ograniczony do 10 różnych, niepustych numerów IBAN na każdą ocenę. Nie jest to obietnica dotycząca liczby żądań HTTP na kliknięcie: Power Query może oceniać zapytania wielokrotnie.

3. Ustaw dostęp API i ochronę danych

Gdy Excel poprosi o poświadczenia dla https://www.iban-test.eu, wybierz Anonimowy. Token API jest nadal wysyłany: sam kod wprowadza go w nagłówku HTTP jako Authorization: Bearer …. Typ logowania Web API nie jest używany w tym szablonie.

Kod wysyła wywołanie POST do /api/v2/iban/validate. Zgodnie z Dokumentacja Microsoft w witrynie Web.Contents, POST w tym przepływie jest możliwy tylko przy typie logowania do źródła danych Anonimowy.

Ustaw poziomy ochrony danych skoroszytu i źródła internetowego zgodnie z wymaganiami operacyjnymi. Komunikat Formula.Firewall dotyczy kombinacji źródeł danych. Sprawdź Poziomy prywatności dodatku Power Query ; Nie wyłączaj całościowej ochrony danych.

4. Załaduj wyniki i zaktualizuj później

Wybierz Zamknij i załaduj... → Tabela → Nowy arkusz. Nie nadpisuj tabeli wejściowej ani ustawień. Później zmodyfikuj swoją listę w arkuszu Input i wybierz Dane → Odśwież wszystko.

Identyczne oczyszczone numery IBAN są łączone w ramach oceny, a wyniki są następnie przypisywane do wszystkich powiązanych wierszy. Twoja kolejność wprowadzania danych została zachowana.

Prawidłowo obsługuj błędy

• Token lub login: Sprawdź token i zapisane uprawnienia w obszarze Dane → Pobierz dane → Ustawienia źródła danych. Typ logowania Anonimowy musi dotyczyć adresu API.
• Limit, wyłączony dostęp lub błąd serwera: Zapytanie zostaje przerwane. Takie problemy nie powinny dawać wyniku INVALID.
• Stare wyniki po błędzie: Excel może zachować ostatni pomyślny status tabeli. Przed ponownym wykorzystaniem wyników sprawdź status aktualizacji i CheckedAtUTC.
• Ponad dziesięć różnych numerów IBAN: Najpierw sprawdź dostępny limit API. Dopiero wtedy zwiększasz MaxUniqueIbans w kodzie. Rozważ dodatkowe prośby o podgląd i powtórkę.

Czego nie dowodzi ważny IBAN

Weryfikacja sprawdza numer IBAN i podaje dostępne dane bankowe. Nie potwierdza istnienia konkretnego rachunku, jego właściciela, salda ani pomyślnej realizacji płatności. Nawet ważna cyfra kontrolna nie zastępuje weryfikacji posiadacza konta.

Często zadawane pytania

Czy mogę sprawdzić numer IBAN również za pomocą wzoru w Excelu?

Sprawdzanie formatu i cyfr kontrolnych można ogólnie zaimplementować jako formułę. Proste sprawdzenie długości nie wystarczy. Instrukcje te korzystają z interfejsu API w celu odczytania dostępnych tam informacji bankowych i zróżnicowanych kodów błędów.

Czy szablon Excel jest darmowy?

Pobieranie jest bezpłatne. Użycie interfejsu API zależy od Twojego planu i dostępnego limitu. Każda aktualizacja, a nawet podgląd może zająć miejsce.

Jak bezpiecznie udostępnić wyniki?

Skopiuj tylko wymagane wartości wyników do nowego pliku. Usuń tokeny, zapytania i niepotrzebne numery IBAN. Samo ukrycie arkusza Settings nie chroni tokena.

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