Moderný reporting pracujeme s MySQL (plníme tabuľky)

 

Nachádzame sa v štádiu, keď máme vytvorené a editované tabuľky. Tým pádom ich potrebujeme naplniť a prepojiť (použiť FOREIGN KEYS). Možnosti vloženia dát do tabuliek sú dve. Prvá je cez webový formulár, ktorým sa však nebudem zaoberať.

Avšak transport dát z excelu do databázy som rozobral v predchádzajúcom článku. Dnes preto navrhujem pozrieť sa danému makru na zúbok, upraviť si kód pre naše potreby a naplniť vytvorené tabuľky:

Prvou časťou VBA riešenia bola nasledujúca funkcia:

Function esc(txt As String)
    esc = Trim(Replace(txt, " ' ", " \' "))
End Function

Táto funkcia bola určená len na to, aby prípadnú anomáliu – znak: ‘ nahradila za \‘ (Inak povedané, apostrof v jednej bunke môže znamenať dva atribúty v jednom stĺpčeku, čo my nechceme. Tým pádom sa snažím daný reťazec rozbiť a upozorniť na to (VBA by pri takomto SQL skripte vyhodilo chybu a prerušilo vkládanie do DB).

Čo sa týka samotného string – SQL skriptu, spojením funkcií:

strSQL = "INSERT INTO sales.sales (Naklady, Predajca, Mesto, Auto, Diskont, Predaj, Mesiac, Rok) " & _
                "VALUES ('" & esc(.Cells(rowCursor, 1)) & "', " & _
                "'" & esc(.Cells(rowCursor, 2)) & "', " & _
                "'" & esc(.Cells(rowCursor, 3)) & "', " & _
                "'" & esc(.Cells(rowCursor, 4)) & "', " & _
                "'" & esc(.Cells(rowCursor, 5)) & "', " & _
                "'" & esc(.Cells(rowCursor, 6)) & "', " & _
                "'" & esc(.Cells(rowCursor, 7)) & "', " & _
                "'" & esc(.Cells(rowCursor, 8)) & "')"

Vytvorím nasledujúci skript pre MySQL:

INSERT INTO sales.sales (Naklady, Predajca, Mesto, Auto, Diskont, Predaj, Mesiac, Rok) VALUES ('454', '12', '1', '1', '1', '2', '1', '2013')

Tento skript funguje nasledovne: “Vložte do tabuľky Databáza.Tabuľka (Vymenovanie stĺpcov v danej tabuľke) hodnoty (konkrétne hodnoty v rovnakom poradí, ako názvy stĺpcov v predchádzajúcej zátvorke)”.

Tento string si necháme cez for cyklus vytvoriť pre každý riadok separátne a potom v makre vyvolávame connection procedúru spolu s exekúciou hotového skriptu. Nasledujúca ukážka nápaja Excel do MySQL databázy:

Dim rs As Object
Dim cn As Object
   
    Set rs = CreateObject("ADODB.Recordset")
    Set cn = CreateObject("ADODB.Connection")
   
    cn.Open "DRIVER={MySQL ODBC 5.1 Driver};" & _
        "SERVER=localhost;" & _
        "DATABASE=sales;" & _
        "USER=root;" & _
        "PASSWORD=Vlož svoje heslo;" & _
        "Option=3"

S takto vytvorenými objektmi si už len definujeme stringovú premennú (Insert Into ukážka), s ktorou aktivujeme prepojenie a exekuujeme SQL skript. Nasledujúci kód je jednoduchá ukážka použitia SQL skriptu vo forme stringu a jeho exekúcia:

 
strSQL = "set foreign_key_checks=0;"
rs.Open strSQL, cn

Premenná strSQL má vytvorený SQL skript pre vypnutie kontroly správnosti sekundárnych kľúčov (Túto kontrolu pri jednorázovom importe dát do databázy vypínam):

set foreign_key_checks=0;

Keďže naša databáza obsahuje okrem hlavných – záznamových tabuliek tiež normalizačné, ukážeme si, ako naplniť normalizačnú tabuľku a následne si pripravíme Excel aplikáciu pre vklad do záznamovej tabuľky.

Pozrime sa opäť na štruktúru databázy a vezmime si v úvahu normalizačnú tabuľku discount:

Predpokladajme, že táto tabuľka vychádza z požiadaviek Marketingu a Predaja, kde máme štandardizované zľavové hladiny:

V ľudskom ponímaní sme napr. 15% zľavu zašifrovali číslom 4, 10% zase číslom 3. MySQL skript pre túto tabuľku bude vyzerať:

insert into sales.discount (Zlava) values (0),(0.05),(0.1),(0.15);

Poznámka: Všimnite si, ako som spomínané zľavy zapísal a taktiež, že som namiesto “,” použil “.”.

Ak by sme vo WokrBench-i nechali vyselektovať všetko, z predchádzajúcej tabuľky, output by vyzeral takto:

Vklad pre ostatné normalizačné tabuľky vyzerá takto:

1) dealer:

