question

TB-1159 avatar image
0 Votes"
TB-1159 asked AndreasBaumgarten commented

read a text file and extract data and display data in columns to a .csv format using powershell/vbscript

Hi,

I have a text file with data, say a list of computer's names and its description, memory, cpu, os...etc. Right now it's in a text file with a single long column. I want to be able to extract by computer's name and display them into a new file (if possible) by each computer's name as the headers

For ex:

PC1 PC2 PC3
win10 win7 win8
cpu1 cpu2 cpu3

Any help will gladly be appreciated.

windows-server-powershell
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TB-1159 found another cool solution
you can use ISE and debugg (F9) Mode - it's nice to see the output

 $spalte = 0
    
 $File = Get-Content C:\temp\node.txt
    
 $excel = New-Object -ComObject Excel.Application
 $workbook = $excel.workbooks.add()
 $excel.visible = $true
    
 ForEach($line in $File){
 write-host $line -ForegroundColor Yellow
    
   If($line -match "node"){ # new Column
     $zeile=1
     $spalte++
     $excel.cells.item($zeile,$spalte) = $line
    
   }
   else{ # same column
     $zeile++
     $excel.cells.item($zeile,$spalte) = $line
   }
 }


0 Votes 0 ·
TB-1159 avatar image
0 Votes"
TB-1159 answered AndreasBaumgarten commented

Sorry, I forgot to mention that instead of PC1, PC2 and PC3, the header will be reading in as Node Names.

So, it will be something like this:

Node Name: PC1 Node Name: PC2 Node Name: PC3
Win10 Win7 Win8
cpu1 cpu2 cpu3

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@TB-1159 ,

this is the "input format" from the txt file?
Node Name: PC1 Node Name: PC2 Node Name: PC3
Win10 Win7 Win8
cpu1 cpu2 cpu3

Maybe you could post your script you have so far. This might be easier to help.


