Export MySQL do Excel přes JSON

PhpMyAdmin nabízí možnost exportovat tabulku do CSV ve formátu pro Excel. I když je tato možnost šikovná pro rychlý export, ve složitějších případech nelze použít (alespoň snadno). Například nejde jednoduše načíst CSV v UTF-8 do Excelu a pokud nějaký sloupec obsahuje odřádkování (třeba poštovní adresu), může to CSV formát úplně rozbít.

JSON v Excelu

Naštěstí Excel 2016 nabízí možnost načíst JSON soubor a MySQL nabízí možnost exportovat tabulku do JSON.

Navíc JSON soubor lze v PHP nebo Javascriptu (NodeJS) převést na objekt a dále s ním pracovat (např. můžete převést řádky adresy na pole).

I když Microsoft přidal možnost načíst JSON soubor, tak už neztrácel čas popisem postupu.

Export do JSON přes PHPMyAdmin

V PhpMyAdmin otevřete tabulku a klikněte na Exportovat. Zvolte Vlastní způsob exportu a zvolte formát JSON (pokud ho v seznamu nemáte, aktualizujte PhpMyAdmin).

Ověřte, že se soubor exportuje v UTF-8.

V závislosti na verzi PhpMyAdmin mohou být k dispozici další nastavení. Například Vytvořit pěkný JSON naformátuje soubor tak, aby se dal dobře číst i bez importu do dalšího programu (vloží řádkování a odsazení). Některé verze PhpMyAdmin dokonce nabízí možnost přejmenovat sloupce tak, aby po exportu dávali větší smysl.

Na konec klikněte na Proveď a soubor se stáhne.

Následně budete muset otevřít JSON soubor v textovém editoru a umazat z něj hlavičku (komentář), kterou PhpMyAdmin automaticky vkládá, ale není kompatibilní s JSON formátem.

Zpracování JSON souboru

Před importem do Excel můžete chtít JSON soubor nějak upravit pomocí skriptu. Ideální pro práci je JavaScript v NodeJS (JSON.parse()/JSON.stringify()), ale stejně dobře si s ním poradí i PHP (funkce json_decode()/json_encode()) nebo jiný jazyk, který umí JSON zpracovat.

Příklad pro rozdělení řádek adresy uložené v JSON přes PHP:

<?php //PHP 7.x
$name = $argv[1];
$file = file_get_contents($name);
$json = json_decode($file, true);

foreach ($json as &$row) {
    $addr = explode("\r\n", $row['address']);
    $row['name'] = $addr[0];
    unset($addr[0]);
    $row['adresa_rows'] = $addr;
}

file_put_contents($name, json_encode($json));

Uvedený kód rozparsuje JSON, a položku address, která obsahuje celou adresu s více řádky, rozdělí na jméno (první řádek) a adresu uloženou jako pole s jednotlivými řádky adresy.

Import do Excel 2016

Když máte JSON s požadovanou strukturou, můžete ho otevřít v Excelu.

Založte prázdný sešit (Soubor – Nový – Prázdný sešit) a na záložce Data zvolte Načíst data – Ze souboru – Z formátu JSON. Zvolte soubor a klikněte na Importovat. Otevře se editor Power Query, který slouží pro převod cizích formátů do formátu Excel.

V hlavním okně se vám zobrazí řádky (položky z JSON soubor) s hodnotou Record. To znamená, že Excel rozpoznal víceúrovňové pole nebo vnořený objekt v JSON formátu, ale neví, jak s ním budete chtít pracovat.

Pokud importujete jiný JSON formát (ne z MySQL), může se vám zobrazit jen pár řádků. Například pokud máte JSON vrácený z nějakého API, které vrátilo data ve formátu {success:true, result:{data:[ ... ]}}, klikněte nejprve na „Record“ u řádky result a následně na Record u řádky data. Pro vrácení o úroveň zpět použijte seznam napravo Použitý postup.

Na záložce Tranformace (pozor, záložky mohou být dvě se stejným názvem) klikněte na Do tabulky, což vytvoří z JSON formátu základní datovou tabulku. V dialogu Do tabulky zvolte Žádný oddělovač a Nadbytečné řádky nastavte na Zobrazit jako chyby.

Zobrazí se tabulka s Column 1 a hodnotami Record. V hlavičce sloupce si všimněte tlačítka se šipkami. Klikněte na něj a zobrazí se seznam sloupců nalezených v záznamech. Zaškrtněte, které sloupce chcete přenést do tabulky.

Pod seznamem je volba Použít původní název sloupce jako předponu. Pro první úroveň, kde se sloupec jmenuje Column 1, to nemá smysl, ale třeba u adresy, kde jde o jednotlivé řádky to naopak smysl má, aby se sloupce pojmenovali „adresa.1„, „adresa.2„, atd. Po kliknutí na OK se do tabulky přidají sloupce z rozbalených záznamů (Column 1).

Pokud některý ze sloupců obsahuje další úroveň pole (nebo objektu), opět se zobrazí hodnota Record. Klikněte tedy u daného sloupce opět na tlačítko se šipkami v hlavičce sloupce a opakujte postup s volbou sloupců (zde již můžete nechat zaškrtnuté Použít původní název sloupce jako předponu).

Až budete mít všechny úrovně JSON dat rozbalené do sloupců, můžete sloupce přeuspořádat tak, že kliknete na hlavičku sloupce (CTRL+klik pro výběr více sloupců) a následně je za hlavičku přesunout na požadované místo. Dvojklikem na název sloupce ho může přejmenovat. Klikem na levou ikonu v hlavičce “ABC 123“ můžete změnit typ dat například na číslo nebo datum.

Pro převod MySQL boolean (TinyInt s hodnotami 0 a 1) musíte nejprve převést sloupec na číslo a pak teprve na Pravda/Nepravda a v dotazu zvolit Přidat nový krok. Pokud potřebujete převést desetinná čísla mezi formáty (s tečkou, čárkou, s oddělovači tisíců, apod.), zvolte Použít národní prostředí a v dialogu zvolte formát (Desetinné číslo) a prostředí (např. Angličtina – Spojené státy pro zpracování čísel s desetinnou tečkou místo čárky).

Pravou ikonou se šipkou můžete přidat filtr nebo data seřadit.

Až budete mít sloupce nastavené jak potřebujete, klikněte na Zavřít a načíst na záložce Home. Tím se data vloží do otevřeného sešitu jako nový list. Pozor na to, že podle množství dat může vkládání trvat i několik minut.

Se sloupci můžete dále pracovat pomocí vytvořených hlaviček s filtry nebo dalšími funkcemi.

Nakonec soubor uložte jako *.xlsx. Pozor na to, že pokud budete chtít později upravovat způsob, jakým jsou data importována (záložka Data – Dotazy a připojení), budete muset společně s *.xlsx uložit (odeslat) i soubor *.JSON. Pokud později soubor JSON změníte (přidáte do něj záznamy), můžete volbou Data – Aktualizovat vše nová data přidat do tabulky.

Napsat komentář

Vaše emailová adresa nebude zveřejněna. Vyžadované informace jsou označeny *

Tato stránka používá Akismet k omezení spamu. Podívejte se, jak vaše data z komentářů zpracováváme..