Import údajov z priečinka s viacerými súbormi (Power Query)

Ak chcete skombinovať viacero súborov s rovnakou schémou uloženou v jednom priečinku do jednej tabuľky, použite Power Query. Napríklad každý mesiac chcete skombinovať rozpočtové zošity z viacerých oddelení, kde sú stĺpce rovnaké, ale počet riadkov a hodnôt sa líši v každom zošite. Po nastavení môžete použiť ďalšie transformácie rovnako ako pri každom jednom importovanom zdroji údajov a potom obnoviť údaje tak, aby sa zobrazili výsledky pre každý mesiac.

Schematický prehľad kombinovania súborov priečinkov

Poznámka: V tejto téme sa dozviete, ako kombinovať súbory z priečinka. Môžete tiež kombinovať súbory uložené v SharePointe, ukladacom priestore objektu BLOB platformy Azure a Azure Data Lake Storage. Proces je podobný.

Novšie verzie Office 2013

Majte to jednoduché:

Uistite sa, že všetky súbory, ktoré chcete skombinovať, sa nachádzajú vo vyhradenom priečinku bez nadbytočných súborov. V opačnom prípade sa všetky súbory v priečinku a všetky podpriečinky, ktoré vyberiete, zahrnú do údajov, ktoré sa majú kombinovať.
Každý súbor by mal mať rovnakú schému s konzistentnými hlavičkami stĺpcov, typmi údajov a počtom stĺpcov. Stĺpce nemusia byť v rovnakom poradí ako zhoda podľa názvov stĺpcov.
Ak je to možné, vyhnite sa nesúvisiacim údajovým objektom pre zdroje údajov, ktoré môžu mať viac ako jeden údajový objekt, napríklad súbor JSON, excelový zošit alebo accessovú databázu.

Každý z týchto súborov má jednoduchý vzor, iba jednu tabuľku údajov v každom súbore.

Vyberte položku Údaje > Získať údaje > zo súboru > z priečinka. Zobrazí sa dialógové okno Prehľadávať .
Vyhľadajte priečinok obsahujúci súbory, ktoré chcete skombinovať.
Zoznam súborov v priečinku sa zobrazí v <ceste k priečinku> dialógovom okne. Overte, či sú v zozname uvedené všetky požadované súbory.
Vyberte jeden z príkazov v dolnej časti dialógového okna, napríklad Kombinovať > Kombinovať & Načítať. V časti O všetkých týchto príkazoch sú popísané ďalšie príkazy.
Ak vyberiete ľubovoľný príkaz Kombinovať , zobrazí sa dialógové okno Kombinovanie súborov . Ak chcete zmeniť nastavenie súboru, vyberte jednotlivé súbory z poľa Vzorový súbor , nastavte požadované položky Pôvod súboru, Oddeľovač a Zisťovanie typu údajov . Môžete tiež začiarknuť alebo zrušiť začiarknutie políčka Vynechať súbory s chybami v dolnej časti dialógového okna.
Vyberte tlačidlo OK.

Výsledok

Power Query automaticky vytvára dotazy na zlúčenie údajov z každého súboru do hárka. Kroky a stĺpce dotazu závisia od toho, ktorý príkaz vyberiete. Ďalšie informácie nájdete v časti Informácie o všetkých týchto dotazoch.

Vyberte položku Údaje > Získať údaje > zo súboru > z priečinka. Zobrazí sa dialógové okno Prehľadávať.
Vyhľadajte priečinok obsahujúci súbory, ktoré chcete skombinovať.
Zoznam súborov v priečinku sa zobrazí v <ceste k priečinku> dialógovom okne. Overte, či sú v zozname uvedené všetky požadované súbory.
Vyberte jeden z príkazov v dolnej časti dialógového okna, napríklad Kombinovať > Kombinovať & Transformácia. V časti O všetkých týchto príkazoch sú popísané ďalšie príkazy.

Zobrazí sa Editor Power Query.
Stĺpec Hodnota je štruktúrovaný stĺpec Zoznam . Vyberte ikonu Rozbaliť a potom vyberte položku Rozbaliť na nové riadky.
Stĺpec Hodnota je teraz štruktúrovaným stĺpcom Záznam . Vyberte ikonu Rozbaliť . Zobrazí sa rozbaľovacie dialógové okno.
Ponechajte všetky stĺpce vybraté. Možno budete chcieť zrušiť začiarknutie políčka Použiť ako predponu pôvodný názov stĺpca . Vyberte tlačidlo OK.
Vyberte všetky stĺpce, ktoré obsahujú hodnoty údajov. Vyberte položku Domov, šípku vedľa položky Odstrániť stĺpce a potom vyberte položku Odstrániť ostatné stĺpce.
Vyberte položku Domov > Zavrieť & načítať.

