Import plików CSV

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…

  1. 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.

  2. Dodaj linijkę

    DoCmd.SetWarnings False

    na początku procedury, lub przed puszczeniem sql-a w kodzie (docmd.runsql)

  3. To jest to, czego mi brakowało. Dziękuję.

  4. Przydało sie i to bardzo !. Dzięki i pozdrawiam.

Reply to Certa ¬
Cancel reply

NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

WordPress SEO fine-tune by Meta SEO Pack from Poradnik Webmastera
Skip to toolbar