Jak načítat data ze složky - Excel Power Query

Úvodem do načítaní dat ze složky

V tomto článku se zaměřím na načítaní dat ze složky. Využijeme možnost, které nabízí v Excel s Power Query. Pro přehlednost je článek rozdělen na kapitoly:

• Data - ať máte co načítat
• Načíst data ze složky
• Varianty načtení a výhody / nevýhody
  • Kombinovat
  • Načíst
  • Transformovat
• Zavřít a načíst - do Excel jako propojení
• Načteno
• Aktualizace
• Související články
• Ke stažení

Poznámka: Předpokládám Excel verze 2010 a novější. Od Excel 2016 standartní součástí, do straších verzí si budete muset implementovat (nainstalovat) viz odkaz v kapitole související články.

Data

Než začnete testovat načítání ze složky, je potřeba mít data ve složce (adresáři), která budete načítat (například txt nebo csv soubory). Ukázka jak mohou soubory ve složce vypadat (ukázka z průzkumníka, kdo má raději jiný souborový manažer, může vidět jiné uspořádání, ale výsledek je stejný).

Jednotlivé soubor jsou naprosto stejné (týká se typu a uspořádání sloupců, data sjou pochopitelně různá). Jde o textový soubor, kde jednotlivé položky jsou odděleny středníky (v adresáři se nenacházejí jiné typy souborů). Jelikož jde o testovací ukázky, mají zdrojová data jen několik sloupců (jedná se o ukázku načtení, tak ať vidíte na první pohled výsledek a můžete dále testovat co, jak, kdy, proč). Pokud načítání porozumíte, není problém načíst i podstatně větší soubor (klidně přes milion řádků).

Poznámka: Jde jen o ukázku dat, klidně si můžete vygenerovat (nebo použít vaše) data s více jak milionem řádku, ale pro ukázky a ať si nezničím server, kde hostuji dávám k dispozici malé soubory (nádherně se ukazuje, co a jak se načte). Bude-li velký zájem, možná vygeneruji velký datový soubor a uložím data na ulozto (nemám tak velký prostor na serveru), nebo jinou úschovnu. ;)

Načíst data ze složky

Pro českou verzi Excel. Z karty Data v sekci Načíst a transformovat data klik na ikonu Načíst data z menu vyberete: Ze souboru a následně Ze složky. Pozor někdy může jít o druhou sekci a v některých o první.

V anglické verzi opět karta Data v sekci Get & Transform Data v sekci klik na ikonu New Query z menu From File a následně From Folder. Poroz také v anglické verzi může jít až o drouhou sekci.

V dialogovém okně Složka (Folder) vyplníte přes ikonu Procházet... cestu je složce, zobrazí se v textovém poli: Cesta ke složce

Po kliku OK obdržíte dialogové okno

V následně zobrazeném dialogovém okně vidíte seznam všech souboru ve složce (zde jsou vidět jen textové soubory). V další článku ukáži jak zpracovat i složky (adresáře), které obsahují i jiné typy souboru, popřípadě, textové soubory, které mají různí velikosti přípon (TXT vs txt).

V dialogovém okně máte na výběr z několika tlačítek:

• Kombinovat
• Načíst
• Transformovat data

V dalších kapitolách si jednotlivé možnosti postupně rozebereme, přeci jen každá umí něco jiného a z mého pohledu nejlepší je ta poslední Transformovat data, ale pro jednodužší zpracování může vyhovovat první a pro nějaké speciální výstupy i to prostřední.

Kombinovat

Klikem na ikonu Kombinovat obdržíte dialogové okno Kombinovat soubory, kde můžete (spíše musíte) vyplnit několik informací.

