Forum Discussion
Decompress and load multiple .gz files from multiple folders
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?
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
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
- First create a function: I called it: fnUnpackGzipLinesFromBinary
(zip) => let Source = Binary.Decompress( File.Contents( zip), Compression.GZip ), #"Imported" = Table.FromColumns({Lines.FromBinary(Source,null,null,1252)}) in #"Imported"Next, create this query based on folder source that points to your folder and runs the unzip commmand on each row it finds.The sorting is unnecesary in the code. However, I apply a filter to see only files that have a .gz extension to avoid errors.let path = "C:\Users\Michael\CPU-1\disk\var\log\", Source = Folder.Files(path), #"Expanded Attributes1" = Table.ExpandRecordColumn(Source, "Attributes", {"Size"}, {"Attributes.Size"}), #"Sorted Rows" = Table.Sort(#"Expanded Attributes1",{{"Attributes.Size", Order.Ascending}}), #"Added Custom" = Table.AddColumn(#"Sorted Rows", "LogFile", each Text.Start([Name],3)), FilteredGzRows = Table.SelectRows(#"Added Custom", each ([Extension] = ".gz")), UnpackGzips = Table.AddColumn(FilteredGzRows, "Custom", each fnUnpackGzipLinesFromBinary([Folder Path]&[Name])), in UnpackGzipsAnonymous thanks for the code. I realise where I had been going wrong, I had been trying to call the fnUnpackGzipLinesFromBinary on the Contents column, rather than the file directly itself. I used a slightly different function that will parse the CSV.
For anyone looking for this in the future, create a function "fnUnpackGzipLinesFromBinary":
(zip) => let Source = Binary.Decompress( File.Contents( zip), Compression.GZip ), #"Imported" = Csv.Document(Source,[Delimiter=",", Columns=28, Encoding=1252, QuoteStyle=QuoteStyle.None]), #"Promoted Headers" = Table.PromoteHeaders(#"Imported", [PromoteAllScalars=true]) in #"Promoted Headers"Then call the function from your load scripts:
let Source = Folder.Files("C:\Users\user\files"), #"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".gz")), UnpackGzips = Table.AddColumn(#"Filtered Rows", "Custom", each fnUnpackGzipLinesFromBinary([Folder Path]&[Name])), #"Expanded Custom" = Table.ExpandTableColumn(UnpackGzips, ...insert column list here...) in #"Expanded Custom"- Alirezam
Helper V
I have tried these codes and they did not work for me. This is what I ended up:
Any idea?
