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
Thankyou, lbendlin and MarkLaf for your responses.
Hi manyad,
We are pleased to note that your issue has been resolved. Thank you for sharing your insights and approach in resolving the issue, which will be beneficial to other members of the community. Should you have any further queries, please feel free to contact the Microsoft Fabric community.
Thank you.