• Ukázkový soubor - který chcete zpracovat jako ukázkový, v Excel se zobrazuje pouze první nalezený soubor (jsou-li všechny soubory stejné)
• Původ souboru - aneb kódování, buď víte a vyberene, nebo překlikaváte a koukáte na ukázku, zda je správně zobrazena čeština, potažmo speciální znaky.
• Oddělovač - v češtině nejčastěji středník (bodkočárka), někdy může jít o čárku, mezeru, nebo počet znaků (záleží na vašich datech)
• Zjišťování datového typu Téměř vždy mi stačilo založené na prvních 200 řádcích, pokud ale mermomocí chcete, můžete vybrat na celé datové sadě a nebo nezjišťovat datové typy.

Klikem na OK se otevře samostatné okno Editor Power Query

Neboli data jsou načtena pěkně ze všech souboru, můžete vidět v prvním sloupci Zdroj.Název jak se který soubor jmenuje a následují příslušná data (pokud vám první sloupec vadí zle jej odstranit (přes pravé tlačítko, případně více jak na práce se sloupci v odkazovaném článku na konci článku).

Pokud jste s výsledkem načtení spokojení můžete přejít na kapitolu Zavřít a načíst, nebo se podávat co dělají další tlačítka.

Načíst

Klikem na ikonu Načíst se přímo do Excel načtou informace o jednotlivých souborech ve složce (adresáři). Na první pohled vypadá tabulka divně, ale v praxi se často i tento výpis hodí. Na druhou stranu je nejrychlejší ;)

Mohdy máte hotovo. Pokud ne můžete se přečíst i následující způsob.

Transformovat

Za mě asi nejlepší možnost (mnohdy složka obsahuje i další soubory a nechcete je někam kopírovat, přeci jen jde o velké soubory miliony řádků, což kopírovat desitemegové soubory nějaký čas zabere), která je asi nejsofistikovanější.

Proto si ukážeme i tuto možnost. Spíše dvě jak na to:

• První možnost - přímé načtení
• Druhá možnost - úprava tabulky před načtením

První možnost - přímé načtení

V prvním kroku můžete rovnou ve sloupci Content kliknout na ty dvě šipky napravo od názvu sloupce.

Obdržíte dialogové okno, které znáte z Ikony Kombinovat

Neboli můžete vyplnit:

• Ukázkový soubor - který chcete zpracovat jako ukázkový
• Původ souboru - aneb kodování
• Oddělovač - v češtině nejčastěji středník (bodkočárka)
• Zjišťování datového typu

V předchozí kapitole Kombinovat jsou položky popsány podrobněji.

A kliknout na OK

Na první pohled stejný výsledek jako při kliku na ikonu Načíst, ale větší význam má pokud Power Query Editor použijete pro selekci jen těch souborů, které chcete ze složky načítat. Toto si ukážeme v dalším článku, jen jsem potřeboval základ, ať se mám kde odkazovat a navíc zde tato informace i logicky patří.

Druhá možnost - úprava tabulky před načtením

Nebo-li je potřeba upravit sloupce (zde si v dalším článku ukážeme jak upravit při různých souborech ve složce, kterou chcete načítat). Nejprve odmažte ostatní sloupce (Tip pro pokročilé, poté co sloupec zdroj název rozdělíte a máte odfiltrovány jen ty soubory, které chcete zpracovat - viz článek Jak na práci se sloupci v Power Query).

Po kliku na "dvojšipku" (ikona vpravo) u sloupce Content opět obdržíte dialogové okno Kombinovat soubory:

Výsledkem je opět spojení dat, jen okno neobsahuje sloupec se jmény souboru (vím, že lze provést přes ikonu Kombinovat a sloupec odmazat zde, jen to není tak efektivní pro více souboru v jedné složce/adresáři o kterém se zmíním v další kapitole, ale ať je pohromadě, tak tuto možnost zmiňuji i v tomto článku, protože zde i logicky patří).

Máte hotovo a můžeme se vrhnout na Zavřít a načíst, pokud nepotřebujete načtená data nějak upravovat využitím Power Query.

Zavřít a načíst

