• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

log files to reporting ?

1shivamkushwaha

New Member
Hi,
on daily basis I am getting this kind of log file(PFA).
Using this file I have to create reports. I am really not sure how to get this file into excel in proper format.
I am attaching the log file and the output sample. (currently I am using excel and power query to get my work done).

samplefile - log file
screenshot 1- output I wanted

step by step process will be much appreciated

Thanks in advance.
 

Attachments

p45cal

Well-Known Member
Your samplefile.txt seems to be JSON formatted file, except that it's missing an opening curly bracket ({)as the first character in the file and a closing curly bracket (}) as the very last character in the file. See attached samplefile.txt version where I've added these.
Then the following PQ code (M Code) will give you more or less what you want:
Code:
let
    Source = Json.Document(File.Contents("C:\Users\Public\Documents\samplefile.txt")),
    ConvertedtoTable = Record.ToTable(Source),
    ExpandedValue = Table.ExpandListColumn(ConvertedtoTable, "Value"),
    ExpandedValue1 = Table.ExpandRecordColumn(ExpandedValue, "Value", {"fileName", "inputRowCount", "rowSuccessCount", "rowSkipCount", "failure", "reportCreatedDate"}),
    ChangedType = Table.TransformColumnTypes(ExpandedValue1,{{"reportCreatedDate", type date}, {"inputRowCount", Int64.Type}, {"rowSuccessCount", Int64.Type}, {"rowSkipCount", Int64.Type}, {"failure", Int64.Type}, {"fileName", type text}})
in
    ChangedType
Of course, you'll have edit the first line of the code to point to your (adjusted) file.

76838
 

Attachments

Chihiro

Excel Ninja
Are you sure that your sample is in exact format that your original is in?

It's very similar in structure to JSON, but does not conform to JSON standard.

As @p45cal mentioned, it's missing opening and closing curly brackets. If your file is actually missing curly brackets. Here's what you can do without altering source file.

Instead of importing data as JSON. Import as text from binary. Then perform text operation to add curly brackets and remove unnecessary character(s). Note: I did not convert types.

Ex:
Code:
let
    Source = "{" & Text.Trim(Text.FromBinary(File.Contents("C:\Users\Shared\Downloads\samplefile.txt"))) & "}",
    convert = Json.Document(Source),
    #"Converted to Table" = Record.ToTable(convert),
    #"Expanded Value" = Table.ExpandListColumn(#"Converted to Table", "Value"),
    #"Expanded Value1" = Table.ExpandRecordColumn(#"Expanded Value", "Value", {"fileName", "inputRowCount", "rowSuccessCount", "rowSkipCount", "failure", "reportCreatedDate"}, {"fileName", "inputRowCount", "rowSuccessCount", "rowSkipCount", "failure", "reportCreatedDate"})
in
    #"Expanded Value1"
 
Top