Import danych z pliku TXT ADO   strona główna:
A po co ten Excel ;-)
 
Dziś chciałem zaprezentować narzędzie do importu danych z pliku txt. Swego czasu pisałem sporo takich procedur i uznałem że przydało by się takie  
narzędzie do którego przekazałoby się argumenty dotyczące importu a sama procedura byłaby uniwersalna. Powstał więc taki twór i siedzi w moim  
archiwum. Najwyższy czas się nim podzielić :-)  
 
Option Explicit   
 
Sub ImportZTXT_ADO(rngKomCel As Excel.Range, _   
                   strTXTFilePath As String, _   
                   strTXTFileName As String, _   
                   strSQL As String, _   
                   ParamArray vShemaInfo() As Variant)   
    On Error GoTo ImportZTXT_ADO_Error   
 
    Const adOpenStatic = 1   
    Const adStateOpen = 1   
    Const adEditNone = 0   
 
    Dim strSchemaIniFilePath As String, vItem As Variant, nr As Integer   
    Dim objRecordset As Object, strConnectionString As String   
 
    nr = FreeFile   
    strSchemaIniFilePath = strTXTFilePath & "\schema.ini"   
    Open strSchemaIniFilePath For Output As #nr   
        Print #nr, "[" & strTXTFileName & "]"   
        For Each vItem In vShemaInfo   
            Print #nr, vItem   
        Next   
    Close #nr   
 
    strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _   
                          "Data Source=" & strTXTFilePath & ";" & _   
                          "Extended Properties=""text"""   
 
    Set objRecordset = CreateObject("ADODB.Recordset")   
    With objRecordset   
        .Open strSQL, _   
              strConnectionString, _   
              adOpenStatic   
        If Not (.BOF And .EOF) Then rngKomCel.CopyFromRecordset objRecordset   
    End With   
      
ImportZTXT_ADO_Exit:   
    On Error Resume Next   
      
    If Not (objRecordset Is Nothing) Then   
        With objRecordset   
            If CBool(.State And adStateOpen) Then   
                If .EditMode <> adEditNone Then .CancelUpdate   
                .Close   
            End If   
        End With   
        Set objRecordset = Nothing   
    End If   
      
    VBA.Kill strSchemaIniFilePath   
    Exit Sub   
 
ImportZTXT_ADO_Error:   
    MsgBox "Byk nr: - " & Err.Number & vbCrLf & vbCrLf & _   
            Err.Description, vbExclamation, "VBAProject - ImpZTXTADO"   
    Resume ImportZTXT_ADO_Exit   
 
End Sub   
 
Procedurę omówię fragmentami:  
1. Utworzenie pliku schema.ini i zapis do niego informacji nt. zasad importu danych.  
 
    nr = FreeFile   
    strSchemaIniFilePath = strTXTFilePath & "\schema.ini"   
    Open strSchemaIniFilePath For Output As #nr   
        Print #nr, "[" & strTXTFileName & "]"   
        For Each vItem In vShemaInfo   
            Print #nr, vItem   
        Next   
    Close #nr   
 
Do importu z pliku txt koniecznym jest utworzenie tzw. sterownika źródła danych tekstowych - plik schema.ini Określane są w nim cechy   
źródła danych: sposób formatowania pliku tekstowego, sposób jego odczytu w trakcie importu... Plik ten należy stworzyć na tej samej ścieżce co plik  
z którego dane zamierzamy importować. Jednak żeby nie bawić się za każdym razem w tworzenie i usuwanie takiego pliku procedura tworzy go sama  
na podstawie argumentów: strTXTFilePath, strTXTFileName i (ParamArray) vShemaInfo  
     Gdyby nie utworzyć pliku schema.ini wszystkie kolumny w pliku txt byłyby potraktowane jako jedna kolumna i tak zostałyby zaimportowane.  
Następnym krokiem po takim imporcie jest rozdzielenie danych przy pomocy narzędzia Tekst jako Kolumny. Skoro więc można tak to po co sobie  
sprawę utrudniać przez schema.ini?? Jeżeli nie wskażemy na etapie importu informacji dot. choćby separatora czy faktu posiadania nagłówków przez  
kolumny danych w pliku txt jedyne zapytanie jakie można by wykonać to np.: SELECT * FORM plik.txt. Nie ma mowy o imporcie tylko wybranych   
kolumn czy nadawanie warunków w klauzuli WHERE. Powiedzmy że mamy w pliku 15 kolumn i 100k wierszy w tym 99% danych niepotrzebnych do   
bieżącej analizy bo chcemy z tego tylko 3 kolumny i wiersze spełniające pewien warunek nadawany na kolumnę 4'rtą. Importować całość danych i w  
Excel'u wyciągać istotne dane? Bez sensu! Nie mówią już o kłopotach z importem tak dużej ilości danych w wersjach Excel'a wcześniejszych niż 2007.   Inicjowanie sterownika źródła danych tekstowych
Myślę że powyższym przykładem uzasadniłem powody dla których warto przyjrzeć się zasadom tworzenia i wykorzystania schema.ini   ------>> :-)  
 
2. Utworzenie obiektów połączenia i recordsetu ADODB oraz import danych do rngKomCel  
 
    strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _   
                          "Data Source=" & strTXTFilePath & ";" & _   
                          "Extended Properties=""text"""   
 
    Set objRecordset = CreateObject("ADODB.Recordset")   
    With objRecordset   
        .Open strSQL, _   
              strConnectionString, _   
              adOpenStatic   
        If Not (.BOF And .EOF) Then rngKomCel.CopyFromRecordset objRecordset   
    End With   
 
3. W ramach obsługi błędów zamknięcie i zwolnienie pamięci utworzonych obiektów ADODB oraz usunięcie schema.ini  
 
ImportZTXT_ADO_Exit:   
    On Error Resume Next   
      
    If Not (objRecordset Is Nothing) Then   
        With objRecordset   
            If CBool(.State And adStateOpen) Then   
                If .EditMode <> adEditNone Then .CancelUpdate   
                .Close   
            End If   
        End With   
        Set objRecordset = Nothing   
    End If   
      
    VBA.Kill strSchemaIniFilePath   
    Exit Sub   
 
I przykładowa pocedura startowa  
 
Sub test()   
 
    ImportZTXT_ADO [A1], _   
                   "C:\Documents and Settings\Kuchtowie\Pulpit", _   
                   "test.txt", _   
                   "SELECT * FROM test.txt;", _   
                   "Format = Delimited(,)", "DecimalSymbol = .", _   
                   "CharacterSet = 1250", "ColNameHeader = False"   
End Sub   
 
A import części kolumn pod pewnym warunkiem gdy separatorem danych jest (Tab) a kolumny posiadają nagłówki?? :-)  
 
Sub test2()  
    ImportZTXT_ADO [A1], _  
                   "C:\Documents and Settings\Kuchtowie\Pulpit", _   
                   "test2.txt", _  
                   "SELECT [dane1], [dane2], [dane4] " & _  
                       "FROM test2.txt " & _  
                       "WHERE [dane2] BETWEEN #07/19/2010# AND #07/21/2010#;", _  
                   "Format = TabDelimited", "ColNameHeader = True"  
End Sub  
 
W zapytaniu dotyczącym dat w kolumnie [dane2] należy używać amerykańskiego formatu dat tj. MM/dd/yyyy