Import .log Files from a single Folder Into a Single Column (do not split by deliminator)

BourbonME

New Member
Joined
Aug 18, 2020
Messages
2
Office Version
  1. 2013
Platform
  1. Windows
Hello all!
New to Power BI and the community and excited to get started! That said, first project = first problem... :)

Task:
To import 6 months of daily .log files (~180 files)

Issue:
.log file is non standard and each row uses multiple delimiters (4 different per row).
I can successfully parse the .log files as long as the data is loaded completely and Power Query does not take any liberty with interpreting the file type.

First Attempt (which works perfectly on a single file)
This works great when I import a single file to work out my parsing logic.

Power Query:
let
    fnRawFileContents = (fullpath as text) as table =>
let
    Value = Table.FromList(Lines.FromBinary(File.Contents(fullpath)),Splitter.SplitByNothing())

in Value,

// LOAD FILE
    Source = fnRawFileContents("\\path\FileName.LOG"),

// A BUNCH OF PARSING AND FILTERING
    #"EndProduct"
in
    #"EndProduct"


Second Attempt (at folder level):
It is not obvious to me how to modify this code that is generated when using the folder import.

Power Query:
let

// SOURCE
    Source = Folder.Files("\\FolderPath"),

// SORT TO ENSURE I HAVE MOST RECENT ON TOP
    #"Sorted Rows" = Table.Sort(Source,{{"Date created", Order.Descending}}),

// DAYS TO KEEP
    #"Kept First Rows" = Table.FirstN(#"Sorted Rows",10), /*will bump to 180 when in PRD*/

// FILTER BY FILE METADATA
    #"Filtered Hidden Files1" = Table.SelectRows(#"Kept First Rows", each [Attributes]?[Hidden]? <> true),

// ADD COLUMN NAME WITH NAME OF SOURCE FILE
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
    #"Removed Other Columns" = Table.SelectColumns(#"Invoke Custom Function1",{"Transform File", "Name"}),

// EXPAND TABLE DATA
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),

// REMOVE COMMENTED ROWS
    #"Filtered Rows" = Table.SelectRows(#"Expanded Table Column1", each not Text.StartsWith([Column1], "#")),

// A BUNCH OF PARSING AND FILTERING
    #"EndProduct"

in
    #"EndProduct"


END RESULT
Oversimplifying, I would like to be able to apply the 'SplitByNothing' logic to all of the .log files when using the Folder Import method. I just want to explicitly load all data in the files into one column so I have full control over how I'm parsing without any data loss/truncation.

Any thoughts or guidance is appreciated.

Thanks!
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

BourbonME

New Member
Joined
Aug 18, 2020
Messages
2
Office Version
  1. 2013
Platform
  1. Windows
I thought of a hack on this one (emphasis on HACK). I imported the log files as a fixed width file with one massive column. It works but it is anything but elegant.

I know there is a better way! So if someone thinks of something better please let me know.

Thanks!!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,145
Messages
5,623,003
Members
415,945
Latest member
Koboca

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top