Trying to extract files from ZIP

[sowmya2553]

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..

[artemus]

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.... Maybe it will work better for you?

[rahkim]

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.  

[Dellis81]

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

[v-juanli-msft]

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
Maggie

Community 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.

[sowmya2553]

Hi Maggie,

I have tried and still the same error.

Kindly verify screenshot.

Regards,

Sowmya

[sowmya2553]

Any update on this further?

[Dellis81]

No, not really.  Ended up utilizing a zip utiliity that allows autonaming of unzipped files.  From there, I went down the power query option of connecting to a folder, that pulls in all files from said folder.

[sowmya2553]

Can you please guide me how did you do that?

I am not fully understanding..

---

@Dellis81 wrote:

No, not really.  Ended up utilizing a zip utiliity that allows autonaming of unzipped files.  From there, I went down the power query option of connecting to a folder, that pulls in all files from said folder.

---

[Dellis81]

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!

[sowmya2553]

Hi there,

Once you extract the files from Zip, you can select all the worksheet names that you want to load in power BI.

You can use the append query option (but ensure you have the same column names in all sheets) so that it will append in power query.

[v-juanli-msft]

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
    Custom

Note, don't use the path for the zip file, but enter the path of the folder where zip file lies.

Best Regards
Maggie

Community 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.

[Anonymous]

Hi there,

This doesn't work, i downloaded your file, changed the path, and received this error

Do you think you can help please?

[sowmya2553]

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
    Custom

Note, don't use the path for the zip file, but enter the path of the folder where zip file lies.

Best Regards
Maggie

Community 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
    Custom

Note, don't use the path for the zip file, but enter the path of the folder where zip file lies.

Best Regards
Maggie

Community 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.

---

[sowmya2553]

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