Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 ...
Need Help
  • lbendlin's avatar
    lbendlin
    6 years ago

    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"
lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
6 years ago

Your doesnotwork.zip file does not adhere to the "old" ZIP standard. Both the compressed file size and the uncompressed file size of the "Report 1.csv"  file are zero in the local header. That behavior is normally reserved for files over 4GB.

 

That can be handled by the unzip tools (which use the central directory at the end), but your M code (which doesn't use the directory - it uses local file headers ) expects actual data there.  You need to modify the M code to handle this different situation.

 

https://en.wikipedia.org/wiki/Zip_(file_format)#Structure

 

This gets even more exciting:

https://stackoverflow.com/questions/4802097/how-does-one-find-the-start-of-the-central-directory-in-zip-files

 

Here is the hex dump of the end of your zip file.

0x02014b50 marks the beginning of the official copy of the central directory, as indicated by the last few bytes 0x00761ba9 (which point to the start of the directory)

then at 0x00761bbd you can see the compressed size of the first file (0x00761b6f) and the uncompressed size (0x04eddec7) which translates to 82697927 bytes - exactly the uncompressed size of report 1.csv

 

That means you need to provide the 0x00761b6f value to the decompression function.

 

  

 

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
6 years ago

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 MyData