Using powershell to collect certain columns of data

Question

I am having some problems getting windows powershell to do what I want it to do. Essentially, I have 24 subfolders in my current folder, named Angle1, Angle2, etc. up to Angle 24.

In each of these subfolders there is a file called output.txt. In output.txt, there are 10 columns of data, separated by a blank space. I would like to get the 9th column of each output.txt file in each subfolder, and put them all in a new file in parent folder called total.txt. So, ultimately, I would have 24 columns of data, each separated by a space. The first column of total.txt would correspond to the 9th column of output.txt in Angle1, etc.

Note that the Angle folders have other subfolders which I want to ignore.

Despite many attempts, I have not been able to get this to work. I am not sure if I need to use the -Recurse option to do this, or if Powershell is even capable of doing this. IS there anyone here who is an expert at a powershell, that could say whether this is possible or not?

A small except from one output.txt file:

1348 2 26.0785 3.18115 20.5328 -0.79613 3.18115 2.51545 4.13292 0.278888 
1348 2 26.8091 2.07125 19.4069 -0.0655152 2.07125 1.38956 2.49505 0.254024 
1348 2 24.162 3.90054 20.4261 -2.71258 3.90054 2.40877 5.32677 0.0208912 
1348 2 24.1527 3.90493 20.4233 -2.72189 3.90493 2.40595 5.33346 0.00646916 
1348 2 24.1436 3.88005 20.4429 -2.73095 3.88005 2.42555 5.32881 0.0127918 
1348 2 24.1087 3.87723 20.4519 -2.76586 3.87723 2.43455 5.34882 0.0198102 
1348 2 24.2572 3.83432 20.4136 -2.61737 3.83432 2.39624 5.22442 0.0243901 
1348 2 24.1609 3.7174 19.8739 -2.71375 3.7174 1.85659 4.9629 0.0838124

The code that I was using was in Linux, which worked to some degree:

awk '{print $9}' Angle1/output.txt >>tmp
for ((i=2;i<25;i++))
do
awk '{print $9}' Angle${i}/output.txt |paste -d " " tmp - >>total.txt
mv total.txt tmp
done
mv tmp total.txt

However, now I need to run in Powershell, not linux. At this time I don't really have any code that remotely works for this. Basically a conglomeration of Get-Child commands etc. but nothing that came close to what I wanted.

Can you post an example of output.txt and the code that you have tried so far? This is certainly possible using powershell. — Samuel Prout
– Samuel Prout, Commented May 18, 2015 at 16:36

user4003407 · Accepted Answer · 2015-05-18 17:29:11Z

2

Assuming that all files have same number of rows:

$Results=1..24|ForEach-Object {
    ,@(Get-Content Angle$_\output.txt|ForEach-Object {(-split$_)[8]})
}
&{
    for($i=0;$i-lt$Results[0].Length;++$i){
        @($Results|ForEach-Object {$_[$i]})-join' '
    }
}|Set-Content total.txt

answered May 18, 2015 at 17:29

user4003407

22.3k4 gold badges54 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

beehaus · Accepted Answer · 2015-05-18 17:19:39Z

0

Try this

            $result = @()
    $Anglecount = 0

    do {
    $Anglecount ++
        $content = get-content (".\Angle"+$i+"\output.txt")
        foreach ($line in $content) { 

            $trimmed = $line.Trim()
            $split = $line.Split(" ")
            $result += $split[8]

        }
        $Anglecount
    } until ($Anglecount -eq 24)

    $result | Out-File total.txt

Not the most elegant solution but will do the trick for you

edited May 18, 2015 at 17:19

answered May 18, 2015 at 16:53

beehaus

4151 gold badge5 silver badges13 bronze badges

14 Comments

Jack Over a year ago

when I save this as a .ps1 and run, it creates total.txt but it is blank

beehaus Over a year ago

Yes Open up the Powershell ISE and paste the code into there for testing. Then save it as a ps1 file

Jack Over a year ago

sorry, i edited comment. I save as ps1 and ran it. It seemed like it looped through directories, but total.txt was empty

Jack Over a year ago

It says that it is 15kb, but when I open the file, I dont see anything? Strange

Jack Over a year ago

Is it getting the 9th column of data?

|

Jeter-work · Accepted Answer · 2015-05-18 17:53:30Z

If this were comma separated input, this would be dirt simple. So I created a short script to convert the data to CSV, save it that way, then I show a short example how to use it.

function Get-ColumnData { param ([string]$folder)

$fileInput = Get-Content -Path "$folder\output.txt"

# initialize an array
$fileOutput = @()

# put title headers for later import as CSV
# I chose arbitrary names, they can be almost anything, but avoid spaces and special characters
$fileOutput += "Column1,Column2,Column3,Column4,Column6,Column7,Column8,Column9,ColumnA"

foreach ($line in $fileinput) {
    # here's the meat, trim whitespace off the end and replace spaces with commas
    $line = $line.TrimEnd() -replace ' ',','

    # add the results to the array
    $fileoutput += $line
    }
# write to a csv
# there's proabably another way to go from an array of strings to an array of objects, 
#    but saving as a CSV, then importing the CSV is dirt simple
$fileOutput | out-file 'C:\temp1\converted.csv'
$Objects = import-csv 'C:\temp1\converted.csv'

# Select the next to last column
$Objects | Select -ExpandProperty Column9 
}

    $location = "c:\temp1" #set to whatever local or remote path
    $folders = Get-ChildItem $location -Directory
    foreach ($folder in $folders) {
        if (Test-Path $folder\output.txt) {
            $folderColumn = Get-ColumnData $folder
            }
            # at this point you have a column of data but it's stored as a collection of strings
            # need to do something here to add it to your data file and I'm stuck there
        }

You would need to find a way from here to get just one column from $Objects and have that be a column for your final answer.

Collectives™ on Stack Overflow

Using powershell to collect certain columns of data

3 Answers 3

Comments

14 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

14 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related