Wykorzystanie wget.exe do pobierania danych raportowych

W poprzednim poście pisałem o możliwości pobrania danych ze strony która wymaga logowania za pomocą loginu i hasła wpisywanego do formularza. Rozwiązanie bazowało na kodzie w VBA, co w pewnych sytuacjach jest nieco kłopotliwe do wykorzystania.

Dziś chciał bym przedstawić rozwiązanie bazujące na narzędziu wget.exe dostępnego dla platformy *nix jaki Windows. Narzędzie dla Windows jest do pobraniu np. tu. Narzędzie to nie wymaga instalowania, wystarczy że skopujemy plik wget.exe do jakiegoś katalogu.

Metoda jaką się posłużymy wymaga posiadania wiedzy o tym co jest przesyłane do strony z której chcemy coś pobrać. Przeglądarki webowe komunikują się z serwerami za pomocą komunikatów tekstowych które możemy podejrzeć za pomocą odpowiedniego narzędzia. Jednym z takich narzędzi może być rozszerzenie do przeglądarki Firefox: LiveHeader. Rozszerzenie to pokazuje pełną komunikację między przeglądarką a serwerem na poziomie nagłówków. Dzięki temu można podejrzeć co jest wysyłane do serwera WWW np. po naciśnięciu guzika wyślij lub loguj. Dla przykładu to co zaobserwujemy po kliknięciu Loguj w przeglądarce:

Na tym rysunku widzimy adres strony jaką wywołaliśmy po wyciśnięciu Loguj

A tu widzimy jaki nagłówek został przesłany do serwera.

Uzbrojeni w takie informacje możemy wykorzystać pewne specyficzne mechanizmy narzędzia wget.exe do zalogowania a następnie do pobrania raportu.

Przykładowy skrypt takiej znajdujący się np. w pliku webget.cmd to:

@ECHO OFF
wget --save-cookies cookies.txt --post-data "login=janek&password=123456789" -q "http://localhost/index.php?k=auth&a=auth&target="
wget --load-cookies cookies.txt --post-data "filtr_1=201006&filtr_2=844&sekcja=b&raport_n=compl&=undefined" --output-document=raport.csv -q "http://localhost/?k=raporty&a=GetReport&content=xls"

Wyjaśnienia mogą wymagać poszczególne opcje jakie zostały użyte podczas takiej operacji:

• --save-cookies cookies.txt - zapisuje wszystkie informacje pobrane podczas logowania do pliku
• --post-data "dane=vos" - wysyła dane do serwera udając formularz. Dane które są w przykładzie pochodziły z rysunku 2
• -q - wget.exe działa w trybie cichym, to znaczy że nie będzie wyświetlał wszystkich komunikatów
• "http://strona/" - adres URL strony którą pobieramy
• --load-cookies cookies.txt - pobieramy informacje o logowaniu z wcześniej przygotowanego pliku
• --output-document=raport.csv - zapisujemy wynik w pliku o konkretnej nazwie

Proszę zauważyć że zarówno dane dla --post-data jaki zam adres URL znajdują się między znakami " " - jest to konieczne do prawidłowego działania skryptu

Szybkie przekodowanie pliku teksowego

Częstą zmorą podczas pracy z plikami jest ich kodowanie. Czasem można sobie poradzić ręcznie jakimś prostym narzędziem np. Notepad++, czasem importujemy plik do Access-a i eksportujemy w żądanym kodowaniu. Te metody się sprawdzają do momentu gdy nie zderzymy się z plikiem wielkości ~1GB. Cóż można wtedy zrobić? Ano skorzystać z dobrodziejstw darmowego narzędzia ICONV dla platformy Win32 czyli Windowsa.
Pierwotnie narzędzie to było dostępne dla systemów rodziny *nix, lecz w chwili obecnej możemy się cieszyć że jest dostępne też dla nas szarych użytkowników okieek.

Pliki wykonywalne Iconv można ściągnąć z adresu: http://gnuwin32.sourceforge.net/packages/libiconv.htm. Do wyboru mamy paczkę zip lub instalator exe. W zależności od wyboru ściągamy żądany plik i wypakowywujemy lub instalujemy.

