Pobieranie bazy danych banków do SQL Server

Skąd pobrać numery banków?

Po artykule na temat numeru PESEL, nadszedł czas na coś bardziej skomplikowanego. PESEL teoretycznie ma każdy, numer konta bankowego - niekoniecznie. Nie zmienia to jednak faktu, że jest numerem wykorzystywanym znacznie częściej i to nie tylko dlatego, że każdy może mieć więcej numerów kont. Jak można się domyślić, numer konta bankowego ma swoją stałą, z góry określoną strukturę. Oprócz kodu kraju, sumy kontrolnej i numeru konkretnego klienta w pełnym numerze zaszyty jest symbol banku. Da się zatem określić, do jakiego banku należy określony numer konta. Skąd pobrać bazę danych banków wraz z przydzielonymi im numerami? Najlepiej szukać ich bezpośrednio na stronie Narodowego Banku Polskiego. Na dzień 21.11.2012 plik z informacjami na ten temat znajdował się tutaj. Na wyżej wymienionej stronie znajduje się wiele innych plików z różnymi informacjami - proces pobierania ich i konwersji do postaci tabel SQL Server nie będzie się znacząco różnił. Rzekłbym, że proces będzie protszy, bo i pliki mniejsze. Ja zająłem się największym i najdokładniejszym, jaki tam znalazłem. Opisano go jako: 'Numery działających banków krajowych (w tym banków spółdzielczych), oddziałów instytucji kredytowych, oddziałów banków zagranicznych, oddziałów banków zagranicznych i ich jednostek organizacyjnych, którym nadano numery rozliczeniowe.', a sam plik znajduje się w tym miejscu: dz_bank_jorg.txt.

Format udostępnianego pliku

Plik jest dość szczegółowo i jasno opisany przez dokument struktura_plikow_txt.pdf udostępniany przez NBP. Trochę to zaskakujące, biorąc pod uwagę jakość i czytelność różnych innych dokumentów instytucji państwowych. Żeby nie było za wesoło, jest tam jedna nieścisłość/jedno niedopowiedzenie. Plik z danymi zawiera 32 kolumny, natomiast plik dokumentacji opisuje 31 kolumn. Mam nadzieję, że jest to problem przejściowy. Na potrzeby przykładu kolumna została oznaczona jako Nieudokumentowana. Przed przystąpieniem do dalszej części warto się z tymi plikami zapoznać. Dla tych, którzy są leniwi streszczę: plik z danymi to plik tekstowy. Każdy rekord umieszczony jest w nowej linii (oddzielony separatorem \r\n), a każda wartość w danej linii oddzdielona jest znakiem tabulacji (\t). Takie dane można odczytać bazpośrednio z poziomu SQL Server. Jak?

Tworzenie pliku z opisem formatu

SQL Server powinien być na tyle uniwersalny, aby dało się wczytać pliki w wielu różnych formatach. Jeżeli jest to XML, to nie ma problemu. Co z plikami tekstowymi? Nie można założyć, że rekordy są zawsze oddzielone znakiem nowej linii, a poszczególne kolumny tabulacją. Może to być równie dobrze znak spacji, średnika, symbol $ lub znak |. Narzędzie uniwersalne musi być trochę bardziej skomplikowane - właśnie po to, aby obsłużyć szerszy zakres danych wejściowych.

Co pozwala silnikowi SQL Server rozpoznawać różne formaty plików? Plik formatu! Przyjrzyjmy się definicji takiego formatu pliku, przystosowanego do odczytu danych udostępnianych przez Narodowy Bank Polski:

Plik formatu jest duży, bo dużo jest pól. Zrozumienie składni i właściwa interpretacja jest jednak prostsza, niż się na początku wydaje.

Konstrukcja pliku z definicją formatu

Już nawet pierwszy rzut oka pozwala dostrzec pewne powtarzające się bloki. Przyjrzyjmy się jeszcze raz, tym razem nieco zmodyfikowanej wersji:

Zaprezentowany szablon można przyjąć jako wzorzec. Każdy plik rozpoczyna się od znacznika BCPFORMAT z zadeklarowanymi przestrzeniami nazw. Wewnątrz głównego węzła znajdują się dwa elementy: RECORD oraz ROW. Ten pierwszy definiuje to, co znajduje się w pliku wejściowym. Drugi określa, na co powinien to skonwertować SQL Server. Związek plik-baza danych definiowany jest przez parę RECORD.FIELD.ID i ROW.COLUMN.SOURCE. Inaczej mówiąc: te same wartości w tych atrybutach definiują złączenie, niczym zwykły JOIN.

Należy się jeszcze kilka słów na temat atrybutów w elementach RECORD oraz ROW. Atrybut ID elementu record został już opisany - jest kluczem. Atrybut xsi:type określa sposób wydobycia, rozpoznania pola w pliku z danymi wejściowymi. Nie zagłębiając się mocno w szczegóły, wartość CharTerm oznacza symbol terminalny. Jest to znak lub grupa znaków, które określają koniec pola. W naszym przykładzie będzie to znak tabulacji, \t, lub znak końca linii, \r\n. Do atrybutu xsi:type z wartością CharTerm musi być dodany drugi atrybut, TERMINATOR, którego wartość reprezentuje ów symbol terminalny. Aby dokończyć opisywanie atrybutów elementu FIELD należy wspomnieć o MAX_LENGTH. Służy on, a jakże, do określenia maksymalnej długości pola. Domyślnie przyjmowane jest 8000 dla char i 4000 dla nchar. Można ten atrybut zostawić w spokoju, ale bezpieczniej jest go ustawić tak, jak opisano w dokumentacji pliku wejściowego. Po pierwsze, SQL Server nie będzie musiał rezerwować pamięci dla potencjalnie większych wartości, a po drugie, wyświetli nam stosowny komunikat, jeżeli limity zostaną przekroczone.

Element row w podanym przypadku też jest prosty. Oprócz klucza opisanego powyżej umieszczono w nim dwa atrybuty: NAME - określa nagłówek/nazwę kolumny wynikowej, oraz xsi:type określający typ danych SQL. Wśród wielu zdefiniowanych wartości najpowszechniej stosowane są: SQLTINYINT, SQLSMALLINT, SQLINT, SQLNVARCHAR. Nie zamierzam w tym miejscu powielać dokumentacji, więc zainteresowanych odsyłam tutaj: Formaty plików XML do importu i eksportu danych.

Pobieranie danych

Najgorsze są jednorazowe przygotowania. Samo pobieranie danych jest już bardzo proste. Przyjrzyjmy się przykładowej instrukcji SELECT pokazanej na poniższym listingu:

Instrukcja wygląda na prostą i taką w rzeczywistości jest. Wykonujemy tylko zwykłą instrukcję SELECT na źródle danych (dz_bank_jorg.txt) i interpretujemy zawarte tam dane w sposób zdefiniowany w pliku formatu (format.xml). Brudną robotą zajmuje się funkcja OPENROWSET.

Szczegółowych informacji na temat OPENROWSET należy szukać w dokumentacji. Ja ze swojej strony, w miarę możliwości, postaram się zamieścić inne przykłady praktycznych zastosowań tej funkcji.

Kategoria:SQL Server