Jaké možnosti jsou po načtení dat do Excel? Ono to je na první pohled jasné, ale na ten druhý zjistíte, že těch možností je více. Data můžete přímo načít do Excel, nebo vytvořit propojení (tím pádem můžete načíst i několik milionu dat a nejste limitování miliónem řádku, které ma Excel - připoujete se na datový zdroj). Nebo-li na data se budete připojovat (vhodné/nutné pro velké datové zdroje, více než milión řádků).

Klikem na Načíst a zavřít > Zavřít a načíst do.. obdržíte dialogové okno Importovat data, jinak přes Zavřít a načíst se jen data načtou do Excel.

Ve způsobu zobrazení dat v sešitu si můžete zvolit:

• Tabulka - data se načtou přímo do Excel *- co udělá přímo ikona Načíst a zavřít (takže si můžete zjednodušit)
• Sestava kontingenční tabulky
• Kontingenční graf
• Pouze vytvořit připojení - data se nenačítájí přímo do Excel, pouze se vytvoří propojení a Excel je zpracuje. Tímto způspobem můžete zpracovávat i více než milion (omezení počtu řádků) dat (až cca 2 miliardy řádků).

Pozor nezapomenout (na to se často zapomíná) zaškrtnout vložit data do datového modelu.

Načteno

Pokud máte načteno může se zobrazit více méně dvěma způsoby:

Načtete data přímo do Excel nebo provedete připojení se k datům.

Jen propojení

V Excel na první pohled nevidíte data, ale jsou připojena. Pozor jen zda bylo správné nastavení v dialogovém okně Importovat data (nebo-li byl spolu s "zakliknutím" Pouze vytvořit připojení a zatrhnuto i Přidat tahle data do datového modelu).

Pokud v dotazech nevidíte počet (jako na snímku), nebylo zatrhnuto přidat data do datového modelu. :( a nebude fungovat jednoduše aktualizace.

Přímo načíst do tabulky

Vidíte data přímo data v Excel - pokud jste zatrhli jen Načíst data, nebo zvolili volbu kombinovat.

Aktualizace

Přibydou-li data v souboru, případně se přibude nový soubor ve složce stačí klik na ikonku pro aktualizaci.

Pozor. Pokud nezakliknete v připojení "datový model", aktualizace vám nebude fungovat.

Ukázka, že může být načtenov cíce než 1.000.000 řádků, přes 1,7 miliónu. ;)

Pozor někdy se nějakou dobu přepočítává. Pokud se načtení nepodaří zkuste kliknout na ikonu ještě jednou. Pokud ani tak se aktualizace nepodaří, doporučuji vygooglit příslušnou chybovou hlášku.

Související články

Pokud vás zaujalo, možná se budete chtít podívat na související články s Práci v Power Query:

• Jak na Power Query - seriál
• Jak na práci se sloupci v Power Query
• Jak na práci s řádky v Power Query

S power Query se pojí i Power Pivot a Power View proto také pár odkazů:

• Power Pivot úvodem
• Vložit data do Power Pivot

Ke stažení

• Soubor Query-2017.txt data pro testy ke stažení zdarma.
• Soubor Query-2018.txt data pro testy ke stažení zdarma.
• Soubor Query-2019.txt data pro testy ke stažení zdarma.

Další ukázky v přípravě. Urychlit publikaci můžete podporou na: Patreonu.

Závěrem

Napadá vás nějaká komplikace se kterou jste se setkali? Máte nějak tip, můžete zmínit v komentářích.

Článek byl aktualizován: 19.09.2020 11:02

Pavel Lasák - autor webu

Microsoft Office (Word, Excel, Google tabulky, PowerPoint) se věnuji od roku 2000 (od dubna roku 2004 na této doméně) - V roce 2017 jsem od Microsoft získal prestižní ocenění MVP (zatím 8x za sebou). Své vědomosti a zkušenosti dávám k dispozici i on-line ve videích pro SEDUO. Ve firmách školím a konzultuji, učím na MUNI. Tento web již tvořím přes 20 let (o Excel píší přes 25). Zdarma je zde přes 1.500 návodu, tipů a triků, včetně přes 350 různých šablon, sešitů a přes 70 taháků v pdf.