cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

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?

 

File link

 

Thank you

J

3 ACCEPTED SOLUTIONS

Accepted Solutions
artemus Regular Visitor
Regular Visitor

Re: open file as text

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"
artemus Regular Visitor
Regular Visitor

Re: open file as text

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"
Highlighted
artemus Regular Visitor
Regular Visitor

Re: open file as text

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 8
artemus Regular Visitor
Regular Visitor

Re: open file as text

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"

Re: open file as text

Thank you @artemus.

Works a treat! Smiley Happy

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?

artemus Regular Visitor
Regular Visitor

Re: open file as text

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"

Re: open file as text

@artemus 

I am getting an error of 

 

"Expression.SyntaxError: Token Eof expected."

 

 

artemus Regular Visitor
Regular Visitor

Re: open file as text

This is 2 queries. The // line is the name of the query

Re: open file as text

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.

 

Image 1169.png

 

Highlighted
artemus Regular Visitor
Regular Visitor

Re: open file as text

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.

Re: open file as text

Thank you very much @artemus.

It works as a charm.Smiley Happy