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

TB 21

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.

Chris 656

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
  }
}

14 answers

TB 21 Reputation points

2021-04-14T17:27:36.61+00:00

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
Please sign in to rate this answer.
Andreas Baumgarten 123.4K Reputation points MVP Volunteer Moderator

2021-04-14T18:51:19.513+00:00

@TB ,

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
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
TB 21 Reputation points

2021-04-14T19:21:55.57+00:00

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/"
Please sign in to rate this answer.
Andreas Baumgarten 123.4K Reputation points MVP Volunteer Moderator

2021-04-14T19:58:38.467+00:00

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

TB 21 Reputation points

2021-04-14T20:08:15.663+00:00

Yes, please.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Andreas Baumgarten 123.4K MVP Volunteer Moderator

Hi @TB ,

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

TB 21 Reputation points

2021-04-14T20:35:15.067+00:00

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?
Please sign in to rate this answer.
Andreas Baumgarten 123.4K Reputation points MVP Volunteer Moderator

2021-04-14T21:04:43.953+00:00

Hi @TB ,

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
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
TB 21 Reputation points

2021-04-14T21:02:21.303+00:00

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 :)
Please sign in to rate this answer.
Andreas Baumgarten 123.4K Reputation points MVP Volunteer Moderator

2021-04-14T21:20:00.36+00:00

If I run the script here it looks like this:

Input file:

Result file:

----------

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

Regards
Andreas Baumgarten
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

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

14 answers

Your answer