Exportujeme data z reportu do databázy MySQL (krok za krokom)

V rámci analytického života vo firme sa stáva, že niektoré “tabuľky” z reportov, ktoré máte na starosti každý mesiac potrebujete “skladovať” niekde mimo Excel. Dôvody sú rôzne.

 

No asi najdôležitejším je ochrana dát pred povedzme neodbornou dodatočnou manipuláciou kolegov, poprípade neočakávaným poškodením excelovských súborov. Tým samozrejme nechcem podceňovať týmovú prácu s dátami, ale niektoré data proste potrebujeme mať v prehľadnej forme uložené, aby sme mohli efektívne vytvárať dodatočné analýzy.

 

Ja som sa rozohdol predstaviť procedúru, ktorá vloží data z tabuľky v istom liste do jednoduchej databázy v MySQL. Majme teda vytvorenú jednoduchú databázu s názvom temp.

 

Skript pre jej vytvorenie v MySQL vyzerá takto: create database temp;

 

 

Takto vytvorená databáza neobsahuje žiadne tabuľky a tým pádom ani relácie medzi nimi. Použijeme MySQL skript, ktorým zvolíme vytvorenú databázu pre manipuláciu: use temp;

 

Vytvoríme jednoduchú tabuľku s názvom tab, ktorá bude obsahovať stĺpce Meno a Priezvisko pomocou nasledujúceho skriptu: create table tab ( Meno varchar(30), Priezvisko varchar(30) );

 

 

Pre jednoduché overenie použijeme skript, ktorým vyberieme všetky data z tabuľky tab:

 

select*from tab;

 

 

Z predhádzajúceho obrázku je vidieť, že tabuľka je prázdna. Teraz prejdeme do prostredia Excel-u a pripravíme si podklad pre export do databázy. V liste s názvom VLOZ si pripravte nasledujúcu tabuľku:

 

 

Tento list si vo VBA editore premenujte na wsVLOZ takto:

V tomto štádiu potrebujete aktivovať knižnicu Microsoft activeX Data Objects 2.8 Library:

 

Táto knižnica nám umožní používať objekty pre pripájanie a manipuláciu s vonkajšími zdrojmi (databázami). Posledná technická prekážka pred samotným makrom je inštalácia MySQL Excel connectoru. Důležité je však zvoliť správnu verziu. Záleží či máte 32 alebo 64 bitovú verziu Excelu. Tento atribút si v MS Excel 2013 zistíte nasledovne:

 

FILE –> ACCOUNT –> ABOUT EXCEL

 

 

MySQL Excel Connector si sitahnete po kliknutí na nasledujúci obrázok:

Stiahnite a nainštaluje Connector a môžeme sa následne pustiť do VBA kódu:

Sub InsertData()
    Dim rs As Object
    Dim cn As Object
   
    'Vytvorenie pripojenia na MySQL
   Set rs = CreateObject("ADODB.Recordset")
   Set cn = CreateObject("ADODB.Connection")
   
    'Pripajanie sa na MySQL
    cn.Open "DRIVER={MySQL ODBC 5.1 Driver};" & _
        "SERVER=localhost;" & _
        "DATABASE=temp;" & _
        "USER=názov užívatela;" & _
        "PASSWORD=heslo do MySQL;" & _
        "Option=3"
   
    'Samotné vloženie dát - vytvorenie QUERY
    With wsVLOZ
        For rowCursor = 2 To 5
            strSQL = "INSERT INTO temp.tab (Meno, Priezvisko) " & _
                "VALUES ('" & esc(.Cells(rowCursor, 1)) & "', " & _
                "'" & esc(.Cells(rowCursor, 2)) & "')"
            rs.Open strSQL, cn
        Next
    End With
   
    'Ohlásenie, že data boli vložené
    MsgBox "Data boli vložené!", vbExclamation
   
    'Odstránenie vložených dát
    Range(Cells(2, 1), Cells(Cells(Rows.Count, 2).End(xlUp).Row, 2)).ClearContentsEnd Sub

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

V tomto kóde by som chcel poukázať na dôležitosť správnej úpravy červene sfarbených častí kódu. Poslednou úlohou bude vytvoriť tlačítko a priradiť mu makro s názvom InsertData.

 

Po kliknutí na tlačítko sa vykoná procedúra s nasledujúcou hláškou:

 

 

Na záver sa vstupné data v Exceli vymažú. Ak použijete MySQL skript pre zobrazenie dát z tabuľky tab, zistíte že sa dáta z Excel-u exportovali do databázy temp (viď nasledujúci obrázok).

 

Dúfam, že sa Vám všetky kroky podarilo zvládnuť a tým pádom preniknúť tak trochu do záludností práce s MySQL prostredníctvom MS Excel. Taktiež nezabúdajte na Facebook – skupinu (kliknite na nasledujúce logo ).