W codziennej pracy z danymi bardzo często zdarza się korzystać z różnych plików do importu/zasilania danych. Wbudowane narzędzia importu sprawdzają się bardzo dobrze w przypadku gdy mamy do czynienia z prawdziwym plikiem CSV o poprawnej strukturze. Często jednak musimy poddać obróbce źródłowe pliki, aby można było je zaimportować.
Przedstawiam sposób, który przy użyciu zapytań SQL wybierze nam interesujące dane z pliku.
Dane wejściowe w postaci pliku txt:
Title **************************************************** Title of page1 |---------------------------------| |a1|b1 |c1 |1 | 1.234,00 | |---------------------------------| |a2|b2 |c2 |2 | -2.345,66 | |---------------------------------| |a3|b3 |c3 |3 | 0,01 | |---------------------------------| Page1 .................................................... Title of page2 |---------------------------------| |a4|b4 |c4 |4 | 123,99 | |---------------------------------| |a5|b5 |c5 |5 | 1.987,00 | |---------------------------------| |a6|b6 |c6 |6 |1.123.123,00 | |---------------------------------| Page2 ....................................................
Tak naprawdę interesują nas linie 6,8,10,17,19,21 cała reszta jest zbędna… Normalnie można by napisać kod VBA, który to przerobi i zaimportuje, ale można inaczej
Rozwiązanie
Tworzymy plik schema.ini w katalogu gdzie znajduje sie plik do importu
[test.txt] ColNameHeader=False Format=Delimited(|)
W linii pierwszej należy zmienić nazwę pliku, w omawianym przypadku jest to test.txt. Linia druga to informacja, że plik nie zawiera nazw kolumn w pierwszym wierszu, a 3 linia to wskazanie, że kolumny są rodzielane znakiem “|”.
Pełne informacje o mozliwości schema.ini pod adresem:
http://msdn.microsoft.com/en-us/library/windows/desktop/ms709353(v=vs.85).aspx
Teraz pora na stworzenie kwerendy, która wybierze nam dane z pliku, aby ją wprowadzić tworzymy nową kwerendę i przechodzimy do widoku SQL.
Wprowadzamy następujące zpuytanie:
SELECT F2 as Pole1 ,F3 as Pole2 ,F4 as Pole3 ,F5 as Pole4 ,cdbl(Replace(F6,".","")) as Pole5 FROM [Text;DATABASE=d:\devproject\main].test.txt where len(f6)>0
w lini 8 zmieniamy ścieżkę i nazwę pliku, linia 10 to warunek na to aby wybrać interesujące nas dane. Następnym krokiem jest uruchomienie tej kwerendy.
Dalszą obróbkę pomijam, można ją zapisać i wykorzystać do dodania do istniejącej tabeli, można też poprostu bezpośrednio w niej dopisać instrukcje insert into
Automatyzacja
Jak powszechnie wiadomo, gdy mamy pobierać tak przygotowane dane to z reguły tych plików jest więcej i mają różne nazwy, można oczywiście powtarzać w/w czynności dla każdego pliku z osobna, ale można też zautomatyzować to działanie poprzez procedurę w VBA. Poniżej zamieszczam procedurę, która pobierze wszystkie pliki txt z przekazanego katalogu, stworzy dla każdego pliku odpowiedni schema.ini, a następnie wgra dane do tabeli, tworzą kolumnę idpliku z nazwą importowanego pliku tekstowego. Dla każdego katalogu zostanie stworzona jedna tabela tab + kolejny numer zaczynając od zera:
Sub PrzetworzTXT(ByVal sciezka As String) Dim plik As String Dim katalog() As String Dim i As Integer Dim createtab As Boolean Dim sql As String Dim schemaini As String 'szablon pliku schema.ini schemaini = "[plik]" & vbNewLine & _ "ColNameHeader = False" & vbNewLine & _ "Format=Delimited(|)" 'Pobierz listę katalogów katalog = Split(sciezka, ";") For i = 0 To UBound(katalog) 'usun tabele jeśli istnieje On Error Resume Next DoCmd.RunSQL "drop table tab" & i On Error GoTo 0 createtab = True 'rozpocznij pobieranie plików tekstowych plik = Dir$(katalog(i) & "*.txt") Do Until Len(plik) = 0 Debug.Print plik 'Zapisz schema.ini dla aktualnego pliku Open katalog(i) & "schema.ini" For Append As #1 Print #1, Replace(schemaini, "plik", plik) Close #1 'stwórz zapytanie, jeśli pierwsze to tworzące tabele, jeśli nie to dodające dane If createtab Then createtab = False sql = "SELECT """ & plik & """ as IDPliku, F2 As Pole1,F3 as Pole2,F4 as Pole3,F5 as Pole4,cdbl(Replace(F6,""."","""")) as Pole5" & _ " into tab" & i & _ " FROM [Text;DATABASE=" & katalog(i) & "]." & plik & _ " where len(f6)>0" Else sql = "insert into tab" & i & _ " SELECT """ & plik & """ as IDPliku, F2 As Pole1,F3 as Pole2,F4 as Pole3,F5 as Pole4,cdbl(Replace(F6,""."","""")) as Pole5" & _ " FROM [Text;DATABASE=" & katalog(i) & "]." & plik & _ " where len(f6)>0" End If Debug.Print sql 'uruchom zapytanie do importu pliku DoCmd.RunSQL sql 'usun schema.ini Kill katalog(i) & "schema.ini" plik = Dir() Loop Next End Sub
W celu wywołania procedury korzystamy z kodu:
PrzetworzTXT "d:\devproject\temp\1\;d:\devproject\temp\2\"
Mam nadzieje, ze komuś sie przyda…
Witam, ciekawa procedura, mam tylko jedno pytanie jak ją zmodyfikowa aby nie było konieczności potwierdzania importu kolejnych plików txt do bazy. W momencie wystąpienia wiecej niż jednego pliku w katalogu trzeba ciągle potwierdzac czy wstawic dane do tabeli. Przy dużej ilości plików uciążliwe.
Dodaj linijkę
na początku procedury, lub przed puszczeniem sql-a w kodzie (docmd.runsql)
To jest to, czego mi brakowało. Dziękuję.
Cieszę się, że komuś się przydało
Przydało sie i to bardzo !. Dzięki i pozdrawiam.