Dnes som dostal zaujímavý dotaz – ako načítať údaje zo ZIP súboru, čiže zo zazipovaného súboru. V tomto konkrétnom prípade išlo o to, ako načítať údaje z excelovského súboru, zazipovaného do klasického ZIP súboru, a ako ich načítať pomocou Power Query do Excelu alebo Power BI. A to v prípadoch, keď je súbor uložený na lokálnom disku, alebo na zdieľanom adresári, alebo dokonca na webe – napr. na SharePointe alebo na OneDrive. Nie je to triviálna úloha, ale keď už viete, ako na to, tak je to celkom ľahké.

Málokto vie, že Power Query vie čítať aj ZIP súbory. Nevie to priamo, ale vďaka jednoduchosti tohto formátu to ide spraviť relatívne jednoducho pomocou naskladania funkcií v Power Query. Takže ide len o to, aby sme vykoumali, ako načítať obsah ZIP súboru, dekomprimovali ho, a následne z neho vybrali len tie súbory, ktoré nás zaujímajú. Prípadne aj všetky.

Funkcia pre načítanie súboru zo ZIP súboru

Celé to ide spraviť jednou elegantnou funkciou v Power Query, s týmto zdrojovým kódom:

let 
   nahodnyAliasFunkcie = (zazipovanySuborCesta as text) =>
let
    binaryZip = Web.Contents(zazipovanySuborCesta),
    // shorthand
    UInt32 = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32,ByteOrder.LittleEndian),
    UInt16 = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16,ByteOrder.LittleEndian),
    // ZIP file header fixed size structure
    Header = BinaryFormat.Record([
	    	MiscHeader 	 	= BinaryFormat.Binary(14),
                CompressedSize   	= UInt32,
                UncompressedSize 	= UInt32,
                FileNameLen      	= UInt16,
                ExtraFieldLen 	 	= UInt16]),
    // ZIP file header dynamic size structure
    FileData = (h)=> BinaryFormat.Record([
                FileName         	= BinaryFormat.Text(h[FileNameLen]),
                ExtraField       	= BinaryFormat.Text(h[ExtraFieldLen]),
                UncompressedData 	= BinaryFormat.Transform(
	    		BinaryFormat.Binary(h[CompressedSize]),
	    			(x) =>  try 
	    				Binary.Buffer(Binary.Decompress(x, Compression.Deflate)) 
	    				otherwise null)]),
    // Parsing the binary in search for PKZIP header signature
    ZipIterator = BinaryFormat.Choice(UInt32, (signature) => if signature <> 0x04034B50 
                                            then BinaryFormat.Record([FileName=null])
                                            else BinaryFormat.Choice(Header,(z)=>FileData(z))),
    ZipFormat = BinaryFormat.List(ZipIterator),
    out = ZipFormat(binaryZip)
in
    out{0}[UncompressedData]
in nahodnyAliasFunkcie

A ako to funguje?

V prvom rade je treba sa poďakovať autorovi väčšej časti tohto kódu v jazyku M, ktorý si s tým dal prácu už pred nejakými 2 rokmi. Tento kód je upravenou verziou jeho M skriptu.

Tento skript vytvorí funkciu v jazyku M, s parametrom „zazipovanySuborCesta“, do ktorej stačí zadať cestu k zazipovanému súboru, a Power Query tento súbor rozbalí a vráti z neho prvý súbor ako binárne dáta. Tie následne spracujeme klasickými postupmi v Power Query.

Ako to rozchodiť

V prvom rade potrebujeme otvoriť Power Query, a pridať doňho prázdny dotaz. Do tohto dotazu potom potrebujeme vložiť náš skript, napr. tak ako som o tom písal už v tomto článku. A potom tú funkciu použiť.

Dajte si teda cez Power Query načítať nové údaje, cez dátový zdroj s názvom „Prázdny dotaz“. V každej verzii Power Query sa to robí pre istotu inak. Napr. v V Exceli 2016 sa to robí tak, že kliknete v hlavnom menu na záložku Údaje, a tam na Nový dotaz => Z iných zdrojov => Prázdny dotaz:

A v Power BI Desktope to spravíte tak, že kliknete v hlavnom menu na záložku Domov, a potom „Získať údaje“ => „Prázdny dotaz“:

Následne sa otvorí okno Power Query. V ňom klikneme na tlačítko „Rozšírený editor“:

Otvorí sa rozšírený editor M skriptu. Tam vymažte celý ten text/skript, ktorý tam je, a namiesto neho tam prilepte skript zo začiatku tohto článku. Potom kliknite na tlačítko Hotovo:

Následne sa zadávaný dotaz zmení na funkciu Power Query. To uvidíte v paneli naľavo, indikované pomocou ikonky „fx“:

Týmto sme zadefinovali funkciu Power Query s názvom „Dotaz1“. A tú je dobré si premenovať na nejaký iný názov, napr. „NacitajPrvySuborZoZIPu“. To spravíte dvojklikom na ten názov, a prepísaním názvu „Dotaz1“ na tento nový názov:

Teraz túto funkciu už iba potrebujeme vyvolať. To spravíme zadaním parametra „zazipovanySuborCesta“, kde zadáme cestu k súboru, a potom dáme vyvolať tú funkciu. Tam zadáme názov súboru takto:

