PQ - UnZip an use Content in Power Query

[sandy666]

UnZip an use Content in Power Query

it works for local files but not from web/url (at least I failed)


(ZIPFile) =>
let
Header = BinaryFormat.Record([
MiscHeader = BinaryFormat.Binary(14),
BinarySize = BinaryFormat.ByteOrder(BinaryFormat.UnsignedIntege r32, ByteOrder.LittleEndian),
FileSize = BinaryFormat.ByteOrder(BinaryFormat.UnsignedIntege r32, ByteOrder.LittleEndian),
FileNameLen= BinaryFormat.ByteOrder(BinaryFormat.UnsignedIntege r16, ByteOrder.LittleEndian),
ExtrasLen = BinaryFormat.ByteOrder(BinaryFormat.UnsignedIntege r16, ByteOrder.LittleEndian)
]),

HeaderChoice = BinaryFormat.Choice(
BinaryFormat.ByteOrder(BinaryFormat.UnsignedIntege r32, ByteOrder.LittleEndian),
each if _ <> 67324752 // not the IsValid number? then return a dummy formatter
then BinaryFormat.Record()
else BinaryFormat.Choice(
BinaryFormat.Binary(26), // Header payload - 14+4+4+2+2
each BinaryFormat.Record([
IsValid = true,
Filename = BinaryFormat.Text(Header(_)[FileNameLen]),
Extras = BinaryFormat.Text(Header(_)[ExtrasLen]),
Content = BinaryFormat.Transform(
BinaryFormat.Binary(Header(_)[BinarySize]),
(x) => try Binary.Buffer(Binary.Decompress(x, Compression.Deflate)) otherwise null
)
]),
type binary // enable streaming
)
),

ZipFormat = BinaryFormat.List(HeaderChoice, each _ = true),

Entries = List.Transform(
List.RemoveLastN( ZipFormat(ZIPFile), 1),
(e) => [FileName = e[Filename], Content = e[Content] ]
)
in
Table.FromRecords(Entries)

create function from code above, rename to UnzipContents

then create Query with code:


let
Source = File.Contents("[I]full path to your zip file"),
Files = UnzipContents(Source)
in
Files

use gear on the right side of Source step to choose a zip file


https://i.postimg.cc/xjyn2vVs/unzip.png
2997


[I]founded here:Reading zip files in Power Query (https://sql10.blogspot.com/2016/06/reading-zip-files-in-powerquery-m.html)