Forum Discussion
open file as text
Hello,
Getting the "We didn't recognize the format of your first file" error when trying to extract the binaries.
Tried to link to the file directly and that didn't work either.
Any ideas on how to access it so to be able to model it?
Thank you
J
Here you go. Just change the Source line to where you got the data
// B879-MCK-MARRIOTT-CENTRAL-2018 let Source = Text.FromBinary(File.Contents("C:\Users\artemus\Downloads\B879-MCK-MARRIOTT-CENTRAL-2018.slog")), #"Split Text" = Text.Split(Source, "#(cr)#(lf)#(cr)#(lf)"), #"Removed Top Items" = List.Skip(#"Split Text",1), #"Converted to Table" = Table.FromList(#"Removed Top Items", Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Extracted Text After Delimiter" = Table.TransformColumns(#"Converted to Table", {{"Column1", each Text.AfterDelimiter(_, "$"), type text}}), #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Text After Delimiter", "Column1", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Column1.1", "Column1.2"}), #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}}), #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type", "Column1.2", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Column1.2.1", "Column1.2.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Column1.2.1", type date}}), #"Split Column by Delimiter2" = Table.SplitColumn(#"Changed Type1", "Column1.2.2", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Column1.2.2.1", "Column1.2.2.2"}), #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Column1.2.2.1", type time}, {"Column1.2.2.2", type text}}), #"Merged Date and Time" = Table.CombineColumns(#"Changed Type2", {"Column1.2.1", "Column1.2.2.1"}, (columns) => List.First(columns) & List.Last(columns), "Merged"), #"Changed Type3" = Table.TransformColumnTypes(#"Merged Date and Time",{{"Merged", type datetime}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type3",{{"Column1.1", "Session"}, {"Merged", "Timestamp"}}), #"Split Column by Position" = Table.SplitColumn(#"Renamed Columns", "Column1.2.2.2", Splitter.SplitTextByPositions({0, 1}, false), {"Column1.2.2.2.1", "Column1.2.2.2.2"}), #"Changed Type4" = Table.TransformColumnTypes(#"Split Column by Position",{{"Column1.2.2.2.1", type text}, {"Column1.2.2.2.2", type text}}), #"Renamed Columns1" = Table.RenameColumns(#"Changed Type4",{{"Column1.2.2.2.1", "OperationType"}}), #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns1",">","Input",Replacer.ReplaceText,{"OperationType"}), #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","<","Output",Replacer.ReplaceText,{"OperationType"}), #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1",".","Local",Replacer.ReplaceText,{"OperationType"}), #"Split Column by Delimiter3" = Table.SplitColumn(#"Replaced Value2", "Column1.2.2.2.2", Splitter.SplitTextByEachDelimiter({"#(cr)#(lf)"}, QuoteStyle.Csv, false), {"Column1.2.2.2.2.1", "Column1.2.2.2.2.2"}), #"Changed Type5" = Table.TransformColumnTypes(#"Split Column by Delimiter3",{{"Column1.2.2.2.2.1", type text}, {"Column1.2.2.2.2.2", type text}}), #"Renamed Columns2" = Table.RenameColumns(#"Changed Type5",{{"Column1.2.2.2.2.2", "Metadata"}}), #"Split Column by Delimiter4" = Table.SplitColumn(#"Renamed Columns2", "Column1.2.2.2.2.1", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Column1.2.2.2.2.1.1", "Column1.2.2.2.2.1.2"}), #"Renamed Columns3" = Table.RenameColumns(#"Split Column by Delimiter4",{{"Column1.2.2.2.2.1.1", "Operation"}, {"Column1.2.2.2.2.1.2", "Paramter"}}) in #"Renamed Columns3"Here is one that should work (I think). Just replace the path in the Download query
// SlogParser (file as binary) => let Source = Text.FromBinary(file), #"Split Text" = Text.Split(Source, "#(cr)#(lf)#(cr)#(lf)"), #"Removed Top Items" = List.Skip(#"Split Text",1), #"Converted to Table" = Table.FromList(#"Removed Top Items", Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Extracted Text After Delimiter" = Table.TransformColumns(#"Converted to Table", {{"Column1", each Text.AfterDelimiter(_, "$"), type text}}), #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Text After Delimiter", "Column1", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Column1.1", "Column1.2"}), #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}}), #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type", "Column1.2", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Column1.2.1", "Column1.2.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Column1.2.1", type date}}), #"Split Column by Delimiter2" = Table.SplitColumn(#"Changed Type1", "Column1.2.2", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Column1.2.2.1", "Column1.2.2.2"}), #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Column1.2.2.1", type time}, {"Column1.2.2.2", type text}}), #"Merged Date and Time" = Table.CombineColumns(#"Changed Type2", {"Column1.2.1", "Column1.2.2.1"}, (columns) => List.First(columns) & List.Last(columns), "Merged"), #"Changed Type3" = Table.TransformColumnTypes(#"Merged Date and Time",{{"Merged", type datetime}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type3",{{"Column1.1", "Session"}, {"Merged", "Timestamp"}}), #"Split Column by Position" = Table.SplitColumn(#"Renamed Columns", "Column1.2.2.2", Splitter.SplitTextByPositions({0, 1}, false), {"Column1.2.2.2.1", "Column1.2.2.2.2"}), #"Changed Type4" = Table.TransformColumnTypes(#"Split Column by Position",{{"Column1.2.2.2.1", type text}, {"Column1.2.2.2.2", type text}}), #"Renamed Columns1" = Table.RenameColumns(#"Changed Type4",{{"Column1.2.2.2.1", "OperationType"}}), #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns1",">","Input",Replacer.ReplaceText,{"OperationType"}), #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","<","Output",Replacer.ReplaceText,{"OperationType"}), #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1",".","Local",Replacer.ReplaceText,{"OperationType"}), #"Split Column by Delimiter3" = Table.SplitColumn(#"Replaced Value2", "Column1.2.2.2.2", Splitter.SplitTextByEachDelimiter({"#(cr)#(lf)"}, QuoteStyle.Csv, false), {"Column1.2.2.2.2.1", "Column1.2.2.2.2.2"}), #"Changed Type5" = Table.TransformColumnTypes(#"Split Column by Delimiter3",{{"Column1.2.2.2.2.1", type text}, {"Column1.2.2.2.2.2", type text}}), #"Renamed Columns2" = Table.RenameColumns(#"Changed Type5",{{"Column1.2.2.2.2.2", "Metadata"}}), #"Split Column by Delimiter4" = Table.SplitColumn(#"Renamed Columns2", "Column1.2.2.2.2.1", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Column1.2.2.2.2.1.1", "Column1.2.2.2.2.1.2"}), #"Renamed Columns3" = Table.RenameColumns(#"Split Column by Delimiter4",{{"Column1.2.2.2.2.1.1", "Operation"}, {"Column1.2.2.2.2.1.2", "Paramter"}}) in #"Renamed Columns3" // Downloads let Source = Folder.Files("C:\Users\artemus\Downloads"), #"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".slog")), #"Invoked Custom Function" = Table.AddColumn(#"Filtered Rows", "SlogTable", each SlogParser([Content])), #"Removed Other Columns" = Table.SelectColumns(#"Invoked Custom Function",{"SlogTable"}), #"Expanded SlogTable" = Table.ExpandTableColumn(#"Removed Other Columns", "SlogTable", {"Session", "Timestamp", "OperationType", "Operation", "Paramter", "Metadata"}, {"Session", "Timestamp", "OperationType", "Operation", "Paramter", "Metadata"}) in #"Expanded SlogTable"1. Rename the function query to SlogParser
2. The function query is intermediate. Just leave it alone, it won't output any table by itself.
8 Replies
Here you go. Just change the Source line to where you got the data
// B879-MCK-MARRIOTT-CENTRAL-2018 let Source = Text.FromBinary(File.Contents("C:\Users\artemus\Downloads\B879-MCK-MARRIOTT-CENTRAL-2018.slog")), #"Split Text" = Text.Split(Source, "#(cr)#(lf)#(cr)#(lf)"), #"Removed Top Items" = List.Skip(#"Split Text",1), #"Converted to Table" = Table.FromList(#"Removed Top Items", Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Extracted Text After Delimiter" = Table.TransformColumns(#"Converted to Table", {{"Column1", each Text.AfterDelimiter(_, "$"), type text}}), #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Text After Delimiter", "Column1", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Column1.1", "Column1.2"}), #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}}), #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type", "Column1.2", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Column1.2.1", "Column1.2.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Column1.2.1", type date}}), #"Split Column by Delimiter2" = Table.SplitColumn(#"Changed Type1", "Column1.2.2", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Column1.2.2.1", "Column1.2.2.2"}), #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Column1.2.2.1", type time}, {"Column1.2.2.2", type text}}), #"Merged Date and Time" = Table.CombineColumns(#"Changed Type2", {"Column1.2.1", "Column1.2.2.1"}, (columns) => List.First(columns) & List.Last(columns), "Merged"), #"Changed Type3" = Table.TransformColumnTypes(#"Merged Date and Time",{{"Merged", type datetime}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type3",{{"Column1.1", "Session"}, {"Merged", "Timestamp"}}), #"Split Column by Position" = Table.SplitColumn(#"Renamed Columns", "Column1.2.2.2", Splitter.SplitTextByPositions({0, 1}, false), {"Column1.2.2.2.1", "Column1.2.2.2.2"}), #"Changed Type4" = Table.TransformColumnTypes(#"Split Column by Position",{{"Column1.2.2.2.1", type text}, {"Column1.2.2.2.2", type text}}), #"Renamed Columns1" = Table.RenameColumns(#"Changed Type4",{{"Column1.2.2.2.1", "OperationType"}}), #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns1",">","Input",Replacer.ReplaceText,{"OperationType"}), #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","<","Output",Replacer.ReplaceText,{"OperationType"}), #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1",".","Local",Replacer.ReplaceText,{"OperationType"}), #"Split Column by Delimiter3" = Table.SplitColumn(#"Replaced Value2", "Column1.2.2.2.2", Splitter.SplitTextByEachDelimiter({"#(cr)#(lf)"}, QuoteStyle.Csv, false), {"Column1.2.2.2.2.1", "Column1.2.2.2.2.2"}), #"Changed Type5" = Table.TransformColumnTypes(#"Split Column by Delimiter3",{{"Column1.2.2.2.2.1", type text}, {"Column1.2.2.2.2.2", type text}}), #"Renamed Columns2" = Table.RenameColumns(#"Changed Type5",{{"Column1.2.2.2.2.2", "Metadata"}}), #"Split Column by Delimiter4" = Table.SplitColumn(#"Renamed Columns2", "Column1.2.2.2.2.1", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Column1.2.2.2.2.1.1", "Column1.2.2.2.2.1.2"}), #"Renamed Columns3" = Table.RenameColumns(#"Split Column by Delimiter4",{{"Column1.2.2.2.2.1.1", "Operation"}, {"Column1.2.2.2.2.1.2", "Paramter"}}) in #"Renamed Columns3"Thank you artemus.
Works a treat! :smileyhappy:
I am keen to learn how you did it.
It looks that if I want to run it for different files I will always need to edit the query in the Advanced Editor since it looks that the Source does not accept parameters.
In my case I will have multiple slog files saved in multiple folders and sub-folders. Is there a way so that I can combine the binaries as for other text file formats?
Here is one that should work (I think). Just replace the path in the Download query
// SlogParser (file as binary) => let Source = Text.FromBinary(file), #"Split Text" = Text.Split(Source, "#(cr)#(lf)#(cr)#(lf)"), #"Removed Top Items" = List.Skip(#"Split Text",1), #"Converted to Table" = Table.FromList(#"Removed Top Items", Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Extracted Text After Delimiter" = Table.TransformColumns(#"Converted to Table", {{"Column1", each Text.AfterDelimiter(_, "$"), type text}}), #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Text After Delimiter", "Column1", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Column1.1", "Column1.2"}), #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}}), #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type", "Column1.2", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Column1.2.1", "Column1.2.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Column1.2.1", type date}}), #"Split Column by Delimiter2" = Table.SplitColumn(#"Changed Type1", "Column1.2.2", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Column1.2.2.1", "Column1.2.2.2"}), #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Column1.2.2.1", type time}, {"Column1.2.2.2", type text}}), #"Merged Date and Time" = Table.CombineColumns(#"Changed Type2", {"Column1.2.1", "Column1.2.2.1"}, (columns) => List.First(columns) & List.Last(columns), "Merged"), #"Changed Type3" = Table.TransformColumnTypes(#"Merged Date and Time",{{"Merged", type datetime}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type3",{{"Column1.1", "Session"}, {"Merged", "Timestamp"}}), #"Split Column by Position" = Table.SplitColumn(#"Renamed Columns", "Column1.2.2.2", Splitter.SplitTextByPositions({0, 1}, false), {"Column1.2.2.2.1", "Column1.2.2.2.2"}), #"Changed Type4" = Table.TransformColumnTypes(#"Split Column by Position",{{"Column1.2.2.2.1", type text}, {"Column1.2.2.2.2", type text}}), #"Renamed Columns1" = Table.RenameColumns(#"Changed Type4",{{"Column1.2.2.2.1", "OperationType"}}), #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns1",">","Input",Replacer.ReplaceText,{"OperationType"}), #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","<","Output",Replacer.ReplaceText,{"OperationType"}), #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1",".","Local",Replacer.ReplaceText,{"OperationType"}), #"Split Column by Delimiter3" = Table.SplitColumn(#"Replaced Value2", "Column1.2.2.2.2", Splitter.SplitTextByEachDelimiter({"#(cr)#(lf)"}, QuoteStyle.Csv, false), {"Column1.2.2.2.2.1", "Column1.2.2.2.2.2"}), #"Changed Type5" = Table.TransformColumnTypes(#"Split Column by Delimiter3",{{"Column1.2.2.2.2.1", type text}, {"Column1.2.2.2.2.2", type text}}), #"Renamed Columns2" = Table.RenameColumns(#"Changed Type5",{{"Column1.2.2.2.2.2", "Metadata"}}), #"Split Column by Delimiter4" = Table.SplitColumn(#"Renamed Columns2", "Column1.2.2.2.2.1", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Column1.2.2.2.2.1.1", "Column1.2.2.2.2.1.2"}), #"Renamed Columns3" = Table.RenameColumns(#"Split Column by Delimiter4",{{"Column1.2.2.2.2.1.1", "Operation"}, {"Column1.2.2.2.2.1.2", "Paramter"}}) in #"Renamed Columns3" // Downloads let Source = Folder.Files("C:\Users\artemus\Downloads"), #"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".slog")), #"Invoked Custom Function" = Table.AddColumn(#"Filtered Rows", "SlogTable", each SlogParser([Content])), #"Removed Other Columns" = Table.SelectColumns(#"Invoked Custom Function",{"SlogTable"}), #"Expanded SlogTable" = Table.ExpandTableColumn(#"Removed Other Columns", "SlogTable", {"Session", "Timestamp", "OperationType", "Operation", "Paramter", "Metadata"}, {"Session", "Timestamp", "OperationType", "Operation", "Paramter", "Metadata"}) in #"Expanded SlogTable"