Apr 18 2021 06:36 PM
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,
Apr 18 2021 09:55 PM
Apr 19 2021 06:59 AM
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.
Apr 19 2021 12:53 PM
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.
Apr 21 2021 01:31 PM
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?
Apr 23 2021 05:00 PM
Apr 24 2021 02:19 AM
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
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.
Apr 26 2021 11:34 AM
Apr 26 2021 12:43 PM
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
to learn the basics, after that take our file open PQ and check step by step how it works. On ribbon click on
and in right pane double click on
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
After you are ready I make explain each step in details if any questions.
Apr 26 2021 12:53 PM
Apr 26 2021 01:17 PM
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.
Apr 26 2021 06:29 PM
Apr 26 2021 07:04 PM
Apr 26 2021 07:06 PM
Apr 27 2021 03:30 AM
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.