Error al extraer el archivo de ZIP

Un usuario Pregunto ✅

scott_od

Sé que ha habido varios intentos de abordar esto anteriormente, pero hasta ahora sin resolución (que yo sepa).

Por lo tanto, proporciono información más detallada sobre las propiedades del archivo zip, con la esperanza de que personas mucho más inteligentes que yo puedan identificar la causa raíz del problema.

Fondo

  1. Función personalizada para extraer archivos de Zip, creada con éxito en línea con el blog de BI de Mark White
  2. Al intentar acceder a los datos binarios del archivo CSV, Power Query arroja el error «No reconocimos el formato de su primer archivo (). Filtre la lista de archivos para que solo contenga tipos admitidos (texto, .csv, libros de Excel, etc.) e intente nuevamente«

Nueva información

  • El error mencionado anteriormente en el punto #2, solo ocurre cuando estoy usando archivos ZIP que han sido generados por SAP BI.
  • Si extraigo el archivo CSV del zip y creo manualmente un nuevo archivo zip que contiene el mismo CSV, entonces Power Query puede acceder con éxito a los datos binarios usando la misma función personalizada creada anteriormente en el punto n.º 1.

Esto indica que el error de Power Query se desencadena por el método utilizado para comprimir el archivo CSV o, potencialmente, por el código de la función personalizada que no maneja todos los formatos de compresión (??)

Detalles del archivo ZIP

  • El archivo zip sin el problema se ha denominado funciona.zip y el que causa el error es doesnotwork.zip
  • Aquí puede ver las propiedades de los archivos CSV comprimidos dentro de sus respectivos ZIP (funciona y no funciona), las diferencias se resaltan en amarillo en la imagen «no funciona»
  • Estas son las propiedades de los archivos zip, nuevamente con las diferencias resaltadas en amarillo.

Espero que esto pueda arrojar algo de luz sobre este problema y tal vez generar una solución.

Gracias de antemano a cualquiera que dedique tiempo a investigar esto, y si es útil, aquí están las funciones de consulta y personalizadas en Excel.

¿Ha probado la implementación que publiqué aquí: https://community.powerbi.com/t5/Power-Query/How-to-connect-Azure-DevOps-REST-API-in-to-power-bi/mp.. .

lbendlin

En respuesta a lbendlin

Aquí hay una versión que debería funcionar con todos sus archivos ZIP. Ignora las entradas del archivo local y toma los datos del directorio central en su lugar. Por favor, pruébalo.

// 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)

Nómbralo descomprimir y luego llámalo así

let
    Source = unzip("C:downloadsdoesnotwork.zip"),
    #"Imported CSV" = Csv.Document(Source,[Delimiter=",",  Encoding=1252, QuoteStyle=QuoteStyle.None])
in
    #"Imported CSV"

lbendlin

Su archivo doesnotwork.zip no cumple con el estándar ZIP «antiguo». Tanto el tamaño del archivo comprimido como el tamaño del archivo sin comprimir del archivo «Informe 1.csv» son cero en el encabezado local. Ese comportamiento normalmente se reserva para archivos de más de 4 GB.

Eso puede ser manejado por las herramientas de descompresión (que usan el directorio central al final), pero su código M (que no usa el directorio, usa encabezados de archivos locales) espera datos reales allí. Debe modificar el código M para manejar esta situación diferente.

https://en.wikipedia.org/wiki/Zip_(formato_de_archivo)#Estructura

Esto se pone aún más emocionante:

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

Aquí está el volcado hexadecimal del final de su archivo zip.

0x02014b50 marca el comienzo de la copia oficial del directorio central, como lo indican los últimos bytes 0x00761ba9 (que apuntan al inicio del directorio)

luego, en 0x00761bbd, puede ver el tamaño comprimido del primer archivo (0x00761b6f) y el tamaño sin comprimir (0x04eddec7), que se traduce en 82697927 bytes, exactamente el tamaño sin comprimir del informe 1.csv

Eso significa que debe proporcionar el valor 0x00761b6f a la función de descompresión.

Anotación 2020-06-27 152623.png

lbendlin

En respuesta a lbendlin

Como referencia, aquí está mi código actual, con el tamaño codificado de ese archivo ZIP para que pueda ver que funciona. Obviamente, este código necesita trabajo para manejar adecuadamente los casos en los que el tamaño del archivo se informa como cero.

//  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:downloadsdoesnotwork.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

lbendlin

En respuesta a lbendlin

Aquí hay una versión que debería funcionar con todos sus archivos ZIP. Ignora las entradas del archivo local y toma los datos del directorio central en su lugar. Por favor, pruébalo.

// 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)

Nómbralo descomprimir y luego llámalo así

let
    Source = unzip("C:downloadsdoesnotwork.zip"),
    #"Imported CSV" = Csv.Document(Source,[Delimiter=",",  Encoding=1252, QuoteStyle=QuoteStyle.None])
