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

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

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