1. ak ide lokálny súbor, tak tam zadáme cestu vo formáte file:///cesta_k_suboru/subor.zip. Čiže ak je súbor umiestnený na lokálnom disku, a cesta k nemu je C:\temp\subor.zip, tak tam zadáme file:///c:/temp/subor.zip. Lomítka v ceste je potrebné zadávať ako normálne lomítka,
2. ak ide o súbor na zdieľanom adresári, tak tam zadáme cestu vo formáte \\server\adresar\subor.zip,
3. ak ide o súbor na webe, tak tam zadáme cestu vo formáte https://www.powerpivot.sk/subory/subor.zip. Čiže priamo URL cestu k súboru ZIP.

V mojom prípade tam zadám cestu k lokálnemu súboru, a dám vyvolať funkciu tlačítkom „Vyvolať“:

Po vyvolaní funkcie nám naľavo pribudne ďalší dotaz, ktorý sa volá „Vyvolaná funkcia“, a po kliknutí naň zobrazí binárny súbor:

To je prvý súbor z toho ZIP súboru, a ja viem, že v mojom prípade je to excelovský súbor. Ten teda otvorím tak, že na tú ikonku kliknem pravým tlačítkom myši, a tam vyberiem položku Excel:

To nám načíta z excelovského súboru zoznam tabuliek, alebo vecí, ktoré sa vedia tváriť ako tabuľky – čiže zoznam hárkov, pomenovaných oblastí a tabuliek. Tam klikneme na žltý odkaz „Table“ pre ten riadok, ktorý reprezentuje našu želanú tabuľku, ktorú sme chceli z toho excelovského súboru načítať:

A prípadne si potom upravíme tú načítanú tabuľku:

Ako napr. v mojom prípade, kde dám použiť prvý riadok dát ako hlavičky stĺpcov:

A máme načítanú želanú tabuľku z excelovského súboru zazipovaného do ZIPu 🙂 Aj naša svokra by bola prekvapená, ako som to zvládla.

A čo ak chceme načítať konkrétny súbor?

Ak chceme načítať iný ako prvú súbor, tak máme v podstate 2 možnosti.

Ak chcete načítať N-tý súbor, tak v jednom z posledných riadkov, konkrétne v tomto:

out{0}[UncompressedData]

…nahraďte tú nulu v zložených zátvorkách indexom požadovaného súboru. Indexy začínajú na nule, takže pre 1. súbor tam necháte 0, pre 2. súbor tam dáte 1, pre 3. súbor tam dáte 2, atď..

Ak by ste ale chceli vybrať z toho ZIP súboru konkrétny súbor s konkrétnym menom, tak namiesto toho použite túto funkciu s týmto zdrojovým kódom:

let 
   nahodnyAliasFunkcie = (zazipovanySubor as text, hladanySubor as text) =>
let
    binaryZip = Web.Contents(zazipovanySubor),
    // shorthand
    UInt32 = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32,ByteOrder.LittleEndian),
    UInt16 = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16,ByteOrder.LittleEndian),
    // ZIP file header fixed size structure
    Header = BinaryFormat.Record([
	    	MiscHeader 	 	= BinaryFormat.Binary(14),
                CompressedSize   	= UInt32,
                UncompressedSize 	= UInt32,
                FileNameLen      	= UInt16,
                ExtraFieldLen 	 	= UInt16]),
    // ZIP file header dynamic size structure
    FileData = (h)=> BinaryFormat.Record([
                FileName         	= BinaryFormat.Text(h[FileNameLen]),
                ExtraField       	= BinaryFormat.Text(h[ExtraFieldLen]),
                UncompressedData 	= BinaryFormat.Transform(
	    		BinaryFormat.Binary(h[CompressedSize]),
	    			(x) =>  try 
	    				Binary.Buffer(Binary.Decompress(x, Compression.Deflate)) 
	    				otherwise null)]),
    // Parsing the binary in search for PKZIP header signature
    ZipIterator = BinaryFormat.Choice(UInt32, (signature) => if signature <> 0x04034B50 
                                            then BinaryFormat.Record([FileName=null])
                                            else BinaryFormat.Choice(Header,(z)=>FileData(z))),
    ZipFormat = BinaryFormat.List(ZipIterator),
    out = List.Select(ZipFormat(binaryZip), each _[FileName]=hladanySubor)
in
    out{0}[UncompressedData]
in nahodnyAliasFunkcie

Je to skoro rovnaká funkcia ako tá pred ňou. Líši sa iba dodatočným parametrom „hladanySubor“, kde zadáte názov súboru z vnútra toto ZIPu, ktorý chcete vrátiť. Používa sa to rovnako ako celý predchádzajúci skript, len pri vyvolaní funkcie zadáte oba parametre:

Zvyšok je už potom identický s postupom vyššie.

A čo ak chcem načítať všetky súbory zo ZIP súboru?

To si už nechajte za domácu úlohu, alebo ak sa moje panstvo uráči, tak sa tomu bude venovať v ďalšom článku 🙂

Takže takto viete načítať údaje z excelovského súboru, zazipovaného do klasického ZIP súboru, cez Power Query do Excelu alebo Power BI. Pritom v tom zazipovanom súbore nemusia byť iba excelovské súbory, ale akékoľvek súbory, s ktorými vie pracovať Power Query. Čiže aj CSV, XML, JSON, HTML či PDF. A opäť sa potvrdilo, že ak viete ako na to, tak je to záležitosť pár sekúnd 🙂

Dajte vedieť dole do komentárov, či Vám to pomohlo. Na základe toho sa rozhodnem, či ovládnem svet už hneď dnes alebo až zajtra 😀