(If the reply was helpful please don't forget to upvote and/or accept as answer, thank you)

Regards
Andreas Baumgarten

0 Votes 0 ·
TB-1159 avatar image
0 Votes"
TB-1159 answered TB-1159 commented

Yes, that's the input from a text file.
Node Name: PC1
Win10
cpu1
Node Name: PC2
Win7
cpu2
Node Name: PC3
Win8
cpu3

So far, I only have very little...it's only reading from a text file.
Something like this: get-content c:\test\Debug\Node.log | select-string "Node Name/"

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

And you want the output like this:

PC1,PC2,PC3
Win10,Win7,Win8
cpu1,cpu2,cpu3


(If the reply was helpful please don't forget to upvote and/or accept as answer, thank you)

Regards
Andreas Baumgarten

0 Votes 0 ·
TB-1159 avatar image TB-1159 AndreasBaumgarten ·

Yes, please.

0 Votes 0 ·
AndreasBaumgarten avatar image
0 Votes"
AndreasBaumgarten answered AndreasBaumgarten edited

Hi @TB-1159 ,

maybe this helps to get started:

 $inputfile = "junk/junk.txt"
 $outputfile = "junk/junk.csv"
 $a = Get-Content -Path $inputfile
 $output = ""
 $f=for ($i=0;$i -lt $a.count;$i+=3) {$a[$i]}
 $output = ($f.Replace("Node Name: ","") -join",") + "`n" 
 $s=for ($i=1;$i -lt $a.count;$i+=3) {$a[$i]}
 $output += ($s -join",") + "`n" 
 $t=for ($i=2;$i -lt $a.count;$i+=3) {$a[$i]}
 $output += $t -join","
 $output | Out-File -Filepath $outputfile

Inputfile looks like this:

 Node Name: PC1
 Win10
 cpu1
 Node Name: PC2
 Win7
 cpu2
 Node Name: PC3
 Win8
 cpu3


Outputfile looks like this:

 PC1,PC2,PC3
 Win10,Win7,Win8
 cpu1,cpu2,cpu3


(If the reply was helpful please don't forget to upvote and/or accept as answer, thank you)

Regards
Andreas Baumgarten




5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TB-1159 avatar image
0 Votes"
TB-1159 answered AndreasBaumgarten commented

Are these lines: $f=for ($i=0;$i -lt $a.count;$i+=3
and $s=for ($i=1;$i -lt $a.count;$i+=3, only go up to 3? Can it be in a loop until there's no more Node Name left?

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @TB-1159 ,

The script is working with any number of NodeNames. You can add as much as you like.

But it is required that each of the "NodeName sets" contains 3 lines (Nodename, OS, CPU)
The count in the script is to find the next set of data in the txt file..

The NodeName is the first line and than every 3rd line (starting with 0 (first line in txt file) - 4th line - 7th line - ....)
The OS is the second line and than every 3rd line (starting with 1 (second line in txt file) - 5th line - 8th line - ....)
The CPU is the third line and than every 3rd line (starting with 2 (third line in txt file) - 6th line - 9th line - ....)

This will run through the full txt file until end. It doesn't matter how much Nodes are in the text file.


(If the reply was helpful please don't forget to upvote and/or accept as answer, thank you)

Regards
Andreas Baumgarten





0 Votes 0 ·
TB-1159 avatar image
0 Votes"
TB-1159 answered AndreasBaumgarten commented

I try your code and the output is showing one long row...

The results that I'd like to achieve is below with Node Name and the name of the PCs as my headers.:

Node Name: PC1 Node Name: PC2 Node Name: PC3
Win10 Win7 Win8
cpu1 cpu2 cpu3
........etc...there are more Node Names and other data...but this is just a sample data

As for my text file, it contains one long single column like this:
Node Name: PC1
Win10
cpu1
Node Name: PC2
Win7
cpu2
Node Name: PC3
Win8
cpu3

Hope that makes sense and helps.

BTW, thanks AndreasBaumgarten for your quick response :)

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

If I run the script here it looks like this:

Input file:

87918-image.png

Result file:

87962-image.png


(If the reply was helpful please don't forget to upvote and/or accept as answer, thank you)

Regards
Andreas Baumgarten


0 Votes 0 ·
image.png (60.9 KiB)
image.png (36.7 KiB)
TB-1159 avatar image
0 Votes"
TB-1159 answered

I see your results., thanks for showing that AndreasBaumgarten , very much appreciate it.

I do have another question, maybe this will clear things up.

How about reading all the items listed under that Node Name header, then when it reaches the next Node Name it will become a another column of it owns, reading all the items listed under the next Node Name as a header itself and the process repeats over and over.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AndreasBaumgarten avatar image
0 Votes"
AndreasBaumgarten answered

Hi @TB-1159 ,

I don't get the idea/approach. What will be the advantage of your approach? Faster? Smaller script?
But feel free to play around.


(If the reply was helpful please don't forget to upvote and/or accept as answer, thank you)

Regards
Andreas Baumgarten

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

IanXue-MSFT avatar image
0 Votes"
IanXue-MSFT answered

Hi,

You can try something like this. If there are other rows like memory, add them to $rows.

 $source = 'D:\temp\input.txt' 
 $result = 'D:\temp\output.txt'
 $rows = "name","os","cpu"
 $node = @()
 $rows| ForEach-Object{Set-Variable -Name $_ -Value $null}
 function writenode{
     for($i=0;$i -lt $node.Count;$i++){
         if($rows.count -le $i){
             write-host "Unknown Item $($node[$i]) in $($node[0])" 
         }
         else{
             if($i -eq 0){
                 $node[$i] = $node[$i] -replace 'Node Name: ',''
             }
             $var = Get-Variable -Name $rows[$i]
             $var.Value += "$($node[$i]) "
             Set-Variable -Name $rows[$i] -Value $var
         }
     }
 }
 Get-Content -Path $source | ForEach-Object{
     if($_ -match "Node Name" -and $node){
         writenode
         $node = @()
     }
     $node += $_
 }
 writenode
 $rows| ForEach-Object{
     Get-Variable -Name $_ | Select-Object -ExpandProperty value | Out-File -FilePath $result -Append
 }

Best Regards,
Ian Xue
============================================
If the Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TB-1159 avatar image
0 Votes"
TB-1159 answered

Thank you Ian Xue, I'll give it a try and will let you know.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TB-1159 avatar image
0 Votes"
TB-1159 answered AndreasBaumgarten edited

So, I tried and for some reason, it came out like one long row again....Isn't that weird?


My text file as one long column with Nodes, cpus, memories, etc....

I'd like to read the first Node Name and all the items belong in this Node Name, then when it reaches the next Node Name it will be my second column, and all the items belong in this Node Name the third and so on....Does that help?

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Are you referring to Ian Xue's script?


(If the reply was helpful please don't forget to upvote and/or accept as answer, thank you)

Regards
Andreas Baumgarten

0 Votes 0 ·
TB-1159 avatar image TB-1159 AndreasBaumgarten ·

Yes, I was.

I tried your script< again AndreasBaumgarten, and this is what it looks like.

First, my text file:
Node Name: PC1
cpu1
mem1
hw1
Node Name: PC2
cpu2
mem2
hw2
OS2
Node Name: PC3
cpu3
mem3
hw3
OS3
CD
Monitor

...my output file:
PC1,hw1,mem2,PC3,hw3,Monitor
cpu1,Node Name: PC2,hw2,cpu3,OS3
mem1,cpu2,OS2,mem3,CD

0 Votes 0 ·

Please post your script that you are using, It looks like the is a missing "newline" in your script.
And every data set must have the same amount of values (lines). Otherwise it won't work
It should look like this:

Node Name: PC1
cpu1
mem1
hw1
OS1
CD1
Monitor1
Node Name: PC2
cpu2
mem2
hw2
OS2
CD2
Monitor2
Node Name: PC3
cpu3
mem3
hw3
OS3
CD3
Monitor3


(If the reply was helpful please don't forget to upvote and/or accept as answer, thank you)

Regards
Andreas Baumgarten

0 Votes 0 ·