Výsledok

Každý z týchto zdrojov údajov môže mať na import viac ako jeden objekt. Excelový zošit môže obsahovať viacero hárkov, excelových tabuliek alebo pomenovaných rozsahov. Databáza Accessu môže obsahovať viacero tabuliek a dotazov.

Vyberte položku Údaje > Získať údaje > zo súboru > z priečinka. Zobrazí sa dialógové okno Prehľadávať.
Vyhľadajte priečinok obsahujúci súbory, ktoré chcete skombinovať.
Zoznam súborov v priečinku sa zobrazí v <ceste k priečinku> dialógovom okne. Overte, či sú v zozname uvedené všetky požadované súbory.
Vyberte jeden z príkazov v dolnej časti dialógového okna, napríklad Kombinovať > Kombinovať & Načítať. V časti O všetkých týchto príkazoch sú popísané ďalšie príkazy.
V dialógovom okne Kombinovanie súborov :
- V poli Vzorový súbor vyberte súbor, ktorý sa má použiť ako vzorové údaje používané na vytvorenie dotazov. Objekt nemôžete vybrať alebo vybrať len jeden objekt. Nemôžete však vybrať viac ako jeden.
- Ak máte veľa objektov, pomocou vyhľadávacieho poľa vyhľadajte objekt alebo možnosti zobrazenia spolu s tlačidlom Obnoviť na filtrovanie zoznamu.
- Začiarknite alebo zrušte začiarknutie políčka Vynechať súbory s chybami v dolnej časti dialógového okna.
Vyberte tlačidlo OK.

Výsledok

Power Query automaticky vytvorí dotaz na zlúčenie údajov z každého súboru do hárka. Kroky a stĺpce dotazu závisia od toho, ktorý príkaz vyberiete. Ďalšie informácie nájdete v časti Informácie o všetkých týchto dotazoch.

Na väčšiu flexibilitu môžete explicitne kombinovať súbory v Editor Power Query pomocou príkazu Kombinovať súbory. Povedzme, že zdrojový priečinok obsahuje zmes typov súborov a podpriečinkov a chcete zacieliť konkrétne súbory s rovnakým typom súboru a schémou, ale nie na iné. Môže to zvýšiť výkon a zjednodušiť transformácie.

Vyberte položku Údaje > Získať údaje > zo súboru > z priečinka. Zobrazí sa dialógové okno Prehľadávať .
Vyhľadajte priečinok obsahujúci súbory, ktoré chcete skombinovať, a potom vyberte položku Otvoriť.
V dialógovom okne <Priečinok> sa zobrazí zoznam všetkých súborov v priečinku a podpriečinkoch. Overte, či sú v zozname uvedené všetky požadované súbory.
V dolnej časti vyberte položku Transformovať údaje . Otvorí sa Editor Power Query a zobrazia sa všetky súbory v priečinku a všetky podpriečinky.
Ak chcete vybrať požadované súbory, vyfiltrujte stĺpce, ako je napríklad prípona alebo cesta k priečinku.
Ak chcete skombinovať súbory do jednej tabuľky, vyberte stĺpec Obsah , ktorý obsahuje jednotlivé binárne súbory (zvyčajne prvý stĺpec), a potom vyberte položku Domov > Kombinovať súbory. Zobrazí sa dialógové okno Kombinovanie súborov .
Power Query analyzuje vzorový súbor, predvolene prvý súbor v zozname, aby použil správnu spojnicu a identifikoval zodpovedajúce stĺpce.

Ak chcete pre vzorový súbor použiť iný súbor, vyberte ho z rozbaľovacieho zoznamu Vzorový súbor .
Voliteľne môžete v dolnej časti vybrať položku Vynechať súbory s chybamia vylúčiť tieto súbory z výsledku.
Vyberte tlačidlo OK.

Výsledok

Power Query automaticky vytvorí dotazy na zlúčenie údajov z každého súboru do hárka. Kroky a stĺpce dotazu závisia od toho, ktorý príkaz vyberiete. Ďalšie informácie nájdete v časti Informácie o všetkých týchto dotazoch.

Môžete vybrať niekoľko príkazov a každý z nich má iný účel.

