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
- Función personalizada para extraer archivos de Zip, creada con éxito en línea con el blog de BI de Mark White
- 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.
artemus
¿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.
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:
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:
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:
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 👍
artemus
¿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