Web data source connection

https://www.ffiec.gov/npw/FinancialReport/ReturnAttributesActiveZipFileCSV

 

 

You would then use your favorite unzipper to extract the actual CSV.

 

Note:  Your ZIP files are malformed (missing the Extra Information)

 

UnzipNoExtra:

(ZIPFile) =>
let
    //read the entire ZIP file into memory - we'll use it often so this is worth it
    Source = Binary.Buffer(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), 
                    DataSize=BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian),
                    UnCompressedFileSize=BinaryFormat.Binary(4),
                    FileNameLen=BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16, ByteOrder.LittleEndian)
            ]) ,
    //figure out where the raw data starts            
    Offset = 30+FirstDirectoryEntry(Source)[FileNameLen],
    Compressed = FirstDirectoryEntry(Source)[DataSize]+1,
    //get the raw data of the compressed file
    Raw = BinaryFormat.Record([
                    Header=BinaryFormat.Binary(Offset),
                    Data=BinaryFormat.Binary(Compressed)//,
            ]) 
    // inflate it
in 
    Binary.Decompress(Raw(Source)[Data], Compression.Deflate)

 

finally:

let
    Source = UnzipNoExtra(Web.Contents("https://www.ffiec.gov/npw/FinancialReport/ReturnAttributesActiveZipFileCSV")),
    #"Imported CSV" = Csv.Document(Source,[Delimiter=",", Columns=74, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(#"Imported CSV", [PromoteAllScalars=true])
in
    #"Promoted Headers"