Kombinovanie a transformácia údajov Ak chcete skombinovať všetky súbory s dotazom a potom spustiť Editor Power Query, vyberte položku Kombinovať > Kombinovať a transformovať údaje.
Kombinovanie a načítanie Ak chcete zobraziť dialógové okno Vzorový súbor, vytvorte dotaz a potom ho načítajte do hárka, vyberte položku Kombinovať > Kombinovať a načítať.
Kombinovať a načítať do Ak chcete zobraziť dialógové okno Vzorový súbor, vytvorte dotaz a potom zobrazte dialógové okno Import , vyberte položku Kombinovať > Kombinovať a načítať do.
Zaťaženie Ak chcete vytvoriť dotaz s jedným krokom a potom ho načítať do hárka, vyberte položku Načítať > Načítať.
Načítať do Ak chcete vytvoriť dotaz s jedným krokom a potom zobraziť dialógové okno Import , vyberte položku Načítať > Načítať do.
Transformovať údajeAk chcete vytvoriť dotaz s jedným krokom a potom spustiť Editor Power Query, vyberte položku Transformovať údaje.

Ak však skombinujete súbory, na table Dotazy v skupine Pomocné dotazy sa vytvorí niekoľko podporných dotazov.

Zoznam dotazov vytvorených na table Dotazy

Power Query na základe vzorového dotazu vytvorí dotaz Ukážkový súbor.
Dotaz funkcie Transformovať súbor používa dotaz Parameter1 na určenie každého súboru (alebo binárneho súboru) ako vstupu do dotazu Vzorový súbor. Tento dotaz tiež vytvorí stĺpec Obsah obsahujúci obsah súboru a automaticky rozšíri štruktúrovaný stĺpec Záznam a pridá údaje stĺpca do výsledkov. Dotazy Transformovať súbor a Vzorový súbor sú prepojené, takže zmeny dotazu Vzorový súbor sa prejavia v dotaze Transformovať súbor.
Dotaz obsahujúci konečné výsledky sa nachádza v skupine Iné dotazy. Predvolene je pomenovaná podľa priečinka, z ktorého ste importovali súbory.

Ak chcete vykonať ďalšie skúmanie, kliknite pravým tlačidlom myši na každý dotaz a vyberte položku Upraviť , aby ste preskúmali jednotlivé kroky dotazu a zistili, ako dotazy fungujú v zhode.

Skôr ako začnete súbory kombinovať, uistite sa, že sa všetky nachádzajú v jednom priečinku.

Poznámka: Podporované sú súbory formátu Excel, Text, CSV, JSON, XML a Access.

Kliknite na kartu Power Query a potom na položky Zo súboru > Z priečinka.
Kliknutím na tlačidlo Prehľadávať vyhľadajte priečinok.

Zobrazí sa zoznam súborov v priečinku. Kliknutím na položku Upraviť otvorte Editor dotazov. V tomto prípade sme načítali 4 excelové zošity.
Overte, či sú v zozname všetky požadované súbory, a kliknite na tlačidlo Kombinovať binárne údaje v stĺpci Obsah.
Prebehne analýza a určenie správneho formátu každého súboru, ako je napríklad text, Excel alebo JSON. V tomto prípade sa zobrazil zoznam hárkov z prvého excelového zošita. Vyberte hárok, ktorý chcete použiť, a kliknite na tlačidlo OK.
Transformačný proces kombinovania binárnych údajov automaticky vytvorí dotaz na zlúčenie údajov z každého súboru a zobrazenie ukážky. Ak ste s výsledkom spokojní, kliknite na položku Zavrieť a načítať.

Proces kombinácie binárnych údajov zlúči vybraté hárky do jedného hárku.
Ak sa pôvodné údajové súbory zmenia, importované údaje môžete vždy obnoviť. Kliknite na ľubovoľné miesto v rozsahu údajov a potom prejdite na položku Nástroje dotazov > obnoviť. Okrem toho môžete jednoducho použiť ďalšie kroky transformácie alebo extrakcie úpravou automaticky vytvoreného vzorového dotazu bez toho, aby ste sa museli starať o úpravu alebo vytvorenie ďalších krokov dotazu na funkciu. všetky zmeny vzorového dotazu sa automaticky vygenerujú v prepojenom dotaze funkcie.

Známe problémy

Pri načítaní procesu kombinovania binárnych údajov do Excelu sa môže zobraziť toto chybové hlásenie:

Chybové hlásenie Kombinovania binárnych súborov. Toto je známa chyba, ktorá sa rieši.

Ak ho odmietnete kliknutím na tlačidlo OK, kombinovanie binárnych údajov sa dokončí a vaše údaje sa správne načítajú aj napriek chybe. Na odstránení chyby pracujeme.