Forum Discussion
decompress, skip lines, delimit
- 5 months ago
Edit: actually, looking again at your issue + code (which is basically the same as mine), I think the main thing to change is to add a Columns field to your options record. When it's unspecified (as we are both doing in our M), I think it just looks at top row to determine column count (some number less than the ~20 you mentioned). So, I think the simple fix for you is:
Imported = Csv.Document(Decompressed, [Columns=20, Delimiter="|", Encoding=1252]), // <-- added Columns=#Or, upstream, get the appropriate number of delimiters appended to first row such that it matches column count for primary data
Original post for references:
The following worked for me.
For reference, I made two simple csvs of format:
Zipped each with 7zip into gz. Note: original csvs in same folder but we are filtering them out per your procedure.
The following M successfully unpacks these.
let Source = Folder.Files( #"<FolderPath>" ), FilterGz = Table.SelectRows(Source, each [Extension] = ".gz"), AddGzParse = Table.AddColumn( FilterGz, "gz_tables", each // steps for unpacking each gz - you can put these in a separate function if desired [ gz = [Content], csv_binary = Binary.Decompress(gz, Compression.GZip), csv_table = Csv.Document(csv_binary,null,"|"), csv_table_trim = Table.RemoveLastN( Table.RemoveFirstN(csv_table,1), 1), csv_table_promoted = Table.PromoteHeaders(csv_table_trim) ] [csv_table_promoted] ), // use our output from first row to automate column type and expansion // note, it's csv so all cols come in as text FirstTableType = Value.Type( List.First( AddGzParse[gz_tables] ) ), FixGzParseColType = Table.TransformColumns( AddGzParse, {{"gz_tables", each {_}{0}, FirstTableType }} ), SelectMetaAndTables = Table.SelectColumns( FixGzParseColType,{"Name", "Date created", "gz_tables"} ), ExpandGz_tables = Table.ExpandTableColumn( SelectMetaAndTables, "gz_tables", Type.TableSchema(FirstTableType)[Name] ) in ExpandGz_tablesOutput