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 ·
Chris-1748 avatar image
0 Votes"
Chris-1748 answered AndreasBaumgarten commented

Andreas,
is it possible to do this with an Object?
sorry I cant find any characters on my mac os keyboard :-)

 $x = get-content c:\temp\test.txt
 foreach($y in $x)
    
 if($y -match "note")
    
 new-object

· 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.

Getting the "node lines" ist not a big deal. But the lines between this "node lines" vary in numbers (something between a few and 200+) ... this isn't that easy.

For that reason I stopped looking for a solution. But maybe TB-1159 will find a solution.


(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 ·

Hi AndreasBaumgarten - if I have another similar question, would you be able to look into it?

0 Votes 0 ·

Hi @TB-1159 ,

Of course you can ask another question. But I would recommend creating a new question. This post already has 14 replies and various comments. That might get a bit confusing here.


(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 edited

Per dataset you mean the number of items under each node name? If that's the case, the answer is no. I'd like to script to read until it reaches the next node name and start a new column and read it again until the next node name...repeat until end of the file. For each node name, there could be 200+ items under each node, some could be less for another node, it's not always the same number of items for each node.

· 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.

@TB-1159 ,

yes, I mean one dataset = line including the node name and all lines until next node name.
If there are nodes with 200+ lines until the next node you will get a csv file with 200+ rows. At least that is my understanding how the csv file will look like.

At the moment I don't have an idea how to get his done.


(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 ·

Thank you AndrewsBaugarten for your quick reply and honest answer.

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

Hi @TB-1159 ,

if you are using a "dataset" with 7 lines in your txt file you need to extend the PowerShell script I posted before. The first script is working with 3 lines per data set.
$f = first line of dataset
$s = second line of dataset
$t = third line of dataset

If you have a "7 line dataset" it should look like this:

 $inputfile = "junk/junk.txt"
 $outputfile = "junk/junk.csv"
 $a = Get-Content -Path $inputfile
 $output = ""
 $l1=for ($i=0;$i -lt $a.count;$i+=7) {$a[$i]}
 $output = ($l1.Replace("Node Name: ","") -join",")  + "`n"
 $l2=for ($i=1;$i -lt $a.count;$i+=7) {$a[$i]}
 $output += ($l2 -join",") + "`n"
 $l3=for ($i=2;$i -lt $a.count;$i+=7) {$a[$i]}
 $output += ($l3 -join",") + "`n"
 $l4=for ($i=3;$i -lt $a.count;$i+=7) {$a[$i]}
 $output += ($l4 -join",") + "`n"
 $l5=for ($i=4;$i -lt $a.count;$i+=7) {$a[$i]}
 $output += ($l5 -join",") + "`n"
 $l6=for ($i=5;$i -lt $a.count;$i+=7) {$a[$i]}
 $output += ($l6 -join",") + "`n"
 $l7=for ($i=6;$i -lt $a.count;$i+=7) {$a[$i]}
 $output += $l7 -join","
 $output | Out-File -Filepath $outputfile

(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

$inputfile = "c:\test\dummy2.txt"
$outputfile = "c:\test\dummy3.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

Please let me know if I missed anything? It could be me, human, and I'm error prone :)

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 ·
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.

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.

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.

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.

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)