Extracting values from strings in an array using powershell

Question

I've got an issue trying to extract particular values from an Array. I have an array that contains 40010 rows each of which is a string of pipe separated values (64 on each line).

I need to extract values 7, 4, 22, 23, 24, 52 and 62 from each row and write it into a new array so that I will end up with a new array containing 40010 rows with only 7 pipe separated values in each (could be comma separated) row.

I've looked at split and can't seem to get my head around it to even get close to what I need.

I'd also be open to doing this from a file as I'm currently creating my 1st array with
$data = (Get-content $statement_file|Select-String "^01")

If I can add to that command to do the split on the input so I only have one array and don't need the intermediate array that would be even better.

I know if I was in Linux I could do the split with AWK quite easily but I'm fairly new to powershell so would appreciate any suggestions

I think I missed something but you could build the array without any input file if you know the values and the number of rows. — sodawillow
– sodawillow, Commented Feb 15, 2017 at 12:30

TechSpud · Accepted Answer · 2017-02-15 13:56:58Z

4

# create an array of header columns (assuming your pipe separated file doesn't have headers)
$header = 1..64 | ForEach-Object { "h$_" }

# import the file as 'csv' but with pipes as separators, use the above header, then select  columns 7,4,22,23,24,52,62
# edit 1: then only return rows that start with 01
# edit 2: then join these into a pipe separated string
$smallerArray = $statement_file |
    Import-Csv -Delimiter '|' -Header $header |
    Where-Object { $_.h1.StartsWith('01') } |
    Select-Object @{Name="piped"; Expression={ @($_.h7,$_.h4,$_.h22,$_.h23,$_.h24,$_.h52,$_.h62) -join '|' }} |
    Select-Object -ExpandProperty piped

edited Feb 15, 2017 at 13:56

answered Feb 15, 2017 at 12:33

TechSpud

3,5581 gold badge30 silver badges35 bronze badges

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

6 Comments

dazedandconfused Over a year ago

That's close to what I want but there are over 600,000 records in the file and I only need the 40,010 that start with 01 in the first field. I'm currently doing this, <br>

$data1 = (Get-content $statement_file|Select-String "^01")  $data1 | ac $tmp_file  $data2 = $tmp_file |      Import-Csv -Delimiter '|' -Header $header |     Select h7,h4,h22,h23,h24,h52,h62

dazedandconfused Over a year ago

The other issue is When I do $data1[3] I get "01|456000003|00000012|N|WEBB|Webb Ivory Acount|003|Webb Ivory|OPEN|123000072|Mr|Ioan|LastName|12 Road Name|District Name Here|Town Name Here|County Name Here|AA11 1AA|N|20161028|20161120|250.00|250.00|250.00|200.00|0.00|200.00|0.00|0.00|00.0|0.00|10.00|5.00|N|Y|20161120|5.00|||0.00|N|N|0.00|A|1|N||||||||||" yet when I do $data2[3] I get "h7 : 003 h4 : N h22 : 250.00 h23 : 250.00 h24 : 250.00 h52 : h62 :"

dazedandconfused Over a year ago

What I need for output is `003|N|250.00|250.00|250.00||| using the above data (comma separated would be OK as well)

TechSpud Over a year ago

See udpated code, which should give you what you need.

dazedandconfused Over a year ago

Brilliant. That's exactly what I needed. Much appreciated.

|

Collectives™ on Stack Overflow

Extracting values from strings in an array using powershell

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related