Forum Discussion

jerald's avatar
jerald
Frequent Visitor
9 years ago
Solved

Decompress and load multiple .gz files from multiple folders

I was able to decompress a single .gz file with a single csv in it by following Chris Webb's blog.

 

Ive also seen these blogs but was not able to successfully load and decompress files:

 

http://sql10.blogspot.sg/2016/06/reading-zip-files-in-powerquery-m.html
http://www.excelandpowerbi.com/?p=155

 

 

basically, my folder structure is like this

 

Parent_folder

  • Folder_A
    • Folder_A_1
      • file.gz
    • Folder_A_2
      • file.gz

 

 

Need Help
  • hugoberry's avatar
    hugoberry
    9 years ago

    It would be useful to get a peek at one of the files that you are trying to unzip.

     

    Meanwhile you can check that you use the right compression on the files. The MSDN article  mentions two compression mechanisms. Have you tried both?

    Compression.GZip
    Compression.Deflate

     

    Chris in his blog refers more to GZip, whilst Ken and Mark refer both to Deflate.

    Another thing to mention. GZip will be compressing only single file, while ZIP archives would embed entire folder structures. What's your case?

11 Replies

  • hugoberry's avatar
    hugoberry
    Icon for Responsive Resident rankResponsive Resident
    9 years ago

    It would be useful to get a peek at one of the files that you are trying to unzip.

     

    Meanwhile you can check that you use the right compression on the files. The MSDN article  mentions two compression mechanisms. Have you tried both?

    Compression.GZip
    Compression.Deflate

     

    Chris in his blog refers more to GZip, whilst Ken and Mark refer both to Deflate.

    Another thing to mention. GZip will be compressing only single file, while ZIP archives would embed entire folder structures. What's your case?

    • jerald's avatar
      jerald
      Frequent Visitor
      9 years ago

      Thank you so much! I just followed followed the instruction in one of the comment from Chris' blog.  Then invoked it.


      (zip) =>
let
    Source = 
		Binary.Decompress(
			File.Contents(
				zip),
				Compression.GZip
		),
    #"Imported" = 
		Csv.Document(
			Source,
			[Delimiter=":", Columns=2, Encoding=1252])
in
    #"Imported"

       

      • kehamilton92's avatar
        kehamilton92
        Icon for Advocate I rankAdvocate I
        9 years ago

        Hi Jerald,

         

        When invoking the function, did you select Content as your zip field?  I'm doing that and running getting an error that reads"
        An error occurred in the "" query.  Expression.Error: We cannot convert a value of type Binary to type Text. Details: Value = Binary Type=Type
        Did you run into this?  Any suggestions on how to get around it?

         

        Thank you,

        Katie

  • Ansari010's avatar
    Ansari010
    New Member
    3 years ago

    R Script is an another option to load .gz file in Power BI Desktop. Below is the code snippet.

     

    install.packages -> data.table
library('data.table')
data <- fread(file="C:/Test/Employees.csv.gz")
data

    See more details using this link

    I know this is an old post but it can help someone with other options.