Import pliku tekstowego za pomocą ADO

W odpowiedzi na uwagę czytelnika zamieszczam modyfikację kodu importującego dane z pliku tekstowego lub pliku HTML.

Zmianie uległ w zasadzie connection string oraz zapytanie SQL. Potrzeby okazał się też plik Schema.ini. Plik ten mówi bibliotece ADO w jaki sposób ma interprewtować dane zawarte w pliku tekstowym. jak wiadomo niektóre pliki są rozdzielane znakiem średnika, inne przecinkiem a jeszcze inne za pomocą tabulatora. Dodatkowo w tym pliku możemy określić typ danych oraz nazwy importowanych kolumn.

UWAGA: Informacje z tego pliku są wykorzystywane między innymi w momencie importu danych do Excel-a za pomocą kreatora importu danych zewnętrznych.

Sub Raport(Target As Range, SQL As String, Optional Name)

Dim sConn As String
Dim sPath As String
Dim sName As String
Dim qt As QueryTable
Dim wks As Excel.Worksheet

If IsMissing(Name) Then
' sprawdź czy taki obiekt nie istnieje
sName = "Lista"
Else
sName = CStr(Name)
End If

On Error Resume Next
If ThisWorkbook.Names(sName).Name <> "" Then
If Err.Number = 0 Then
sName = sName & "_1"
End If
Err.Clear
End If

On Error GoTo ERR_Handler:
' ścieżka do pliku roboczego
sPath = "E:\test\"

' Connection String
sConn = "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sPath & ";" & _
"Mode=Share Deny None;Extended Properties=""text;HDR=Yes;FMT=Delimited"";" '& _

' skoroszyt roboczy
Set wks = Target.Parent

' sprawdź czy obiekt istnieje
If wks.QueryTables.Count > 0 Then
' generuje błąd jak QT nie ma, działanie celowe
Set qt = wks.QueryTables(sName)
Else
' generuj błąd braku obiektu
Err.Raise 9
End If

With qt   ' tworzymy obiekt QueryTable we wskazanej lokalizacji
.CommandType = xlCmdSql ' informacja o tym że korzystamy z polecenia SQL
.CommandText = SQL ' Komenda SQL, gdzie [Sheet1$] skoroszytem, po wstawieniu np. [lista] pobieramy dane z zakresu nazwanego
.Name = sName ' nazwa obiektu Querytable
.Refresh BackgroundQuery:=False 'pobieramy dane
End With

END_Handler:

Exit Sub

ERR_Handler:

Select Case Err.Number
Case 9
' tworzenie obiektu
Set qt = wks.QueryTables.Add(Connection:=sConn, Destination:=Target)
Resume Next

Case Else
MsgBox Err.Description
Resume END_Handler:
End Select

End Sub

' przykład wykorzystania
Sub test_2()
' wyswietl elementy ze skoroszytu Sheet1, znak $ konieczny do tego żeby JET wiedział że to cały skoroszyt
Raport Sheet3.Range("A1"), "select * from plik#csv where jeszcze_coś_innego > #2009-07-05#", "Wynik_3"

End Sub

Przykładowy plik Schema.ini. Zawiera on deklarację dla jednego pliku plik.csv.

[plik.csv]
Format=Delimited(;)
MaxScanRows=25
CharacterSet=ANSI
Col1=Coś Char
Col2=coś_innego Char
Col3=jeszcze_coś_innego Date

UWAGA: Plik schema.ini musi być w tym samym katalogu co importowany plik tekstowy.

W przypadku gdyby w katalogu znajdowało się wiele plików tekstowych i każdy wymagał by określenia parametrów importu możemy dodać kolejne sekcje. Tak jak w kolejnym przykładzie:

[plik.csv]
Format=Delimited(;)
MaxScanRows=25
CharacterSet=ANSI
Col1=Coś Char
Col2=coś_innego Char
Col3=jeszcze_coś_innego Date

[plik_2.csv]
Format=Delimited(;)

Nie ma konieczności deklarowania wszystkich parametrów. W przypadku gdy tego nie zrobimy ADO wykorzysta wartości domyślne.

Przykładowy plik (plik.csv) który importowałem:

osoba;miasto;data
ala;Łódź;2009-07-01
ola;Warszawa;2009-07-02
jola;Lublin;2009-07-03
ala;Warszawa;2009-07-04
ola;Lublin;2009-07-05
jola;Warszawa;2009-07-06
ala;Lublin;2009-07-07
ola;Warszawa;2009-07-08
jola;Lublin;2009-07-09
ela;Warszawa;2009-07-10

Modyfikacja Connection string-a oraz zapytania SQL polega na tym że nie deklarujemy jawnie nazwy pliku z którego pobieramy dane, deklarujemy tylko katalog. Pliki które znajdują się w tym katalogu traktujemy natomiast jak niezależne obiekty w "bazie danych" i na nich wykonujemy operacje.

UWAGA: zamiast kropki w nazwie pliku stosujemy znak #

Na koniec dodam że w zapytaniach możemy korzystać z pełnego zestawu funkcji dostępnych w dialekcie JET SQL.