insert into sales.dealer (Predajca) values ('Peter'),('Martin'),('Roman'),('Milan'),('Lukáš'),('Eva'),('Martina'),('Angelika'),('Natália'),('Jana'),('Mária'),('Alžbeta'),('Markéta');

2) city:

insert into sales.city (Mesto) values ('Praha'),('Brno'),('Plzeň'),('Olomouc'),('Mladá Boleslav'),('Pardubice'),('Hradec Králové'),('Liberec');

3) car:

insert into sales.car (Auto, Cena) values ('Citigo',200000),('Fabia',340000),('Roomster',360000),('Rapid',430000),('Yeti',440000),('Octavia',540000),('Superb',670000);

Pre záznamové tabuľky som pripravil jednoduchú obmenu VBA úvodného kódu:

Select Case ActiveSheet.Name
                Case "COST"
                    For rowCursor = 2 To UB
                        strSQL = "INSERT INTO sales.cost (Predajca, Mesiac, Rok, Mzda, Sprava,Bonus) " & _
                        "VALUES ('" & esc(Cells(rowCursor, 1)) & "', " & _
                        "'" & esc(Cells(rowCursor, 2)) & "', " & _
                        "'" & esc(Cells(rowCursor, 3)) & "', " & _
                        "'" & esc(Cells(rowCursor, 4)) & "', " & _
                        "'" & esc(Cells(rowCursor, 5)) & "', " & _
                        "'" & esc(Cells(rowCursor, 6)) & "')"
                        rs.Open strSQL, cn
                    Next
                Case "SALES"
                    For rowCursor = 2 To UB
                        strSQL = "INSERT INTO sales.sales (Naklady, Predajca, Mesto, Auto, Diskont, Predaj, Mesiac, Rok) " & _
                        "VALUES ('" & esc(Cells(rowCursor, 1)) & "', " & _
                        "'" & esc(Cells(rowCursor, 2)) & "', " & _
                        "'" & esc(Cells(rowCursor, 3)) & "', " & _
                        "'" & esc(Cells(rowCursor, 4)) & "', " & _
                        "'" & esc(Cells(rowCursor, 5)) & "', " & _
                        "'" & esc(Cells(rowCursor, 6)) & "', " & _
                        "'" & esc(Cells(rowCursor, 7)) & "', " & _
                        "'" & esc(Cells(rowCursor, 8)) & "')"
                        rs.Open strSQL, cn
                    Next
                Case Else
                    MsgBox "Data môžete exportovať iba z listov COST alebo SALES", vbCritical
                    Exit Sub
            End Select

Táto na prvý pohľad komplikovaná procedúra nie je nič iné ako select case procedúra, kde sa pýtam na názov aktívneho listu. Ak je aktívny list COST, tak použijem insert into skript pre tabuľku sales.cost, v opačnom prípade ak sa list volá sales, tak použijeme SQL pre tabuľku sales.sales. Ak by som sa nachádzal v inom liste, tak ma Excel upozorní, že musím byť v jednom z daných listov (COST alebo SALES).

Túto vymoženosť snaď najlepšie demonštruje template – file, kde si v liste PARAMETERS stačí upraviť log-in DB údaje a kliknutím vložiť data.

Poznámka: VBA, resp. Excel je v tomto výhodný v zmysle, že si vstupné data môžete ľubovoľnou procedúrou skontrolovať či sú v poriadku (kreativite sa možnosti nekladú – ja v snahe zachovať pochopiteľný obsah som nič dodatočné neaplikoval).

Na záver budem konštatovať, že databázka je naplnená a v budúcom článku definujeme kľúče medzi tabuľkami a začneme selektovať.

V prípade dotazov som plne k dispozícii .