in
    #"Imported CSV"

scott_od

En respuesta a lbendlin

@lbendlin, en primer lugar, gracias por sus esfuerzos y excelente análisis, realmente lo aprecio.

Probé su código, pero por el momento solo devuelve la primera columna del archivo zip:

Capturar.JPG

lbendlin

En respuesta a scott_od

Elimine y vuelva a aplicar el paso de importación csv.

scott_od

En respuesta a lbendlin

Disculpa pero no entiendo??

lbendlin

En respuesta a scott_od

las funciones .Buffer cargan los datos una vez en la memoria y evitan recargarlos una y otra vez (como se hace en el código original)

scott_od

En respuesta a lbendlin

esa parte la entiendo.

Lo que no entiendo es a qué te referías con «Eliminar y volver a aplicar el paso de importación csv» o cómo obtener todas las columnas del zip

lbendlin

En respuesta a scott_od

Quise decir que deberías eliminar el paso CSV de mis últimas instrucciones. Prueba esto en su lugar. Dará como resultado un icono CSV en el que luego puede hacer doble clic.

let
    Source = unzip("C:downloadsdoesnotwork.zip")
in
    Source

scott_od

En respuesta a lbendlin

@lbendlin lo siento, estoy de vuelta en este tema otra vez 🙄

¿Cómo modificaría su código para manejar múltiples archivos CSV dentro de un ZIP?
Actualmente siempre obtengo el primer CSV.

Gracias

lbendlin

En respuesta a scott_od

Aquí hay un enlace a una solución integral completa de @artemus

https://community.powerbi.com/t5/Power-Query/How-to-connect-Azure-DevOps-REST-API-in-to-power-bi/mp…

Rob_B

En respuesta a lbendlin

@lbendlin gracias por brindar una solución a este problema. Me estoy encontrando con un problema y no puedo entender lo que está pasando. Cuando intento invocar la función Descomprimir personalizada, recibo el siguiente error:

An error occurred in the " query. Expression.Error: We cannot convert a value of type Binary to type Text.
Details:
     Value=[Binary]
     Type=[Type]

¿Algunas ideas? Debo señalar que primero probé la solución de Mark White (http://sql10.blogspot.com/2016/06/reading-zip-files-in-powerquery-m.html), pero también tuve problemas con esa solución.

¡Gracias de antemano por tu ayuda!

lbendlin

En respuesta a Rob_B

Es posible que desee compartir algunos detalles más. También escribí una entrada de blog sobre este mismo tema si está interesado.

Rob_B

En respuesta a lbendlin

Gracias @lbendlin, me encantaría ver tu blog. ¿Puedes compartir el enlace?

Intenté usar la solución de Mark White para acceder a este archivo zip, pero recibo el siguiente error:

Rob_B_0-1600900610934.png

Su función personalizada funciona para otros archivos zip en el mismo sitio web (utilizando la función Web.Contents como fuente), sin embargo, no funciona en el archivo zip vinculado anteriormente. Supongo que el tamaño del archivo es demasiado grande (1,52 GB comprimido, >10 GB sin comprimir). El .csv en la carpeta .zip es tan grande que se usa Zip64. Quizás por eso la solución de Mark no funciona.

Probé su función pero cuando la invoco, Power BI me da el siguiente mensaje de error:

Rob_B_1-1600900997000.png

No estoy lo suficientemente familiarizado con el código M para averiguar qué está mal en su función. Debo señalar que recibo el mismo error para todos los archivos zip en el sitio web vinculado anteriormente, incluidos los archivos zip más pequeños que no usan Zip64.

¡Gracias!

Robar

lbendlin

En respuesta a Rob_B

Aquí esta la Blog

https://community.powerbi.com/t5/Community-Blog/Trabajando-Con-Archivos-Zip-en-Power-Query/ba-p/1190186

pero el tamaño de sus archivos puede ser el problema real. Solo probé con archivos de hasta 600 MB.

Rob_B

En respuesta a lbendlin

Gracias @lbendlin, ¡tu publicación de blog es realmente útil!

El siguiente paso es descubrir cómo modificar la función para acomodar archivos de mayor tamaño usando Zip64 😀

scott_od

En respuesta a lbendlin

Ah vale, disculpas.

Confirme que todo funciona como se esperaba.

Gracias de nuevo por su tiempo y esfuerzo en esto 👍

¿Ha probado la implementación que publiqué aquí: https://community.powerbi.com/t5/Power-Query/How-to-connect-Azure-DevOps-REST-API-in-to-power-bi/mp.. .

lbendlin

En respuesta a artemus

@artemus gracias por brindar la solución estructural completa. He agregado una pizca de Binary.Buffer para hacerlo un poco más rápido.

scott_od

En respuesta a artemus

@artemus No había visto esa publicación, pero tienes razón, ¡sí funciona! 🙂

Gracias por tu ayuda

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *