How to use powershell to read the values in the 2nd and 18th columns of a csv file with no headers and write them to a csv file

Question

I have a text file with a couple hundred lines of data where the data elements are separated by a comma. The lines of data have varying numbers of elements in each line, but I am only interested in the 2nd and 18th data elements in the first line of the file, highlighted below, so I only want to read the first line of the file. The goal is to use powershell to read through a directory structure with hundreds of similar files and create an output csv file that contains only those two elements.

Here's a sample of the data:

Header=0,"Some Name",0,3,54,48,90,0,1,23,0,100,1,5,0.400000,250,1000,500,9600,10,10,10,960,1,2,30,14,100,1,5,0,0,2000,0,0,0,1,0,0

RmSetup=4310,1,1#2|0|3#4|3#4|3#4|3#4|3#4|5#6|0|0|3#4|0|3#4|3#4|8|0,10,100,0,0,1,70,1,1,0,0,5310,0,0

C1=AAAAAAEEXX,195.168.3.1,0,0,DSP

C2=AAAAAAEEXX,195.168.3.2,1,0,DSP

C3=AAAAAAEEXX,195.168.3.3,1,0,DSP

A1="A123 (Name2 CTG#2) - Ia",c,600.000000,100.000000,0.000000,Ia,-1,-1,-1,0.000000

A2="B123 (Name2 CC CTG#2) - Ib",c,600.000000,100.000000,0.000000,Ib,-1,-1,-1,0.000000

Here's an example of the desired output:

Site,Hz

Some Name,9600

Some Other Name,4800

Answer 1

Whether it's the 18th or 19th elements you're after, just adjust the values of $cnt1 and $cnt2 on the code below.

You can remove the array "$d" and replace the reference to "$d" with a Get-Content cmdlet to get the information from a file.

Be aware that there can be NO COMMAS in any of the quoted strings in the file.

$d = 'Header=0,"Some Name",0,3,54,48,90,0,1,23,0,100,1,5,0.400000,250,1000,500,9600,10,10,10,960,1,2,30,14,100,1,5,0,0,2000,0,0,0,1,0,0',
     'RmSetup=4310,1,1#2|0|3#4|3#4|3#4|3#4|3#4|5#6|0|0|3#4|0|3#4|3#4|8|0,10,100,0,0,1,70,1,1,0,0,5310,0,0',
     'C1=AAAAAAEEXX,195.168.3.1,0,0,DSP',
     'C2=AAAAAAEEXX,195.168.3.2,1,0,DSP',
     'C3=AAAAAAEEXX,195.168.3.3,1,0,DSP',
     'A1="A123 (Name2 CTG#2) - Ia",c,600.000000,100.000000,0.000000,Ia,-1,-1,-1,0.000000',
     'A2="B123 (Name2 CC CTG#2) - Ib",c,600.000000,100.000000,0.000000,Ib,-1,-1,-1,0.000000'


#positions
$cnt1 = 2       # 2nd
$cnt2 = 18      # 18th
#indexes
$index1 = $cnt1 - 1     # 2nd
$index2 = $cnt2 - 1     # 18th

$each = [ordered]@{}
$d |
    ForEach-Object{
        $a = $_ -split ','
        $each["Site"] = $null
        $each["Hz"] = $null
        if ($a.count -ge $cnt1){
            $each["Site"] += $a[$index1]
            if ($a.count -ge $cnt2){
                $each["Hz"] += $a[$index2]
            }
            [PSCustomObject]$each
        }
    } | Export-Csv c:\junk\pos.csv -NoTypeInformation

EDIT: Export to CSV. Use has insteas of array to provide headers for the CSV.

Answer 2

Thank you for your reply. By modifying your solution slightly to recurse the file structure I am able to get the desired output to the console, but I'm not successful in sending it to a .csv file. The code below produces this output:

"Name One",12345

"Name Two",12345

"Name Three",5432

"Name Four",1763

If you can show me how to get this output into a.csv file I will have the desired solution.

#positions
$cnt1 = 2       # 2nd
$cnt2 = 19      # 19th
#indexes
$index1 = $cnt1 - 1     # 2nd
$index2 = $cnt2 - 1     # 19th

Get-ChildItem -Path "Q:\DFR\APP" -Filter "R*.par" -Recurse -Depth 2 |
Get-Content -First 1 |
    ForEach-Object{
        [array]$i = @()
        $a = $_ -split ','
        if ($a.count -ge $cnt1){
            $i += $a[$index1]
        }
        if ($a.count -ge $cnt2){
            $i += $a[$index2]
        }
        if ($i[0] -eq 0 -OR $i[0]){
            $i -join ','
        }

    }

Answer 3

By reviewing other previous scripts I've worked with, I was able to send the appropriate output to a .csv file. The only remaining tweak that would improve the solution would be to suppress the output that is still going to the console. Here's the solution as I'm using it now.

#positions
$cnt1 = 2       # 2nd
$cnt2 = 19      # 19th
#indexes
$index1 = $cnt1 - 1     # 2nd
$index2 = $cnt2 - 1     # 19th

Get-ChildItem -Path "Q:\DFR\APP" -Filter "R*.par" -Recurse -Depth 2 |
Get-Content -First 1 |
    ForEach-Object{
        [array]$i = @()
        $a = $_ -split ','
        if ($a.count -ge $cnt1){
            $i += $a[$index1]
        }
        if ($a.count -ge $cnt2){
            $i += $a[$index2]
        }
        if ($i[0] -eq 0 -OR $i[0]){
            $i -join ','
        }

        [PSCustomObject] @{
                Site = $i[0]
                Hz = $i[1];
                
            } | Export-Csv -NoTypeInformation -Path "$csvlog" -Append

    }