Results 1 to 7 of 7

Thread: read an unstructured text file with commands and results

  1. #1
    Neophyte blueice_haller's Avatar
    Join Date
    Feb 2022
    Location
    Germany
    Posts
    3
    Articles
    0
    Excel Version
    365

    Question read an unstructured text file with commands and results



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi,
    I'm searching for an way how to read an unstructured text file.
    The result can be one or multiple lines which should read into one cell until the next command: #(cr)#(lf)command.
    The result can also be an error or no result which means the next command.
    The first headlines should be: status ; hostname
    The next headline of every column should be the commands: command 1 ; command 2 ; command 3
    A new data set start at: #(cr)#(lf)#(cr)#(lf)

    Advised by Ken in https://www.excelguru.ca/blog/2015/1...comment-362121 I tried to split the cells.
    But this does not change the result.

    Using Notepad++ enabling paragraph I see that every line ends with [CR][LF].

    Example:
    Code:
      
    [SUCCESS] Hostname1
    Command 1
    Result 1
    Command 2
    Result 2
    ..
    ..
    Command 3
    Result 3
      
    [ERROR] Hostname2
    Command 1
    Result 1
    Command 2
    Result 2
    ..
    ..
    Command 3
    Error

    This is how it should look like:

    Status Hostname Command 1 Command 2 Command 3
    [SUCCESS] Hostname1 Result 1 Result 2
    ..
    ..
    Result 3
    [ERROR] Hostname2 Result 1 Result 2
    ..
    ..
    Error










    This is how it looks like currently - even with Split Column by Delimiter:

    [SUCCESS] Hostname1
    Command 1
    Result 1
    Command 2
    Result 2
    ..
    ..
    Command 3
    Result 3
    [ERROR] Hostname2
    Command 1
    Result 1
    Command 2
    Result 2
    ..
    ..
    Command 3
    Error

































    Thank you in advance.

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,493
    Articles
    43
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Ah, apologies. I completely mis-read the original post. I was under the impression that the "before" picture was what you were trying to achive.

    Okay, so this is a bit tricky, mainly because of the need to merge multiple results into a single cell. I've attached a sample text file that I build based off your data (which I hopefully got correct.) You can download that first, then:

    • Create a new query in Power Query
    • Go to View -> Advanced Editor
    • Replace all the code in that window with this:

    Code:
    let    Source = Table.FromColumns({Lines.FromBinary(File.Contents("C:\Users\KenPuls\OneDrive - Excelguru Consulting Inc\Desktop\sample.txt"), null, null, 1252)}),
        #"Added Conditional Column" = Table.AddColumn(Source, "Custom", each if Text.StartsWith([Column1], "[") then [Column1] else null),
        #"Filled Down" = Table.FillDown(#"Added Conditional Column",{"Custom"}),
        #"Added Conditional Column1" = Table.AddColumn(#"Filled Down", "Custom.1", each if Text.StartsWith([Column1], "Command") then [Column1] else null),
        #"Filled Down1" = Table.FillDown(#"Added Conditional Column1",{"Custom.1"}),
        #"Added Conditional Column2" = Table.AddColumn(#"Filled Down1", "Remove", each if [Column1] = [Custom] then true else if [Column1] = [Custom.1] then true else false),
        #"Filtered Rows" = Table.SelectRows(#"Added Conditional Column2", each ([Remove] = false) and ([Column1] <> "")),
        #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Remove"}),
        #"Grouped Rows" = Table.Group(#"Removed Columns", {"Custom", "Custom.1"}, {{"Data", each _, type table [Column1=nullable text, Custom=text, Custom.1=text]}}),
        #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom.3", each Text.Combine([Data][Column1],",")),
        #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Data"}),
        #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[Custom.1]), "Custom.1", "Custom.3"),
        #"Replaced Value" = Table.ReplaceValue(#"Pivoted Column",",","#(cr)#(lf)",Replacer.ReplaceText,{"Command 1", "Command 2", "Command 3"}),
        #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value", "Custom", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Custom.1", "Custom.2"}),
        #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", type text}, {"Custom.2", type text}}),
        #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Custom.1", "Status"}, {"Custom.2", "Hostname"}})
    in
        #"Renamed Columns"
    • Update the file path in the first step to point to your file
    • Click Done


    At that point, you should be looking at the output you're after.

    What I would do then, is walk through each step of the query, starting with Source, to see what I did. Remember you can click the gear icon to see how I configured those specific steps as well.

    I hope this helps!
    Attached Files Attached Files
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book Master Your Data for Excel and Power BI, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Neophyte blueice_haller's Avatar
    Join Date
    Feb 2022
    Location
    Germany
    Posts
    3
    Articles
    0
    Excel Version
    365
    Thank you very much I believe I'm able to run your code:

    Click image for larger version. 

