Banner

štvrtok 24. apríla 2014

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;
 
image
 
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) );
 
image
 
Pre jednoduché overenie použijeme skript, ktorým vyberieme všetky data z tabuľky tab:
 
select*from tab;
 
image
 
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:
 
image

 

Tento list si vo VBA editore premenujte na wsVLOZ takto:

image
V tomto štádiu potrebujete aktivovať knižnicu Microsoft activeX Data Objects 2.8 Library:
 
image
image
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
 
image

 

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

downloads_normal
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)).ClearContents
End 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:
 
image
 
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).
 
image
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 Winking smile).
 
EK logo FB

utorok 15. apríla 2014

VBA funkcie INSTR a REPLACE

 

Určite mi dáte za pravdu, že pri práci s datami je hľadanie znaku v bunke a nahradzovanie písmena, poprípade výrazu jednou z rutín, ktoré každý analytik ovláda.

Považujme riešenie pomocou štandarných excelovských funkcií za samozrejmosť a ukážeme si, ako hľadať a nahradzovať znaky pomocou procedúry VBA. Ak sa zameráme na ukážku vo forme nasledujúceho obrázku, zistíme, že niektorré výrazy obsahujú anomáliu v tvare %%.

image

Našou úlohou bude:

1) Nájsť vrámci stĺpca v každej bunke túto anomáliu: %%

2) Ak sa daná anomália v bunke nájde, tak Excel vymení “-“ za “_”

Pre nájdenie konkrétneho znaku (výrazu) použijeme VBA funkciu INSTR:

INSTR( štart ; text ; hľadaný znak alebo výraz )

Kde atribút štart je počiatočné písmenko, od ktorého má funckia prehľadávať výraz. Tento atribút nie je povinný. Ak ho teda nepoužijeme, funkcia začne automaticky prehľadávať od prvého písmenka. Funckia obsahuje ešte jeden nepovinný atribút, ktorým je spôsob porovnávania. Používa sa vbBinaryCompare (rozlišuje malé a veľké písmenka) a vbTextCompare (nerozlišuje malé a veľké písmenka).

V pripravenom template bude použitá hodnota referenčnej bunky Cells(i, 1).Value, kde finálny výraz bude vyzerať nasledujúco:

InStr(Cells(i, 1).Value, "%%")

Pre nahradenie spomenutých čiarok použijeme funkciu REPLACE:

Replace( text ; hľadaný text ; nový text )

Ako to bolo v predchádzajúcej funckii, aj REPLACE má o niečo viac atribútov, ktoré sú nepovinné. Ja spomeniem štart, ktorý sa zadáva hneď po atribúte nový text.a pomocou ktorého v konečnom dôsledku definujeme, od ktorého písmenka, vrámci textu má funkcia nahradzovať. Aplikácia v našom príklade mať nasledujúcu podobu:

Replace(Cells(i, 1), "-", "_")

Nakoniec stačí pridať zopár premenných, FOR cyklus a podmienku IF. Výsledný kód vyzerá nasledovne:

Sub NajstNahradit()

Dim i As Integer
Dim UB As Integer

    UB = Cells(Rows.Count, 1).End(xlUp).Row
   
    For i = 2 To 10
        If InStr(Cells(i, 1).Value, "%%") > 0 Then
            Cells(i, 1) = Replace(Cells(i, 1), "-", "_")
       
End If
    Next i


End Sub

Celá procedúra spolu s príkladom je k dispozícii v nasledujúcom template. Stačí kliknúť na obrázok Download. V prípade, dotazov sa neváhajte opýtať na Facebooku (Klikni na logo Excel-u vedľa Download), stačí dať like a napísať správuWinking smile.                 

                          downloads_normalEK logo FB

utorok 1. apríla 2014

Ako zmeniť “.” za “,” v čísle tak, aby sme zachovali čo najpresnejšie desatinné čísla

 

Prejdeme priamo k problému. Máte situáciu, keď sa Vám z databázy alebo iného zdroja dostanú čísla v nasledujúcej forme:

image

Pokiaľ nedisponujete EN verziou Excelu a potrebujete zachovať čo najlepšiu presnosť, tak môžete mať seriózny problém. Dôvod? Pri použití možnosti Find / Replace sa dostanete do situácie, keď si náš milovaný Excel zmyslí, že číslo zaokrúhli ( prvý prípad na 10,6), pritom je jedno či to použijete manuálne alebo v rámci VBA procedúry. V prípade, že chcete použiť iba funkcie, stačí vedľa napísať kombináciu funkcií:

=IFERROR(REPLACE(A2;FIND(“.”;A2;1);1;”,”);A2)

 image

Avšak ak si na obrázku všimnete stĺpec C, v ktorom delíme hodnotu zo stĺpca B číslom jeden, Excel si pamätá 13 desatinných miest. V rámci niekoľkohodinového špekulovania na internete som nenašiel spôsob, akým by sa mohli nastaviť takéto možnosti. Každopádne, ak máte podobný problém a potrebujete na to použiť makro v rámci procedúry – skúste si nasledujúce riešenie:

Sub Makro()

Application.ScreenUpdating = False
Dim i
Dim UB
Dim temp

UB = Cells(Rows.Count, 1).End(xlUp).Row

 
    Cells(2, 3).FormulaR1C1 = _
    "=IF(IFERROR(FIND(""."",RC[-2]),0)=0,RC[-2]/1,ROUND(LEFT(RC[-2],FIND(""."",RC[-2])-1)&"",""&MID(RC[-2],FIND(""."",RC[-2])+1,LEN(RC[-2])-FIND(""."",RC[-2]))/1,LEN(MID(RC[-2],FIND(""."",RC[-2])+1,LEN(RC[-2])-FIND(""."",RC[-2])))))"
    Cells(2, 3).Select
    Selection.Copy
    Selection.AutoFill _
    Destination:=Range(Cells(2, 3), Cells(UB, 3))
    Range(Cells(2, 3), Cells(UB, 3)).Copy
   
    Selection.PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False

 
Application.ScreenUpdating = True
End Sub

Ukážkový súbor je k dispozícii kliknutím na obrázok Download, v prípade dotazov som k dispozícii na FB.

                    downloads_normalEK logo FB