Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 the hope that people much smarter than me, will be able to identify the root-cause of the problem.
Background
New Information
This indicates that the Power Query error is being triggered by either the method used to compress the CSV file or potentially the code for the custom function not handling all compression formats (??)
ZIP File Details
I'm hoping this can shed some light on this problem & maybe generate a solution.
Thanks in advance for anyone spending time looking into this, and if it's useful here are the query and custom function in excel
Solved! Go to Solution.
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"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.