Name:	sample - Power Query Editor.png 
Views:	8 
Size:	73.8 KB 
ID:	10839

    Unfortunately with productive data it fails. So I need to adjust it.

    Here is some example productive data:
    Code:
     
    
    [SUCCESS] hostname1.local
    uptime
     14:52pm  up 78 days 21:52,  0 users,  load average: 0.00, 0.00, 0.00
    rpm -qa yast2
    yast2-3.2.51-4.10.2.x86_64
    rpm -qa mc
    mc-4.8.11-4.6.5.x86_64
    systemctl status ntpd
    ● ntpd.service - NTP Server Daemon
       Loaded: loaded (/usr/lib/systemd/system/ntpd.service; enabled; vendor preset: disabled)
      Drop-In: /run/systemd/generator/ntpd.service.d
               └─50-insserv.conf-$time.conf
       Active: active (running) since Mon 2021-11-29 17:03:18 CET; 2 months 20 days ago
         Docs: man:ntpd(1)
     Main PID: 6668 (ntpd)
        Tasks: 2 (limit: 65536)
       CGroup: /system.slice/ntpd.service
               ├─6668 /usr/sbin/ntpd -p /var/run/ntp/ntpd.pid -g -u ntp:ntp -c /etc/ntp.conf
               └─6669 ntpd: asynchronous dns resolver
    Nov 29 17:03:18 hostname1 ntpd[6668]: Listen normally on 2 lo 127.0.0.1:123
    Nov 29 17:03:18 hostname1 ntpd[6668]: Listen normally on 4 lo [::1]:123
    Nov 29 17:03:18 hostname1 ntpd[6668]: Listening on routing socket on fd #22 for interface updates
    Nov 29 17:03:18 hostname1 ntpd[6668]: kernel reports TIME_ERROR: 0x41: Clock Unsynchronized
    Nov 29 17:03:18 hostname1 ntpd[6668]: kernel reports TIME_ERROR: 0x41: Clock Unsynchronized
    Nov 29 17:03:18 hostname1 start-ntpd[6643]: Starting network time protocol daemon (NTPD)
    Nov 29 17:03:18 hostname1 systemd[1]: Started NTP Server Daemon.
    Nov 29 17:12:06 hostname1 ntpd[6668]: kernel reports TIME_ERROR: 0x41: Clock Unsynchronized
    df -ah /data
    Filesystem            Size  Used Avail Use% Mounted on
    /dev/mapper/vg01-data  10G  1.4G  8.7G  14% /data
    lsblk
    NAME        MAJ:MIN RM SIZE RO TYPE MOUNTPOINT
    sda           8:0    0  20G  0 disk 
    ├─sda1        8:1    0   2M  0 part 
    ├─sda2        8:2    0  20M  0 part /boot/efi
    └─sda3        8:3    0  20G  0 part /
    sdb           8:16   0  15G  0 disk 
    └─vg01-data 254:0    0  10G  0 lvm  /data
    mount | grep /data
    /dev/mapper/vg01-data on /data type xfs (rw,relatime,attr2,inode64,noquota)
    Attached Files Attached Files
    Last edited by p45cal; 2022-02-19 at 07:34 PM. Reason: took this post out of moderation

  4. #4
    Neophyte MelBre's Avatar
    Join Date
    Feb 2022
    Location
    Germany
    Posts
    2
    Articles
    0
    Excel Version
    Excel 365
    Quote Originally Posted by blueice_haller View Post

    Here is some example productive data:
    how should the result of your example look like?

  5. #5
    Neophyte blueice_haller's Avatar
    Join Date
    Feb 2022
    Location
    Germany
    Posts
    3
    Articles
    0
    Excel Version
    365
    The first line is normally empty and can be skipped.

    It can happen that 1 or any number of commands have been executed.
    It is not known how many commands are executed. In this case it were 7.
    This number of commands is the same for all Hostnames.
    Therefore in the first run it should determine the number of commands.


    The result should look like before.
    The first and second column is static and the others are dynamic.

    Status
    Hostname uptime rpm -qa yast2 rpm -qa mc systemctl status ntpd df -ah /data lsblk mount | grep /data
    [SUCCESS] hostname1.local 14:52pm up 78 days 21:52, 0 users, load average: 0.00, 0.00, 0.00 yast2-3.2.51-4.10.2.x86_64 mc-4.8.11-4.6.5.x86_64 ● ntpd.service - NTP Server Daemon
    Loaded: loaded (/usr/lib/systemd/system/ntpd.service; enabled; vendor preset: disabled)
    Drop-In: /run/systemd/generator/ntpd.service.d
    └─50-insserv.conf-$time.conf
    Active: active (running) since Mon 2021-11-29 17:03:18 CET; 2 months 20 days ago
    Docs: man:ntpd(1)
    Main PID: 6668 (ntpd)
    Tasks: 2 (limit: 65536)
    CGroup: /system.slice/ntpd.service
    ├─6668 /usr/sbin/ntpd -p /var/run/ntp/ntpd.pid -g -u ntp:ntp -c /etc/ntp.conf
    └─6669 ntpd: asynchronous dns resolver
    Nov 29 17:03:18 hostname1 ntpd[6668]: Listen normally on 2 lo 127.0.0.1:123
    Nov 29 17:03:18 hostname1 ntpd[6668]: Listen normally on 4 lo [::1]:123
    Nov 29 17:03:18 hostname1 ntpd[6668]: Listening on routing socket on fd #22 for interface updates
    Nov 29 17:03:18 hostname1 ntpd[6668]: kernel reports TIME_ERROR: 0x41: Clock Unsynchronized
    Nov 29 17:03:18 hostname1 ntpd[6668]: kernel reports TIME_ERROR: 0x41: Clock Unsynchronized
    Nov 29 17:03:18 hostname1 start-ntpd[6643]: Starting network time protocol daemon (NTPD)
    Nov 29 17:03:18 hostname1 systemd[1]: Started NTP Server Daemon.
    Nov 29 17:12:06 hostname1 ntpd[6668]: kernel reports TIME_ERROR: 0x41: Clock Unsynchronized
    Filesystem Size Used Avail Use% Mounted on
    /dev/mapper/vg01-data 10G 1.4G 8.7G 14% /data
    NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT
    sda 8:0 0 20G 0 disk
    ├─sda1 8:1 0 2M 0 part
    ├─sda2 8:2 0 20M 0 part /boot/efi
    └─sda3 8:3 0 20G 0 part /
    sdb 8:16 0 15G 0 disk
    └─vg01-data 254:0 0 10G 0 lvm /data
    /dev/mapper/vg01-data on /data type xfs (rw,relatime,attr2,inode64,noquota)

    hostname2.local







    hostnamen.local






    Last edited by blueice_haller; 2022-02-23 at 10:58 AM.

  6. #6
    Neophyte MelBre's Avatar
    Join Date
    Feb 2022
    Location
    Germany
    Posts
    2
    Articles
    0
    Excel Version
    Excel 365
    I could not recognize any characteristic for the command lines.
    Therefore I created a list of possible commands in the spreadsheet.

    The result is in the attached file.
    If there are multiple stati in the .txt file, the query may need to be adjusted.

    Does this help you?

    Melanie
    Attached Files Attached Files
    Last edited by MelBre; 2022-02-23 at 03:55 PM.

  7. #7
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,493
    Articles
    43
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Nice work Melanie.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book Master Your Data for Excel and Power BI, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •