Forum Discussion
Failure to extract file from ZIP
I know there has been several attempts to address this previously, but so far without resolution (as far as I am aware). So I am providing more detailed information into the zip file properties, in ...
- Have you tried the implementation I posted here: https://community.powerbi.com/t5/Power-Query/How-to-connect-Azure-DevOps-REST-API-in-to-power-bi/m-p/895318/highlight/true#M30599
Here is a version that should work with all of your ZIP files. It ignores the local file entries and grabs the data from the central directory instead. Please test it out.
// expects full path to the ZIP file, only extracts the first data file after getting its size from the central directory // https://en.wikipedia.org/wiki/Zip_(file_format)#Structure (ZIPFile) => let //read the entire ZIP file into memory - we'll use it often so this is worth it Source = Binary.Buffer(File.Contents(ZIPFile)), // get the full size of the ZIP file Size = Binary.Length(Source), //Find the start of the central directory at the sixth to last byte Directory = BinaryFormat.Record([ MiscHeader=BinaryFormat.Binary(Size-6), Start=BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian) ]) , Start = Directory(Source)[Start], //find the first entry in the directory and get the compressed file size FirstDirectoryEntry = BinaryFormat.Record([ MiscHeader=BinaryFormat.Binary(Start+20), FileSize=BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian), UnCompressedFileSize=BinaryFormat.Binary(4), FileNameLen=BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16, ByteOrder.LittleEndian), ExtrasLen=BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16, ByteOrder.LittleEndian) ]) , //figure ou where the raw data starts Offset = 30+FirstDirectoryEntry(Source)[FileNameLen]+FirstDirectoryEntry(Source)[ExtrasLen], Compressed = FirstDirectoryEntry(Source)[FileSize]+1, //get the raw data of the compressed file Raw = BinaryFormat.Record([ Header=BinaryFormat.Binary(Offset), Data=BinaryFormat.Binary(Compressed) ]) // unzip it in Binary.Decompress(Raw(Source)[Data], Compression.Deflate)Name it unzip and and then call it like this
let Source = unzip("C:\downloads\doesnotwork.zip"), #"Imported CSV" = Csv.Document(Source,[Delimiter=",", Encoding=1252, QuoteStyle=QuoteStyle.None]) in #"Imported CSV"
For reference here is my current code, with the hardcoded size of that one ZIP file so you can see that it works. Obviously this code needs work to properly handle cases where the file size is reported as zero
// credits: http://www.excelandpowerbi.com/?p=155
// http://sql10.blogspot.com/2016/06/reading-zip-files-in-powerquery-m.html
let
Source = File.Contents("C:\downloads\doesnotwork.zip"),
//define function
Decompress = (ZIPFile) =>
let
//describe ZIP file format
MyBinaryFormat = BinaryFormat.Record([
MiscHeader=BinaryFormat.Binary(18),
FileSize=BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian),
UnCompressedFileSize=BinaryFormat.Binary(4),
FileNameLen=BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16, ByteOrder.LittleEndian),
ExtrasLen=BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16, ByteOrder.LittleEndian)
]) ,
// how many bytes to get
MyCompressedFileSizer = MyBinaryFormat(ZIPFile)[FileSize]+1 ,
MyCompressedFileSize = 7740272 ,
// impacts offset
MyFileNameLen = MyBinaryFormat(ZIPFile)[FileNameLen],
MyExtrasLen = MyBinaryFormat(ZIPFile)[ExtrasLen] ,
// describe where the actual data starts and how long it is
MyBinaryFormat2 = BinaryFormat.Record([
Header=BinaryFormat.Binary(30+MyFileNameLen+MyExtrasLen),
Data=BinaryFormat.Binary(MyCompressedFileSize)
]) ,
// grab the data and deflate it
DecompressData = Binary.Decompress(MyBinaryFormat2(ZIPFile)[Data], Compression.Deflate)
in
//return deflated binary data
DecompressData,
// call the function with the path to the ZIP file. Only extracts the first file in the ZIP, regardless of name
MyData = Decompress(Source)
in MyDataHere is a version that should work with all of your ZIP files. It ignores the local file entries and grabs the data from the central directory instead. Please test it out.
// expects full path to the ZIP file, only extracts the first data file after getting its size from the central directory
// https://en.wikipedia.org/wiki/Zip_(file_format)#Structure
(ZIPFile) =>
let
//read the entire ZIP file into memory - we'll use it often so this is worth it
Source = Binary.Buffer(File.Contents(ZIPFile)),
// get the full size of the ZIP file
Size = Binary.Length(Source),
//Find the start of the central directory at the sixth to last byte
Directory = BinaryFormat.Record([
MiscHeader=BinaryFormat.Binary(Size-6),
Start=BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian)
]) ,
Start = Directory(Source)[Start],
//find the first entry in the directory and get the compressed file size
FirstDirectoryEntry = BinaryFormat.Record([
MiscHeader=BinaryFormat.Binary(Start+20),
FileSize=BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian),
UnCompressedFileSize=BinaryFormat.Binary(4),
FileNameLen=BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16, ByteOrder.LittleEndian),
ExtrasLen=BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16, ByteOrder.LittleEndian)
]) ,
//figure ou where the raw data starts
Offset = 30+FirstDirectoryEntry(Source)[FileNameLen]+FirstDirectoryEntry(Source)[ExtrasLen],
Compressed = FirstDirectoryEntry(Source)[FileSize]+1,
//get the raw data of the compressed file
Raw = BinaryFormat.Record([
Header=BinaryFormat.Binary(Offset),
Data=BinaryFormat.Binary(Compressed)
])
// unzip it
in
Binary.Decompress(Raw(Source)[Data], Compression.Deflate)
Name it unzip and and then call it like this
let
Source = unzip("C:\downloads\doesnotwork.zip"),
#"Imported CSV" = Csv.Document(Source,[Delimiter=",", Encoding=1252, QuoteStyle=QuoteStyle.None])
in
#"Imported CSV"