Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
jagostinhoCT
Post Partisan
Post Partisan

open file as text

‎05-03-2019 01:45 AM

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

Labels:
Message 1 of 9
4,044 Views
0
3 ACCEPTED SOLUTIONS
artemus
Employee
Employee

‎05-03-2019 07:27 AM

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"

View solution in original post

Message 2 of 9
4,022 Views
artemus
Employee
Employee
In response to jagostinhoCT

‎05-03-2019 09:05 AM

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"

View solution in original post

Message 4 of 9
4,004 Views
0
artemus
Employee
Employee
In response to jagostinhoCT

‎05-07-2019 03:53 AM

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.

View solution in original post

Message 8 of 9
3,885 Views
8 REPLIES 8
artemus
Employee
Employee

‎05-03-2019 07:27 AM

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"
Message 2 of 9
4,023 Views
jagostinhoCT
Post Partisan
Post Partisan
In response to artemus

‎05-03-2019 07:51 AM

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?

Message 3 of 9
4,019 Views
0
artemus
Employee
Employee
In response to jagostinhoCT

‎05-03-2019 09:05 AM

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"
Message 4 of 9
4,005 Views
0
jagostinhoCT
Post Partisan
Post Partisan
In response to artemus

‎05-03-2019 09:29 AM

@artemus 

I am getting an error of 

 

"Expression.SyntaxError: Token Eof expected."

 

 

Message 5 of 9
4,000 Views
0
artemus
Employee
Employee
In response to jagostinhoCT

‎05-03-2019 11:03 AM

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

Message 6 of 9
3,989 Views
0
jagostinhoCT
Post Partisan
Post Partisan
In response to artemus

‎05-07-2019 03:36 AM

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

 

Message 7 of 9
3,892 Views
0
artemus
Employee
Employee
In response to jagostinhoCT

‎05-07-2019 03:53 AM

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.

Message 8 of 9
3,886 Views
jagostinhoCT
Post Partisan
Post Partisan
In response to artemus

‎05-07-2019 03:59 AM

Thank you very much @artemus.

It works as a charm.Smiley Happy

Message 9 of 9
3,879 Views
0

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

View All
Top Solution Authors
View All
Top Kudoed Authors
View All