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

[Fred_Elmendorf]

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 19th 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 for each file in the directory structure.

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

PowerShell

0 commentsNo commentsReport a concern

---

Add comment

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.

View Markdown

Use answers to provide solutions to the user's question.

Post your answer

 Activity

Following question

Theme

• Previous Versions
• Blog
• Contribute
• Privacy
• Terms of Use
• Code of Conduct
• Trademarks
• © Microsoft 2023

[#2 · Accepted solution]

@Fred_Elmendorf 

Hi, Fred.

First of all, let me re-state your output here as I've interpreted it, as with all that line-wrapping in the original post, it's hard to be sure I have interpreted correctly:

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

Let's assume I've interpreted the sample data correctly and move on.

First, since you have no header, that rules out Import-Csv. You can invest time in dynamically constructing a header but that's a waste of time and effort in this scenario.

Not using Import-Csv is fine for a second reason, which is you only care about the first line anyway, and Import-Csv doesn't allow you to read just one line - you have to read the whole file and then use client-side filtering to deal with just the first line, which is quite inefficient anyway.

So, what should we use instead?

Get-Content, as Get-Content addresses both requirements:

• Read content without a header;
• Read just a part of the file (in this scenario, just the first line).

Once we read the first line, we can split it up into its components and output the columns you're after.

Here's an example using your data sample:

Get-Content -Path "D:\Data\Temp\Forum\forum.csv" -TotalCount 1 |
    ForEach-Object {
        if (($Columns = $_.Split(",")).Count -ge 19)
        {
            [PSCustomObject] @{
                # Remember, arrays are zero-based, so the second column has an index of 1, not 2.
                Column1 = $Columns[1];
                Column18 = $Columns[18];
            }
        }
    }

Sample output:

Cheers,

Lain

[#3]

Thank you, Lain!

Your solution gets just what I need.