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...
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.
So, I created 2 new blank queries and copy/pasted the different bits of text.
I am now getting the following error for the Downloads query.
Expression.Error: The import SlogParser matches no exports. Did you miss a module reference?
And the (file as binary) is asking me to enter a parameter. See image below.