Read a list of Node Name and its software name, then find the next Node Name

Copper Contributor

Hi,

 

Newbie here....I have a question.  What's the best way to read from a txt file that contains a list of Node Name and its Software Name, set it in a column1.....then reads the next Node Name and display in the second column2, etc...

 

Expected Output:

Node Name: C1                          Node Name: C2                               Node Name: C3

sw1                                             swname                                           swname

swname                                      swname                                           swname

etc....

Each Node Name column can have as many as between 20 to 300 sw under them.

 

Can someone help?  The text file is has a single column consists of Node Name follow by software name.

 

Thanks in advance,                         

14 Replies
Excel has built in command DATA then Get External data,, allows to import data from Text file also, where every record must be in a Row, and either separated by Space or Comma.

@Rajesh_Sinha 

 

I know that feature, but since my dataset is on a one long column without any separated commas, how can I set it into cols and rows per Node Name is the question.

@TB_VN 

How do we know that the text in one row is node name and in another one is software name? Perhaps you may share small sample file to illustrate.

@Sergei Baklan 

Text file contains:

NodeName: Server1

sw

sw

sw

sw

sw

etc..

NodeName: Server2

sw

sw

sw

sw

sw

sw

sw

sw

sw

sw

etc...

NodeName: Server3

sw

sw

sw

sw

sw

sw

etc...

 

Does this help?

 

The text will say "Node Name: Server1"...etc, same goes for Software Name: nameofsw in front it. These are probably the keywords you're after?

@TB_VN 

Yes, that's more clear now, thank you.

I'd use Power Query for such transformation. Source could be CSV file as in your tag, in my sample that's the column named "range". In brief: query it, create another column with node names against each record, group by nodes without aggregation creating list of software for each node, create table from these lists.

It looks like

image.png

and M-script is

let
    Source = Excel.CurrentWorkbook(){[Name="range"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each ([Column1] <> null)),
    #"Added Custom" = Table.AddColumn(
        #"Filtered Rows",
        "Nodes",
        each
            if Text.Start([Column1],9)="NodeName:"
            then [Column1]
            else null
    ),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Nodes"}),
    #"Filtered Rows1" = Table.SelectRows(#"Filled Down", each not Text.Contains([Column1], "NodeName:")),
    #"Replaced Value" = Table.ReplaceValue(#"Filtered Rows1","NodeName:","",Replacer.ReplaceText,{"Nodes"}),
    #"Trimmed Text" = Table.TransformColumns(#"Replaced Value",{{"Nodes", Text.Trim, type text}}),
    #"Grouped Rows" = Table.Group(#"Trimmed Text", {"Nodes"}, {{"SW", each _[Column1]}}),
    #"Create Table" = Table.FromColumns( #"Grouped Rows"[SW],#"Grouped Rows"[Nodes])
in
    #"Create Table"

Please check in attached file.

Hi Sergei Baklan, thank you for that script and the output.

I have never used Power Query before and I'm a bit lost at the moment. Could you kindly explain or show me how to do step by step on this?

Sorry for the inconvenience sir, I hope you understand.

@TB_VN 

Okay, you need to invest some time first, with Power Query it's not like take some formula, adjust the range and use it. Patterns exist, but first you shall to understand how PQ works.

I'd suggest to find in File->New

image.png

to learn the basics, after that take our file open PQ and check step by step how it works. On ribbon click on

image.png

and in right pane double click on

image.png

Power Query editor will be opened, some time takes to refresh the data, and here in right pane you may check step by step which transformations are applied

image.png

After you are ready I make explain each step in details if any questions.

I did some google search about PQ and somehow understood some of the pics you posted it there. I will go thru the rest today.

Thank you sir

@TB_VN 

Great. In attached file open the query in advanced editor, I added comments to each step. 

Alternatively you may right click on query name in right pane, Copy, open Notepad++ or Notepad and Paste. Text of M-script will be here. 

I got down to this line:
= Table.Group(#"Trimmed Text", {"Nodes"}, {{"SW", each _[Column1]}}) and it says "Trimmed Text" wasn't recognized. Make sure it's spelled correctly.

What does it trim? Does it remove the extra for text from Nodes and sw?
Also, what if the NodeName is Node Name, a space in between, does it cause any kind of errors if it looks for Node Name and not NodeName? When I try your sample, I changed from NodeName: to Node Name: it seems to stops working, even when I changed your script to have a space in between Node Name? It returns Null, or maybe I need to do a Filled Down all the way?

Another question, this line: if Text.Start([Column1],9)="NodeName:", what if my line is not all the same, will it know when the next Node Name to stop and start a new column header?

Sorry sir if I asked too many dumb questions and making you repeating stuff again, but thanks for being very patience with me :) as I'm picking up this and learn how to use this PQ tool.

I hope you don't mind me asking all these questions sir.
Nevermind about the Trimmed Text, I went back and saw what you did to it. It deleted the extra space in front on the Server1.
:)....still learning

@TB_VN 

Node Name, NodeName, nodename are all different. You may replace one by one all possible variants. Alternatively, if you always have a colon (:) before server name, you may split this column on two using ":" as delimiter. After that remove first of these two columns.