Załóżmy że plik iconw.exe znajduje się w katalogu: c:\dekoder\, zaś pliki do dekodowania znajdują się w katalogu d:\pliki\. To jak wykorzystać to narzędzie do tego żeby przekodować nasze pliki np. ze strony kodowej UTF-8 do CP1250 (Strona kodowa Windows). Należy wykonać polecenie z wiersza poleceń:

c:\dekoder\iconv.exe -f UTF-8 -t CP1250 d:\pliki\plik.txt > d:\pliki\plik.cp1250.txt

Konstrukcja taka to proste wykonanie instrukcji iconv z przekierowaniem strumienia ">" do nowego pliku. Jest niezwykle wydajna i na średniej klasy sprzęcie przekodowanie pliku o wielkości setek megabajtów zajmuje tylko kilkanaście sekund.

Lista dostępnych stron kodowych jest dostępna po wykonaniu polecenia:

c:\dekoder\iconv.exe -l 

Jednym z ciekawszych zastosowań takiej metody jest przekodowanie pliku przed importem do MSSQl-a. Jest to konieczne gdyż MSSQL nie wspiera tak jakbyśmy chcieli. Cóż można zrobić? ano można użyć procedury systemowej xp_cmdshell to przekonwertorownia pliku.

declare @cmd varchar(2000)
set @cmd = 'c:\dekoder\iconv.exe -f UTF-8 -t CP1250 d:\pliki\plik.txt > d:\pliki\plik_cp1250.txt'
exec xp_cmdshell @cmd

Jeżeli nie będziemy mieli aktywnej procedury xp_cmdshell możemy ją włączyć w następujący sposób:

EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE

Konwertowanie UTF-8 do Unicode w VBA

Kiedyś znalazłem kod do konwertowania tekstu w UTF-8 do Unicode. Przydaje się to czasem podczas przetwarzania danych ze stron web.

Private Const CP_UTF8 = 65001

Private Declare Function MultiByteToWideChar Lib "kernel32" ( _
                                             ByVal CodePage As Long, ByVal dwFlags As Long, _
                                             ByVal lpMultiByteStr As Long, ByVal cchMultiByte As Long, _
                                             ByVal lpWideCharStr As Long, ByVal cchWideChar As Long) As Long

Public Function sUTF8ToUni(bySrc() As Byte) As String
' Converts a UTF-8 byte array to a Unicode string
    Dim lBytes As Long, lNC As Long, lRet As Long

    lBytes = UBound(bySrc) - LBound(bySrc) + 1
    lNC = lBytes
    sUTF8ToUni = String$(lNC, Chr(0))
    lRet = MultiByteToWideChar(CP_UTF8, 0, VarPtr(bySrc(LBound(bySrc))), lBytes, StrPtr(sUTF8ToUni), lNC)
    sUTF8ToUni = Left$(sUTF8ToUni, lRet)
End Function

Logowanie do formularza web

Dziś zaprezentuję metodę logowani się do stron internetowych/intranetowych zabezpieczonych formularzem logowania. Jest to w zasadzie standardowy sposób zabezpieczania aplikacji przed nieautoryzowanym dostępem. O ile jest to dobre z punktu widzenia security, to z punktu widzenia usera który musi klikać w ileś tam formularzy dziennie jest to droga przez mękę.

Dlatego proponuję ułatwić sobie życie za pomocą poniższego zestawu:

Function httpSessionRequest(theStep, method, url, data, cookie, viewState)

    Dim HTTPReferrer As String
    Dim postVars As String
    Dim XMLHTTP As Object
    Dim strHeaders As String
    Dim hArr() As String
    Dim kk As Long
    Dim theCookie As String
    Dim mycookie As String


    If Len(cookie) = 0 Then cookie = "dummy=dummy;"
    HTTPReferrer = Trim(url)
    postVars = Trim(data)

    Set XMLHTTP = CreateObject("MSXML2.serverXMLHttp")
    XMLHTTP.Open method, Trim(url), False

    If UCase(method) = "POST" Then
        XMLHTTP.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
    End If
    
    XMLHTTP.setRequestHeader "Referer", HTTPReferrer    'just in case the server cares
    XMLHTTP.setRequestHeader "Cookie", "excuse the Microsoft bug"
    XMLHTTP.setRequestHeader "Cookie", cookie

    XMLHTTP.send postVars

    'wait for response
    While XMLHTTP.readyState <> 4
        XMLHTTP.waitForResponse 1000
    Wend
    strHeaders = XMLHTTP.getAllResponseHeaders()

    hArr = Split(strHeaders, "Set-Cookie: ")
    For kk = 1 To UBound(hArr)
        theCookie = Left(hArr(kk), InStr(hArr(kk), "path=/") - 2)
        mycookie = mycookie & " " & theCookie
    Next

    If Len(mycookie) = 0 Then mycookie = cookie

    Select Case CInt(theStep)
        Case 1
            httpSessionRequest = mycookie
        Case 2
            httpSessionRequest = XMLHTTP.responsetext
        Case 3
            httpSessionRequest = XMLHTTP.responseStream
        Case 4
            httpSessionRequest = XMLHTTP.responseBody
        Case 5
            httpSessionRequest = XMLHTTP.responseXML
        Case 6
            httpSessionRequest = XMLHTTP
        Case Else
            httpSessionRequest = XMLHTTP.responsetext
    End Select

    Set XMLHTTP = Nothing
End Function

Public Function URLEncode( _
   StringVal As String, _
   Optional SpaceAsPlus As Boolean = False _
) As String

  Dim StringLen As Long: StringLen = Len(StringVal)

  If StringLen > 0 Then
    ReDim result(StringLen) As String
    Dim i As Long, CharCode As Integer
    Dim Char As String, Space As String

    If SpaceAsPlus Then Space = "+" Else Space = "%20"

    For i = 1 To StringLen
      Char = Mid$(StringVal, i, 1)
      CharCode = Asc(Char)
      Select Case CharCode
        Case 97 To 122, 65 To 90, 48 To 57, 45, 46, 95, 126
          result(i) = Char
        Case 32
          result(i) = Space
        Case 0 To 15
          result(i) = "%0" & Hex(CharCode)
        Case Else
          result(i) = "%" & Hex(CharCode)
      End Select
    Next i
    URLEncode = Join(result, "")
  End If
End Function

Function WriteStream(file As String, body)
    Dim oStream As Object
    Set oStream = CreateObject("ADODB.Stream")
    oStream.Open
    oStream.Type = 1
    oStream.Write body
    oStream.SaveToFile "D:\header_.gif"
    oStream.Close
    Set oStream = Nothing
End Function

Sub test()

    Dim Filename As String
    Dim baseURL As String, url1 As String, url2 As String, url3 As String
    Dim Data1 As String
    Dim nodata As String, noCookie As String, noViewState As String
    Dim theCookie As String
    Dim finalHTML As String
    Dim bytes() As Byte

    baseURL = "http://192.168.1.103/"    'This is to fix any broken images in the output.

    url1 = baseURL & "index.php" ' strona służąca do pobrania sesji
    url2 = baseURL & "?k=auth&a=auth&target=" ' strona logowania
    url3 = baseURL & "static/images/header.gif" ' strona

    Data1 = "login=rachwprz&password=thepass"

    theCookie = httpSessionRequest(1, "GET", baseURL, nodata, noCookie, noViewState)
    finalHTML = httpSessionRequest(2, "POST", url2, Data1, theCookie, noViewState)
    
    bytes = httpSessionRequest(4, "POST", url3, Data1, theCookie, noViewState)

    Filename = "D:\header_.gif"
    
    If Dir(Filename) <> "" Then Kill (Filename)

    WriteStream Filename, bytes

    
End Sub

Dla wyjaśnienia dodam tylko:
funkcja httpSessionRequest - jest uniwersalną metodą logowania i pobierania danych ze strony web za. W zależności od parametru theStep uzyskamy pożądany efekt. Przykładem może być pobranie wartości binarnej a następnie zapisanie jest za pomocą WriteStream
parametry przekazywane za pomocą metody POST nie muszą być traktowane funkcją URLEncode