Exportujeme data z reportu do databázy MySQL (ošetrujeme duplicity)

 

V predchádzajcom článku som sa podrobne venoval problematike nastavenia databázy v MySQL a zároveň ukázal, ako cez VBA sa pripojiť a zároveň vložiť data z tabuľky. Avšak v tomto prípade sa nebudem zaoberať otázkou vhodnosti ošetrenia duplicít, prejdem priamo k riešeniu.

V predchádzajúcom článku sme si pomociu SQL dotazu vytvorili tabuľku s názvom tab, ktorá sa skladala len z dvoch premenných – Meno a Priezvisko.

Túto tabuľku, predtým než začneme si musíme tak trochu upraviť na nasledujúci tvar (pridať stĺpec ID – číselné hodnoty).

Túto úpravu realizujeme nasledujúcim kódom v MySQL command: ALTER TABLE TAB ADD COLUMN ID INT; Ak si všimnene nový stĺpec ID, vidíme že pre každého užívateľa hodnotu NULL, t.z. že predchádzajúce záznamy nemali vložený atribút ID.

Vymažme staré záznamy príkazom v MySQL command: DELETE FROM tab; získame tak úplne prázdnu tabuľku – pripravenú pre vkládanie dat.

Prejdeme do Excel prostredia a v liste s názvom DATA si pripravme tabuľku s hodnotami:

Potom pomocou záložky DEVELOPER si pripravte tlačítko, pomocou ktorého budete realizovať export dat do databázy.

 

Poznámka: toto tlačítko vrátane formátovania som pripravil pomocou ActiveX možnosti. Tým pádom ak ste v DESIGN MODE, jeho rozkliknutím sa dostanete do VBA editora kde do procedúry dopíšete názov makra na export dat:

Private Sub CMB_vlozDATA_Click()
    Call VlozData
End Sub

Teraz stačí už len vložiť modul a v ňom nadefinovať funkciu, ktorá odsráni medzery z textov a základné premenné:

Dim oConn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim rsc As ADODB.Recordset

Function esc(txt As String)
' Očistí text o medzery
    esc = Trim(Replace(txt, "'", "\'"))
End Function

Ďalej vložíme nasledujúce makro, ktorým sa pripojíme na databázový server (nezabudnite doplniť prihlasovacie údaje – červene označené):

Sub pripojDB()
   
'pripajanie sa na DB
    Set oConn = New ADODB.Connection
    oConn.Open "DRIVER={MySQL ODBC 5.1 Driver};" & _
    "SERVER=NázovServera;" & _
    "DATABASE=NázovDatabázy;" & _
    "USER=Užívateľ;" & _
    "PASSWORD=Heslo;" & _
    "Option=3"
   
End Sub

Posledné marko bude zvolávať predchádzajúce a bude sa skladať z dvoch stringových premenných, ktorými sú MySQL query na overenie prítomnosti duplicity:

SELECT COUNT(ID) AS idpocet FROM tab WHERE ID=’1’

Druhý MySQL query vkladá jednotlivé riadky ako záznamy do DB:

INSERT INTO tab (Meno, Priezvisko, ID) VALUES (‘Peter’,’Tabiš’,’1’);

Procedúra je ošetrená tak, že ak sa výsledok prvého query – teda idpocet nerovná nule, tak sa prenesie na ďalší riadok a v duplicitnom označí červenou farbou bunku v ID stĺpci. VBA riešenie vyzerá nasledovne:

Sub VlozData()

On Error GoTo Koniec
Dim Data As Worksheet
Dim SQLkod As String
Dim riadok As Integer
Dim duplicita As Integer

'Nastaví list so vstupnými datami
Set Data = Worksheets("Data")
Set rs = New ADODB.Recordset 
Set rsc = New ADODB.Recordset
riadok = 2
dupicita = 0
           
'Pripojí sa na server
pripojDB
   
    With Data
        While Trim(.Cells(riadok, 1)) <> "" 'pokračuj pokial 'did' bunky sú neprázdne
           
           'Ak je recordset pre ovedenie duplicit aktívny, tak ho zavrie
            If rsc.State = adStateOpen Then
                rsc.Close
            End If
            'Definícia query pre MySQL - ako overiť, či sa záznam nachádza v DB
            SQLkod = "SELECT COUNT(ID) AS idpocet FROM tab WHERE ID='" & Trim(.Cells(riadok, 3)) & "'"
           'Obnovenie recordsetu rsc - zistíme či sa záznam nachádza v DB - výstup je premenná "idcount"
            rsc.Open SQLkod, oConn, adOpenStatic, adLockOptimistic
           
            'Označí rsc za aktuálny
            rsc.MoveFirst
           
           'Ak sa dané ID nachádza v DB, vyfarbí ID bunku červenou farbou a zaznamená počet duplicít
            If rsc.Fields("idpocet") > 0 Then
                .Cells(riadok, 3).Interior.Color = RGB(255, 0, 0)
                dupicita = dupicita + 1
                GoTo SkipInsert
            End If
            'zavrie počítací recordset
            rsc.Close

            ' Definícia query pre MySQL - Ako vložiť riadok, teda záznam
            SQLkod = "INSERT INTO tab (Meno, Priezvisko, ID) " & _
            "VALUES ('" & esc(Trim(.Cells(riadok, 1).Value)) & "', '" & _
            esc(Trim(.Cells(riadok, 2).Value)) & "', '" & _
            esc(Trim(.Cells(riadok, 3).Value)) & "')"
           
            'Ak je recordset pre vklad dát aktívny, tak ho zavrie
            If rs.State = 1 Then
                rs.Close
            End If
            'Obnovenie recordsetu rs - vložíme riadok / záznam
            rs.Open SQLkod, oConn, adOpenDynamic, adLockOptimistic
SkipInsert:
            riadok = riadok + 1
        Wend
        .Select
    End With
   
    MsgBox "Počet celkových vložených riadkov je: " & riadok - 2 - dupicita & vbCrLf & " - červené bunky v liste Data sú duplicitné hodnoty", vbInformation, "DB výsledok"

Koniec:
'Chybové hlásenie
    If Err.Description <> "" And Err.Source <> "" Then
        MsgBox Err.Description, vbCritical, Err.Source
    End If
End Sub

Výsledok si overíme tak, že po kliknutí sa nám objaví nasledujúca hláška:

A dáta sa budú nachádzať v MySQL:

Ak by sme chceli vložiť tie isté data ešte raz, po kliknutí sa objaví nasledujúca hláška a duplicitné riadky sa nám sfarbia na červeno:

Samotná tabuľka v MySQL ostane nezmenená. V konečnom dôsledku si môžete nastaviť rôzne spôsoby overenia dát. No predtým než ich vložíte do databázy, vo väčšine prípadov nie je potrebné neustále s ňou komunikovať. Dôležité je nastaviť procedúru tak, aby pripájaním zbytočne nezahlcovala server.

V prípade dotazov alebo rady som k dispozícii na FB – stačí kliknúť na nasledujúce Excel logo