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

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Same issue as Katie.

       

      I have this function that works:

       

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

      Tested it here to make sure. I get 30 columns of text extracted.

       

      let
    Source = UnpackGzip("C:\Users\Michael\CPU-1\disk\var\log\app.20180418002723.log.gz")
in
    Source

       

      Great.

       

      But when I try to use on a batch of files like this, I get an error on the Table.Add Column saying it cant convert Binary to text. There are 120 .gz files in the folder.

       

      let
    path = "C:\Users\Michael\CPU-1\disk\var\log\",
    Source = Folder.Files(path), 
    #"Filtered Rows to only .gz" = Table.SelectRows(Source, each ([Extension] = ".gz")),
    #"Added Custom UnzipContents" = Table.AddColumn(#"Filtered Rows to only .gz", "Custom", each UnpackGzip([Content])),
    #"Expanded Attributes" = Table.ExpandRecordColumn(#"Added Custom UnzipContents", "Attributes", {"Content Type", "Kind", "Size"}, {"Attributes.Content Type", "Attributes.Kind", "Attributes.Size"})
in
    #"Expanded Attributes"

       

      Michael

      • thomas_pike's avatar
        thomas_pike
        Frequent Visitor
        7 years ago

        Katie and Michael,

         

        Did you ever get this approach to work, I too am attempting to use the provided function and getting the error in regards to not being able to convert binary into text.

         

        Tom