CODEWITHSUNDEEP
powershellwindows-server-2008
atricapilla
atricapilla

Reputation: 2630

Extracting columns from text file using PowerShell

I have to extract columns from a text file explained in this post:

Extracting columns from text file using Perl one-liner: similar to Unix cut

but I have to do this also in a Windows Server 2008 which does not have Perl installed. How could I do this using PowerShell? Any ideas or resources? I'm PowerShell noob...

Upvotes: 15

Views: 65996

Answers (4)

user3100105
user3100105

Reputation: 96

Try this. This will help to skip initial rows if you want, extract/iterate through columns, edit the column data and rebuild the record: 

$header3 = @("Field_1","Field_2","Field_3","Field_4","Field_5")     

Import-Csv $fileName -Header $header3 -Delimiter "`t" | select -skip 3 | Foreach-Object {

    $record = $indexName 
    foreach ($property in $_.PSObject.Properties){

        #doSomething $property.Name, $property.Value

            if($property.Name -like '*CUSIP*'){

                $record = $record + "," + '"' + $property.Value + '"' 
            }
            else{
                $record = $record + "," + $property.Value 
            }                           
    }               

        $array.add($record) | out-null  
        #write-host $record                         
}

Upvotes: 0

Keith Hill
Keith Hill

Reputation: 201602

Try this:

Get-Content test.txt | Foreach {($_ -split '\s+',4)[0..2]}

And if you want the data in those columns printed on the same line:

Get-Content test.txt | Foreach {"$(($_ -split '\s+',4)[0..2])"}

Note that this requires PowerShell 2.0 for the -split operator. Also, the ,4 tells the the split operator the maximum number of split strings you want but keep in mind the last string will always contain all extras concat'd.

For fixed width columns, here's one approach for column width equal to 7 ($w=7):

$res = Get-Content test.txt | Foreach {
           $i=0;$w=7;$c=0; `
           while($i+$w -lt $_.length -and $c++ -lt 2) {
               $_.Substring($i,$w);$i=$i+$w-1}}

$res will contain each column for all rows. To set the max columns change $c++ -lt 2 from 2 to something else. There is probably a more elegant solution but don't have time right now to ponder it. :-)

Upvotes: 20

hoge
hoge

Reputation: 4977

To ordinary、

type foo.bar | % { $_.Split(" ") | select -first 3 }

Upvotes: 1

JaredPar
JaredPar

Reputation: 754545

Assuming it's white space delimited this code should do.

$fileName = "someFilePath.txt"
$columnToGet = 2
$columns = gc $fileName | 
   %{ $_.Split(" ",[StringSplitOptions]"RemoveEmptyEntries")[$columnToGet] }

Upvotes: 7

Related Questions