Forum Discussion
Trying to extract files from ZIP
I am trying to load the ZIP file in power BI through below query but it throws the error “We didn’t recognizes the format of your first file (). Please filter the list of files so it contains only supported types (Text, .csv, Excel workbooks etc.) and try again”.
I have tried this http://sql10.blogspot.com/2016/06/reading-zip-files-in-powerquery-m.html and https://www.excelandpowerbi.com/?p=155 but did not work either...I want to extract the .csv from ZIP file and load the data (if .csv can then be converted to .xlsx that would be more great)
Please help..
14 Replies
This is a few months old, but I thought I would chime in. I'm curious if you get the warning from Excel when you open your file that says the file format and extension don't match. HTML wrapped in an xls extension can cause this. If you open the .xls file in your zip and then save as an .xls then it will probably work. But since that is not feasible to do every time you may want to update your registry or Trusted Center locations so that you don't get the warning any more.
Thank you for your suggestion. Yeah, this was a few months ago, but I will revisit and see how much rework I will need to do to test this idea. But, I do recall, given data limitations within the excel files, I have to manually modify the file name (with a date) to get things dated correctly. So, I have to "touch" the files anyway - so I kinda gave up on hte idea of pulling from the initial zip file.
But, I will definitely keep this in mind - as things continuely evolve.
thanks
Hi sowmya2553
As tested, with help of this blog and this thread,
I can get tables from zip file,
Click on "Binary", you could see your data.
You could open my pbix, change the file path below with yourself.
let path = "C:\Users\maggiel\Desktop\case\6\6.28", Source = Folder.Files(path), #"Filtered Rows to only .zip" = Table.SelectRows(Source, each ([Extension] = ".zip")), #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows to only .zip",{"Content"}), #"Added Custom UnzipContents" = Table.AddColumn(#"Removed Other Columns", "Custom", each UnzipContents([Content])), Custom = #"Added Custom UnzipContents"{0}[Custom] in CustomNote, don't use the path for the zip file, but enter the path of the folder where zip file lies.
Best Regards
MaggieCommunity Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hello Maggie,
I have tried exactly the same but still getting the same error message :(
Also I have tried to edit on your power BI as well but still the same.
Kindly help
I am unable to attacht the screenshot as its not giving me option to attach the file or image.
Regards,
Sowmya
v-juanli-msft wrote:Hi sowmya2553
As tested, with help of this blog and this thread,
I can get tables from zip file,
Click on "Binary", you could see your data.
You could open my pbix, change the file path below with yourself.
let path = "C:\Users\maggiel\Desktop\case\6\6.28", Source = Folder.Files(path), #"Filtered Rows to only .zip" = Table.SelectRows(Source, each ([Extension] = ".zip")), #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows to only .zip",{"Content"}), #"Added Custom UnzipContents" = Table.AddColumn(#"Removed Other Columns", "Custom", each UnzipContents([Content])), Custom = #"Added Custom UnzipContents"{0}[Custom] in CustomNote, don't use the path for the zip file, but enter the path of the folder where zip file lies.
Best Regards
MaggieCommunity Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-juanli-msft wrote:Hi sowmya2553
As tested, with help of this blog and this thread,
I can get tables from zip file,
Click on "Binary", you could see your data.
You could open my pbix, change the file path below with yourself.
let path = "C:\Users\maggiel\Desktop\case\6\6.28", Source = Folder.Files(path), #"Filtered Rows to only .zip" = Table.SelectRows(Source, each ([Extension] = ".zip")), #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows to only .zip",{"Content"}), #"Added Custom UnzipContents" = Table.AddColumn(#"Removed Other Columns", "Custom", each UnzipContents([Content])), Custom = #"Added Custom UnzipContents"{0}[Custom] in CustomNote, don't use the path for the zip file, but enter the path of the folder where zip file lies.
Best Regards
MaggieCommunity Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.My code is as below
let
path = "C:\Users\MNLUSowm\Desktop\BEA",
Source = Folder.Files(path),
#"Filtered Rows to only .zip" = Table.SelectRows(Source, each ([Extension] = ".zip")),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows to only .zip",{"Content"}),
#"Added Custom UnzipContents" = Table.AddColumn(#"Removed Other Columns", "Custom", each UnzipContents([Content])),
Custom = #"Added Custom UnzipContents"{0}[Custom]
in
Custom
Hi sowmya2553
please open properties for that folder, paste the path under "Location" in the "path" as below
let path = "C:\Users\maggiel\Desktop\case\6\6.28",If your issue doesn't solve, please show me what's error you meet.
To upload picture here, please sign in with your Power BI Account here.
You could also upload file/screenshots to the OneDrive and share the link here.
Best Regards
MaggieCommunity Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hi Maggie,
I have tried and still the same error.
Kindly verify screenshot.
Regards,
Sowmya
Hello, I am having the same issue using the same MarkWhite function, with the exceptions of xlsx files. Once I get past this hurdle, each file within the zip file has same name, and then each file has 4 unique tab names. Will I be able to filter and extract same as a single file? Appreciate advice and a solution. Thanks!
Not sure if it help any, but I also made an unzip program: https://community.powerbi.com/t5/Power-Query/How-to-connect-Azure-DevOps-REST-API-in-to-power-bi/m-p/895318/highlight/true#M30599. Maybe it will work better for you?
