Přeskočit navigaci | Přeskočit na novinky
     

Jste zde: Úvodní stránka » excel » Query-editor » Jak-nacitat-data-ze-slozky-Power-Query-Excel
Microsoft Excel logo

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

Videokurzy Excel

Ve spolupráci se SEDUO jsem vytvořil několik videokurzů:

Jak efektivně načítat data ze složky (adresáře) do Excel využitím Power Query. Nebo-li mám desítky měsíčních reportů v *.csv (*.txt) a potřebuji je dostat efektivně a rychle do Excel pro další zpracování například využitím Power Pivot, kontingenčních tabulek atd. Včetně možností mít víc jak 1.000.000 řádků!

Ú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:

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ý).

Power Query - Excel

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ů).

Power Query - Excel

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.

Power Query - Excel

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

Power Query - Excel

Po kliku OK obdržíte dialogové okno

Power Query - Excel

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.
Power Query - Excel

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

Kombinovat načtená data - Power Query - Excel

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ší ;)

Načíst ikona v Power Query - Excel při načítání z více složek

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. Kombinovat dotazy - Power Query - Excel

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.

Power Query - Excel

A kliknout na OK

Power Query - Excel

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).

Power Query - Excel

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

Power Query - Excel

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ří).

Power Query - Excel

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.

Power Query - 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).

Power Query - Excel

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.

Power Query - Excel

Aktualizace

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

Power Query - Excel

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. ;)

Power Query - Excel

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:

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

Microsoft Excel VBA - stahuj logo

Ke stažení

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: 28.06.2019 09:51

Odměna

Ušetřil vám tento web čas, peníze? Pomohl vyřešit problém? Jste ochotni poskytnout symbolickou odměnu na další rozvoj? Vybrte si formu odměny, která vám vyhovuje.


Pavel Lasák - autor webu

Pavel Lasák

Microsoft Office (Word, Excel, Google tabulky, PowerPoint) se věnuji od roku 2000 (od 2004 na této doméně) - V roce 2017 jsem od Microsoft získal prestižní ocenění MVP (zatím jsem jediný z ČR v kategorií Excel). 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 15 let. Zdarma je zde přes 1.000 návodu, tipů a triků, včetně přes 250 různých šablon, sešitů.

   Pavel Lasák LinkedIn Profil    Pavel Lasák Google+ Profil    Pavel Lasák facebook Profil    Pavel Lasák twitter Profil


Komentáře zatím nejsou

Můžete být prvními co zanechají smysluplný komentář.






Excel


Sdílejte

Pomohl Vám návod?
Sdílejte na Facebooku, G+
LinkedIn...

Nové články


Reklama


TOPlist Licence Creative Commons webarchiv rss XML

Stránky o MS Office (Excel) produktu společnosti Microsoft. Neslouží jako technická podpora.
| Email na autora: pavel.lasak@gmail.com | Copyright © : Pavel Lasák 2004 - 2019 | 591

Tento web zatím neprošel jazykovou korekturou. Beta verze redakčního systému.