Ale nie wolno go kopiować ani drukować, chyba, że uzyska się od autorów odpowiednią, pisemną, zgodę. Taki, my autorzy, mamy kaprys. Prosimy o poszanowanie.
W odpowiednim czasie zostanie opublikowany w formie papierowej.
Tekst nie jest jeszcze gotowy – ale codziennie jest go więcej… Zapisz w ‘Ulubionych’ ;)
Podoba się? Zapraszamy na szkolenia.
Rozdział książki WG&KR „SQL w Excelu”
Wojciech Gardziński, Krzysztof Rumiński © 2011
Spis treści:
Import plików tekstowych przez ODBC (OLE DB, ADO)
Szczegółowy opis zawartości pliku SCHEMA.INI
Źródło danych „nie widzi” pliku
Konieczność współdzielenia pliku z kwerendą
Niestandardowe znaki w tekście, np. tabulatory
Import plików tekstowych przez ODBC (OLE DB, ADO)
Specjaliści niby wiedzą, jak dokonać importu pliku tekstowego. Gdy zapytamy informatyka, lub analityka, uzyskamy prawie automatyczną odpowiedź.
Diabeł jednak siedzi w szczegółach. Zapytajmy o metodę i efekty, podrążmy temat. Rozmówca zaczyna się zamyślać…
Stwierdzi, że to pracochłonne i, że powoduje dużo błędów. I jest już na dobrej drodze. Problem wymaga przemyślenia. I jest co optymalizować.
A przecież to „typowe” zadanie.
No właśnie – typowe. A im bardziej „typowe” jest zadanie, tym większa powinna być motywacja do wypracowania optymalnej metody rozwiązania.
Spróbujmy znaleźć kluczowe punkty zagadnienia i wskazać dobre praktyki.
Informatyk użyje opcji „Import” lub „Podłącz tabelę” do jakiegoś systemu bazodanowego, np. Accessa (wszystkie posiadają takie opcje, niektóre bazy danych, wręcz, jako podstawowe, np. Oracle).
Analityk, mający skłonność „wszystko, co się rusza” otworzyć w Excelu, i tym razem „automatycznie” kliknie we właściwym miejscu: Otwórz!
Co otrzymają?
Nawet, gdy tekst jest „równy” i „jednowierszowy” (wytłumaczymy, co to znaczy, za chwilę) import metodami „standardowymi” nie zawsze się udaje. Pomysłowość „producentów” takich wydruków nie zna granic… Do tego dochodzą problemy z formatem zapisanej daty, separatorami liczb i wiele innych.
Skąd się biorą takie pliki?
Systemy bazodanowe ERP są z reguły „zamknięte” – poniekąd słusznie. – Dostęp do tabel systemu poprzez ODBC wymaga posiadania odpowiednich uprawnień i haseł dostępowych, udostępnianych przez dział IT na restrykcyjnych zasadach.
IT nie rozumie jeszcze, że profesjonalne podejście do obsługi użytkowników to …
Analityczny interfejs system-system.
Nawet, gdy firma wdraża hurtownię danych, dostęp taki jest „ściśle reglamentowany – czytaj: trzeba zapłacić producentowi za każdą „wystawioną na zewnątrz” tabelę. Każda zmiana widoku biznesowego, to kolejne negocjacje i kolejne koszty.
Zamiast analitycznego interfejsu mamy wówczas kierowcę z tylnego siedzenia, który bierze duże pieniądze za szarpana jazdę z postojami w najbardziej nerwowych momentach.
Tak więc, praktycznie, mamy do dyspozycji stosunkowo łatwy i pozbawiony ograniczeń „informatycznych”, dostęp na zasadzie – otwieramy plik tekstowy w Excelu, czyli…
interfejs system-plik tekstowy-system.
Na szczęście systemy ERP/BI chętnie eksportują dane do plików tekstowych lub do „plików Excela”, będących, często, po prostu, plikiem CSV, zapisywanym z rozszerzeniem XLS.
Import – krok po kroku
Po pierwsze, najpierw trzeba ustalić, czy plik tekstowy nadaje się do takiego odczytu. Jednoznacznie dyskwalifikuje istnienie hierarchii informacji w tekście – często spotykane, gdy mamy do czynienia z „wydrukiem”, a nie „eksportem”. Na czym owa hierarchia polega?
Oto najprostsze przykłady. Powtórzymy je w dalszej części z pokazaniem wielu ich wariantów – na razie naszym celem jest wyłącznie „kwalifikacja”, czyli jednoznaczne stwierdzenie, czy metoda „dostępu bazodanowego” ma w ogóle sens.
Kwalifikacja
Plik rozdzielany separatorami,
tu ‘;’ (średnikami), ale mogą to być separatory dowolne
Przykładowy nagłówek strony --------------------------
Konto;Nr_dok;Data;Kwota
501;12/45;20110905;1.234,56
501;123/54;20110906;21.234,56
502;1/88;20110906;1.234,00
Przykładowy nagłówek strony --------------------------
502;1234F;20110907;0,56
Plik rozdzielany spacjami
Przykładowy nagłówek strony --------------------------
Konto Nr_dok Data Kwota
501 12/45 20110905 1.234,56
501 123/54 20110906 21.234,56
502 1/88 20110906 1.234,00
Przykładowy nagłówek strony --------------------------
502 1234F 20110907 0,56
Plik z hierarchią informacji
Przykładowy nagłówek strony --------------------------
Konto/Nr_dok Data Kwota
501
12/45 20110905 1.234,56
123/54 20110906 21.234,56
502
Przykładowy nagłówek strony --------------------------
1/88 20110906 1.234,00
1234F 20110907 0,56
Spośród powyższych przykładów, import przez ODBC sprawdzi się w dwóch pierwszych przypadkach, gdyż są to tzw. „pliki jednowierszowe” – komplet informacji nt. danego dokumentu znajduje się w jednej linii tekstu. Interlinie, ani wiersze nagłówków, stopek, obramowania tabel, jeśli powyższej zasady nie naruszają, nie mają znaczenia.
Danych z pliku z kilkuwierszową hierarchią informacji NIE DA SIĘ pozyskać przez ODBC – są inne metody – specjalne procedury, „odtwarzające” pełne wiersze, tak, jak w dwóch pierwszych przykładach – np. AFIN.NET.TextConverter w systemie AFIN.NET.
Trudnością w plikach hierarchicznych jest przyporządkowanie np. konta 501 do dokumentu 12/45, ponieważ informacje te są w dwóch liniach tekstu (występuje tzw. znak końca linii).
Reasumując – w przypadku odczytu pliku tekstowego metodami bazodanowymi, nie jest ważne, czy plik jest „zaśmiecony” nagłówkami, jaki użyto separator oraz format dat lub liczb – ważne, wręcz, kluczowe jest to, czy wszystkie dane są w jednym wierszu.
Próbny import
Zakładamy, że posiadamy plik tekstowy o nazwie PlikTXT_SeparSrednik.txt, który ma zawartość identyczną, jak pierwszy przykład powyżej - patrz: Plik rozdzielany separatorami.
Jeśli dysponujemy już poprawną definicją takiego odczytu, zawartą w pliku SCHEMA.INI, wszystko od razu wykona nam się poprawnie – my zakładamy jednak, że startujemy od samego początku – mamy tekst i, kolejnymi krokami, dochodzimy do celu.
Uruchamiamy program MS Query.
Szczegółowy opis wszystkich kroków, opisany jest w rozdziale „MS Query”
Tu pokazujemy procedurę sporządzenia odczytu, GDY WSZYSTKO DZIAŁA.
Niestety, to rzadki przypadek, ale od czegoś trzeba zacząć, żeby wiedzieć, jak to POWINNO wyglądać. Wybrany przykład nie jest „ideałem” – zawiera np. „śmieciowe” nagłówki stron oraz separatory tysięczne – pierwsze jest bardzo proste do ominięcia, drugie – bardzo skomplikowane. Ale, ponieważ oba tematy są wręcz standardowe dla tego typu plików – pokazujemy je od razu w naszym przykładzie.
W oknie wyboru źródła danych, wybieramy źródło „Text files”. W większości normalnych, standardowych, instalacji Excela, źródło takie nie występuje – w punkcie „Problemy” opisujemy, jak takie źródło zdefiniować.
Okno wyboru źródła danych
Wskazujemy tabelę danych. W tekstowym źródle danych, plik tekstowy jest – uwaga – tabelą danych! Gdy plik tekstowy ma oryginalnie inne rozszerzenie, należy mu zmienić rozszerzenie plików na TXT lub CSV, gdyż tylko takie rozszerzenia są widoczne w tym oknie. Można, co prawda, dodać inne rozszerzenia przy definicji takiego źródła, ale nie jest to ani proste, ani nie ma gwarancji, że zadziała poprawnie. Więc, wskazujemy plik tekstowy…
Katalog z plikami tekstowymi
…który zostaje natychmiast dodany do kwerendy jako tabela. Gdy,\ odszukamy na tej tabeli gwiazdkę, oznaczającą „wszystkie pola”, i klikniemy ją dwukrotnie, wszystkie pola pliku tekstowego (na razie proszę nie zwracać uwagi, że, w gruncie rzeczy, osiągamy zły efekt) pojawiają się w obszarze danych (na dole).
Uwaga – powtarzamy – wszystkie kroki ze szczegółami, opisane są w rozdziale „MS Query”.
Widok kwerendy próbnej
Wszystko jest źle. Są złe nagłówki, nagłówek pliku tekstowego jest pierwszym wierszem danych, kwoty są tekstami, jest wiele wierszy pustych. Na tym etapie nie należy się tym przejmować, tylko od razu przystąpić do definiowania prawidłowego odczytu.
Po przesłaniu danych do Excela, powyższa kwerenda wygląda tak:
Widok kwerendy próbnej w Excelu
Tworzymy plik SCHEMA.INI, definiujący odczyt ODBC.
Plik jest zwykłym plikiem tekstowym, tworzonym np. w Notatniku. Nazwy SCHEMA.INI nie wolno zmieniać – jest zastrzeżona. Plik musi znajdować się w tym samym katalogu, co pliki tekstowe.
Możemy go skądś skopiować i tylko poprawiać wartości – też np. w Notatniku.
Folder z plikiem (plikami) tekstowym oraz z plikiem definicji odczytu ODBC
Stworzyliśmy taki plik i dokonaliśmy w nim następujących zapisów:
Zawartość pliku SCHEMA.INI
[PlikTXT_SeparSrednik.txt]
ColNameHeader=False
Format=Delimited(;)
MaxScanRows=25
CharacterSet=OEM
NumberDigits=2
DecimalSymbol=,
ThousandSymbol=
NumberLeadingZeros=TRUE
Col1=KONTO char Width 10
Col2=NR_DOK char Width 10
Col3=DATA char Width 10
Col4=KWOTA char Width 20
(Patrz również: Szczegółowy opis zawartości pliku SCHEMA.INI)
Proszę zwrócić uwagę na kilka rzeczy, które od razu mogą wydawać się dziwne.
1. Nagłówek
ColNameHeader=False
Przecież nasz plik ma nagłówek – dlaczego tego nie uwzględniamy?
- Nagłówkiem pliku tekstowego jest jego pierwszy wiersz – tu jest to nagłówek strony
Przykładowy nagłówek strony --------------------------
Musimy więc zignorować nagłówek i stworzyć własny. Tworzymy go tak
Col1=KONTO char Width 10
Col2=NR_DOK char Width 10
...
2. Separator tysięczny
- Dlaczego nie zdefiniowaliśmy parametru ‘ThousandSymbol=’ ? Nasz plik tekstowy ma wartości z separatorem tysięcznym – ‘.’ (kropka)
Dygresja
My, Autorzy, przekazujemy niniejszym Naszym Czytelnikom nie treść instrukcji obsługi producenta, będącej wyrazem jego „chciejstwa”, tylko nasze wieloletnie doświadczenie. W tym przypadku, sytuacja wygląda tak, że, pomimo setek prób, nie udało nam się uruchomić prawidłowego odczytu danych z separatorem tysięcznym (można to jedynie zrobić, redefiniując ustawienia międzynarodowe w Windows, a tego nie robimy ze zrozumiałych względów) – więc od razu wskazujemy na ten ewidentny błąd producenta. Po co tracić czas na próby, skoro i tak nic z tego nie wyjdzie?
Parametr ten możemy wręcz pominąć w pliku SCHEMA.INI.
3. Pola tekstowe wartości liczbowych
Idąc za ciosem, możemy również pominąć separator dziesiętny! Ten, co prawda, czasami działa, ale fakt, że nie działa separator tysięczny i tak psuje nam wszystkie kwoty. Więc…
Gdy jest separator tysięczny w tekście - kwoty zawsze odczytujemy jako teksty. Stąd
Col4=KWOTA char Width 20
(„char” – pole typu tekstowego)
I naprawdę nie ma to znaczenia, że jest to dla nas kolejne źródło problemów. Z tym jednak potrafimy walczyć – patrz dalej.
Wracamy do naszej kwerendy i klikamy przycisk Odśwież - przycisk „!” (wykrzyknik).
Dane poprawiają nam się nieco, ale do celu jeszcze daleko.
Widok kwerendy po zastosowaniu SCHEMA.INI
Jeżeli, przy odczycie polskich znaków, pojawiają się problemy, należy spróbować użyć innej strony kodowej. Zamiast OEM wpisujemy ANSI.
CharacterSet=ANSI
Odświeżenie kwerendy powinno poprawić polskie znaki. Gdy użyta strona kodowa jest jeszcze inna, niestety, nic z tym na tym etapie nie jesteśmy w stanie zrobić. Ten parametr ma tylko dwa dopuszczalne stany: „OEM” i ”ANSI”.
Modyfikujemy (poprawiamy) kwerendę
Jak się pozbyć pustych wierszy?
Nasz odczyt to klasyczna kwerenda bazodanowa. Więc wszystkie funkcje i opcje SQL są do dyspozycji. Tworzymy nowe kryterium Kryteria / Nowe kryteria (filtr wierszy)
Okno dodawania kryteriów
Kryterium zostało dodane – liczba wierszy uległa ograniczeniu tylko do wierszy niepustych…
Widok kwerendy po dodaniu pierwszego kryterium
Ponieważ rekordy zawierają jeszcze pierwotny wiersz nagłówkowy, poprawiamy dodatkowo kryteria, dodając kolejne – ograniczamy kwoty (przypominamy, że jest to pole tekstowe) tylko do tych, które zawierają ‘,’ (przecinek). Mogliśmy dodać takie kryterium od razu w poprzednim kroku, bo jest ono wystarczające do zapewnienia tych dwóch efektów naraz – zwykle jednak robi się to krokowo, tak, jak w przykładzie.
Teraz już wiersze kwerendy są tożsame z wierszami nas interesującymi.
Widok kwerendy po etapie dodawania kryteriów
Modyfikujemy (poprawiamy) pola
Przystępujemy do najtrudniejszego i najbardziej skomplikowanego etapu modyfikacji kwerendy.
Rozpocznijmy od owej ‘kwoty’, czyli pola tekstowego, zawierającego liczbę, która jest jednak tekstem i zawiera znaki separatorów – tysięcznego i dziesiętnego.
Separatory są i nie można ich usunąć funkcjami REPLACE() – tak byłoby najłatwiej. Jest w MS Jet taka funkcja, ale… nie działa. Próba zmiany formatu liczby z tekstowego na wartości liczbowe funkcją VAL() też się nie powiedzie, bo separatory są znakami, które nie są właściwie interpretowane.
Musimy więc tekst pola kwota wycinać, ale… liczby zapisuje się bez zer wiodących.
Czyli trzeba wycinać od końca. Ale – kolejny problem – liczby bez zer wiodących, tworzą tekst o zmiennej długości, np. „1.234,00” (8 znaków) i ”0,56” (4 znaki). Musimy więc pole KWOTA „wyrównać”, czyli spowodować, że będzie miało stałą szerokość – liczbę znaków bez spacji. Robi się to tak – do nowego pola naszej kwerendy wpisujemy formułę:
RIGHT('00000000' & kwota;10)
Otrzymujemy: „001.234,00” (10 znaków) i ”0000000,56” (10 znaków).
Ponieważ teksty dalej zawierają separatory, musimy wyciąć z tego pola tylko teksty bez separatorów – robi się to tak:
MID(RIGHT('00000000' & kwota;10);1;3) & MID(RIGHT('00000000' & kwota;10);5;3) & MID(RIGHT('00000000' & kwota;10);9;2)
Otrzymujemy następujące wartości – dalej tekstowe: „00123400” i ”000000056”. Zawierają one już wyłącznie cyfry, więc możemy całość zamienić na wartość liczbową funkcją VAL()
VAL(MID(RIGHT('00000000' & kwota;10);1;3) & MID(RIGHT('00000000' & kwota;10);5;3) & MID(RIGHT('00000000' & kwota;10);9;2))
Otrzymujemy wartości liczbowe: 123400 i 56. Są to jednak wartości zawyżone, gdyż, omijając separator dziesiętny, spowodowaliśmy, że wartości są „w groszach”. Jesteśmy już jednak pewni, że są to wartości liczbowe, więc, aby otrzymać wartości „w zł” możemy je podzielić przez 100.
VAL(MID(RIGHT('00000000' & kwota;10);1;3) & MID(RIGHT('00000000' & kwota;10);5;3) & MID(RIGHT('00000000' & kwota;10);9;2))/100
Formuła ta, pomimo swojej komplikacji, jest jednak dobra i poprawnie zamienia nam wszystkie wartości tekstowe na liczbowe.
Kolejne etapy dochodzenia do właściwego formatu pola liczbowego
Zmieniamy nazwę gotowego pola na KWOTA_OK i usuwamy kolumny pośrednie, aby sprawdzić, czy wszystko działa poprawnie…
Widok kwerendy z gotowym polem KWOTA_OK
Jak widać – działa.
Przystępujemy do zmiany formatu daty.
W naszym przykładzie data jest tekstem w formacie ‘RRRRMMDD’ bez separatorów.
Znowu musimy powycinać z tekstu odpowiednie człony (‘2011’,’09’,’05’), skleić otrzymane teksty (‘20110905’), dodać standardowe separatory daty, czyli ‘-‘ (myślniki) (‘2011-09-05’) i, na końcu, zamienić otrzymany tekst na wartość w formacie daty funkcją DATEVALUE()
Definicja pola poprawnej daty
Formuła:
DATEVALUE(MID(data;1;4) & '-' & MID(data;5;2) & '-' & MID(data;7;2))
Zmieniamy nazwę tego pola na DATA_OK.
Gotowa kwerenda
Usuwamy wszystkie kolumny pośrednie, otrzymując gotowy efekt.
Gotowa kwerenda
Po przesłaniu do Excela, wszystko jest interpretowane poprawnie.
Widok gotowej kwerendy w Excelu
Możemy się nawet pokusić o operacje bazodanowe na polach wartości – tu pokazano grupowanie (sumowanie) wartości według kont.
Widok kwerendy sumującej wartości według kont
Podsumowanie przykładu
Efekt jest piorunujący.
Dostęp do pliku tekstowego jest powtarzalny (wystarczy nacisnąć przycisk Odśwież dane w Excelu), jest bardzo szybki (kilkadziesiąt tysięcy obrabianych wierszy na sekundę), jest edytowalny (zawsze można się „cofnąć” do kwerendy i coś poprawić - Edytuj kwerendę)
Szczegółowy opis zawartości pliku SCHEMA.INI
[NazwaPlikuTekstowego.txt]
Określa nazwę pliku tekstowego, którego dana definicja dotyczy
ColNameHeader=False
Określa, czy plik tekstowy w pierwszym swoim wierszu ma (True) lub nie ma (False) nagłówka kolumn.
Nie polecamy stosować (‘True’) – dużo lepsze efekty daje własna definicja pól – patrz: poniżej. W sporadycznych przypadkach – gdy plik jest idealnie przygotowany do odczytu przez ODBC – można stosować.
Format=Delimited(;)
Możliwe formaty plików tekstowych to:
1. Plik rozdzielany standardowym separatorem systemu operacyjnego (w angielskiej wersji – przecinek, w polskiej – średnik)
Format=CSVDelimited
Nie polecamy stosować – raczej: Format=Delimited(znak)
2. Plik rozdzielany tabulatorem
Format=TabDelimited
3. Plik rozdzielany dowolnym separatorem (pojedynczy znak)
Format=Delimited(znak)
4. Plik rozdzielany spacjami, czyli tekst, wyglądający jak równe kolumny danych
Format=FixedLenght
Wymaga szczegółowej definicji pól (patrz niżej)
Ilość wierszy, na podstawie których sterownik „podejmuje decyzję” kwalifikacji typu pola
MaxScanRows=25
Raczej nie należy pozostawiać sterownikowi możliwości podejmowania jakichkolwiek decyzji – należy samodzielnie definiować pola.
Parametr może być pominięty lub mieć praktycznie dowolną wartość. Wartość 25 jest standardowa dla ODBC i często występuje w takich plikach. Parametr pomijalny.
CharacterSet=OEM
Parametr omówiony w przykładzie – strona kodowa znaków narodowych. Możliwe wartości: OEM, ANSI. Należy próbować wymiennie. Nie zawsze działa poprawnie.
NumberDigits=2
Parametr, określający ilość miejsc po przecinku w wartościach liczbowych. Ma znaczenie tylko przy polach typu Float. Standardowo równy 2. Autorzy nie spotkali się z inną wartością. Gdy odczyt, tak, jak w przykładzie, odbywa się pośrednio, czyli najpierw do pola typu znakowego, a potem przekształcenia do wartości liczbowej – parametr ten nie ma znaczenia i może być pominięty.
DecimalSymbol=,
Parametr, określający separator dziesiętny w wartościach liczbowych. Ma znaczenie tylko przy polach typu Float. Uwagi – jak wyżej.
ThousandSymbol=
Parametr niedziałający. Pomijalny.
NumberLeadingZeros=TRUE
Parametr, określający zera wiodące. Autorzy nie spotkali się z zastosowaniem. Pomijalny.
Col1=KONTO char Width 10
Definicja pierwszego pola danych. Musi mieć format, jak w powyższej linii.
- Col1- definicja kolumny 1.
- KONTO – przykładowa nazwa pola (kolumny),
- char – typ pola (używane wartości: char (znakowy), float (liczba), date (data))
- Width – szerokość pola dla pól typu char.
- 10 – ilość znaków, jak wyżej
Col2=
Col3=
…
Col9=
Col10=
Col11=
…
Definicje kolejnych pól. UWAGA – liczby – identyfikatory pól muszą być kolejnymi liczbami naturalnymi: Col1, Col2, Col3, … (nie może być przerwy w numeracji)
[NazwaKolejnegoPlikuTekstowego.txt]
Określa nazwę kolejnego pliku tekstowego, definiowanego przez ten plik SCHEMA.INI.
Plik SCHEMA.INI może zawierać wiele definicji odczytu różnych plików tekstowych.
Problemy
No i doszliśmy do sedna tematu. Problemy… są.
Bardzo rzadko udaje się dokonać tych wszystkich operacji bezproblemowo, więc, aby poczuć się w tym temacie w miarę swobodnie, należy zdawać sobie sprawę z pułapek. A te czyhają na każdym kroku.
Kilka z nich już omówiliśmy:
- Inne, niż TXT i CSV, rozszerzenia plików tekstowych
- Wiersze „śmieciowe”
- Separatory tysięczne
- Brak funkcji REPLACE()
Występuje jednak szereg problemów informatycznych. Po kolei…
Brak tekstowego źródła danych
Standardowo, gdy instalujemy Excela, na liście źródeł danych (okno Wybierz źródło danych) nie ma pozycji, odnoszącej się do plików tekstowych – najczęściej jest to lista, zawierająca pozycje: Excel, Access i DBF (dokładnie: <Nowe źródło danych>, Pliki programu Excel, Baza danych MS Access i dBase files). Zdefiniowanych źródeł danych może być więcej lub mniej, mogą też mieć inne nazwy, np. w języku angielskim.
Dodajemy nowe źródło danych
W oknie Wybierz źródło danych wybieramy <Nowe źródło danych>.
Nadajemy własną nazwę nowemu źródłu danych (pkt.1.), może być ona dowolna, ale najlepiej dostosować się do standardu, czyli do takiej nazwy, jaka zostałaby nadana przez instalator Excela – Text files.
W kolejnym polu, z listy rozwijalnej, wybieramy Microsoft Text Driver (pkt.2.)
Okno Utwórz nowe źródło danych.
Gdy, po dokonaniu wyboru, lista zostanie zwinięta, klikamy przycisk Połącz (pkt.3.) – tu: niewidoczny. Widać go na widoku poniżej, w tle.
Okno ODBC - ustawienia dla tekstu
(Uwaga – w tym kroku może nastąpić problem)
W powyższym oknie klikamy tylko przycisk OK. Gdy wrócimy do poprzedniego okna również wystarczy potwierdzić przyciskiem OK.
Możliwe trudności
Często się jednak zdarza, że ten krok nie zadziała, albo, gdy nawet zadziała i uda nam się wyjść z tego okna – nie będzie można połączyć się ze źródłem Text files.
Należy wtedy USUNĄĆ nowo stworzone źródło danych Text files, powtórzyć kroki do wyświetlenia tego okna, ale, w tym oknie, odznaczyć opcję Użyj bieżącego katalogu, kliknąć przycisk Wybierz katalog i tam, ręcznie, wskazać katalog z plikami tekstowymi. Dalsze kroki, czyli potwierdzanie – bez zmian. Gdy taka sytuacja się zdarzy – czyli źródło danych będzie wymagało wskazania katalogu – występuje dodatkowa trudność obsługowa – gdy mamy różne pliki tekstowe w różnych katalogach, musimy tworzyć nowe, osobne, źródła danych do każdego katalogu. Nie jest to jednak problem.
Źródło danych „nie widzi” pliku
ODBC jest technologią bardzo starą (20 lat) i już nieco odstaje od nowoczesnych standardów.
Pomimo wprowadzania coraz to nowych i nowocześniejszych wersji Windows i Excela, sterowniki nie są (z)aktualizowane i musimy się do tego dostosować.
Np. w przypadku plików płaskich (TXT, DBF), ale też, czasami, wszystkich baz plikowych (np. Access, Excel) występuje problem, gdy chcemy połączyć się z plikiem (tabelą) lub bazą danych (szczególnie dotyczy to plików tekstowych), a w pełnej ścieżce dostępu do takiego pliku (bazy) występuje znak niedozwolony przez stare wersje Windows (sic!), np. spacja, przecinek, myślnik lub kropka. Czasami dotyczy też polskich znaków.
Stanowi to czasami problem, gdyż przyzwyczailiśmy się już, że nazwy folderów i nazwy plików są długie i znaki takie zawierają.
Opisywany problem może więc wystąpić, gdy umieścimy plik tekstowy w folderze i podfolderach …\Documents and Settings\Użytkownik\Pulpit, …\Moje dokumenty\..., …\Program files\..., itp.
„Starzy informatycy” to wiedzą doskonale i nigdy nie tworzą folderów z danymi z takimi nazwami – radzimy brać z nich przykład.
Czasami też – ten problem występuje szczególnie przy dostępie do plików typu DBF – nie można nazwać pliku nazwą dłuższą niż 8 znaków – był kiedyś taki standard…
Może jest to śmieszne z dzisiejszego punktu widzenia, ale tak jest.
Gdy zmienimy nazwy katalogów i plików, problem powinien ustąpić.
Cudzysłowy w tekście
Niekiedy się zdarza, że pliki tekstowe są wykonane również przez ODBC. A ODBC, gdy pliki zapisuje, czasami zapisuje dane w cudzysłowach (zwykle znak ”). Linie tekstu wyglądają więc czasami tak:
”501”;”12/45”;”20110905 ”;”1.234,56”
A, z kolei, po odczycie, wartość tekstowa pola KONTO, zamiast wartości tekstowej 501 ma wartość ”501”, czyli zawiera cudzysłowy.
Wartość bez cudzysłowów otrzymujemy wtedy formułą w SQL
=MID(pole;2;LEN(pole)-2)
Problem czasami jest jednak jeszcze poważniejszy, a mianowicie, niekiedy, w polach tekstowych występują cudzysłowy wewnątrz pola, np.
”501”;”Koszty obiektu ”nazwa obiektu””;”12/45”; …
W takim przypadku (rzadko się on zdarza, na szczęście) należy zlecić informatykowi napisanie/kupić/zdobyć prościutki program w dowolnym języku programowania (może być makro VBA, który, linia po linii, odczyta plik tekstowy, zapisując jednocześnie inny plik tekstowy, i dokona w każdej linii binarnej(!) zamiany tekstowej: cudzysłów na ciąg pusty. Linia tekstu wygląda wtedy tak…
501;Koszty obiektu nazwa obiektu;12/45; …
(Czyli nie posiada cudzysłowów w ogóle) – … i jest bezproblemowo odczytywalna przez ODBC.
Wiele plików tekstowych
Obsługę wielu plików tekstowych zapewnia możliwość definicji wielu bloków (nazw plików) w pliku SCHEMA.INI (opisane powyżej).
Jak jednak postąpić, gdy pliki są cykliczne, tj. otrzymujemy wydruki o nazwie ze standardowym przedrostkiem i np. datą w nazwie pliku?
Nie pozostaje nic innego jak przewidzieć te nazwy na okresy przyszłe i rozbudować plik SCHEMA.INI, np. tak:
[ZestawienieObrotow_201101.txt]
Tu: Definicja odczytu (identyczna dla wszystkich plików)
[ZestawienieObrotow_201102.txt]
Tu: Definicja odczytu (identyczna dla wszystkich plików)
[ZestawienieObrotow_201103.txt]
Tu: Definicja odczytu (identyczna dla wszystkich plików)
Plik SCHEMA.INI może zawierać definicje odczytu dowolnej ilości plików tekstowych.
Konieczność współdzielenia pliku z kwerendą
Patrz: Standardowe problemy przy stosowaniu MS Query
Niestandardowe znaki w tekście, np. tabulatory
Problem jest, w zasadzie, podobny do problemu „cudzysłowy w polach tekstowych”, opisanego powyżej. Gdy w tekście występują znaki niestandardowe, np. tabulatory, ale też znaki o „dziwnych” kodach – kopiuje się taki znak do Excela, pisze formułę =KOD(„Tu się kleja skopiowany znak”) i otrzymuje kod znaku w systemie ASCII. Następnie należy postąpić podobnie, jak w powyższym przykładzie (pozyskanie programu zmieniającego znaki), który, tym razem zmieni znak o uzyskanym kodzie na spację. Dalej – standardowo.
Jak tego użyć w praktyce?
Niestety, użycie praktyczne jest, w standardowej wersji ODBC w Excelu, dość ograniczone. Nie jest to jednak ograniczenie ODBC, tylko jego excelowej implementacji w programie MS Query.
Główne ograniczenie polega na tym, że kwerenda zawiera SZTYWNĄ definicję nazwy pliku, którego dotyczy i nie można tego sparametryzować, tj. podawać z zewnątrz.
W MS Query można stosować odczyt kwerenda-plik. Działa, ale niedosyt parametryzacji powoduje stosunkowo małą ilość zastosowań praktycznych.
Odczyt przez program VBA lub jakikolwiek inny nie ma tego ograniczenia.
Można jednak tego użyć w sposób profesjonalny, niestety przy standardowej wersji Excela nie można tego zautomatyzować, nawet makrem. Polega na… definicji odczytu, a następnie użyciu pewnej „sztuczki” języka SQL, polegającej na eksporcie odczytywanej tabeli do pliku innego formatu, np. Excela. Jest to możliwe!
Należy, po dokonaniu odczytu i sprawdzeniu go w Query, nacisnąć przycisk SQL na pasku narzędzi, skopiować zapytanie, a następnie uruchomić opcję Plik / Wykonaj SQL.
Opis sposobu dokonania takiego eksportu znajduje się w rozdziale MS Query.
Powtórzmy:
Odczyt jest powtarzalny i niezmienny, wygodny i bardzo szybki (kilkadziesiąt tysięcy wierszy na sekundę). Jest wiec o co walczyć.