Argthtjtr

How does one access data imported from a CSV file by using dynamic note property names? That is, one doesn't know the colunm names beforehand. They do match a pattern and are extracted from the CSV file when the script runs.

As for an example, consider a CSV file:

"Header 1","Header A","Header 3","Header B"

0,0,0,0

1,2,3,4

5,6,7,8

I'd like to extract only columns that end with a letter. To do this, I read the header row and extract names with a regex like so,

$reader = new-object IO.StreamReader("C:tmpdata.csv")

$line = $reader.ReadLine()

$headers = @()



$line.Split(",") | % {

    $m = [regex]::match($_, '("Header [A-Z]")')

    if($m.Success) { $headers += $m.value } }

This will get all the column names I care about:

"Header A"

"Header B"

Now, to access a CSV file I import it like so,

$csvData = import-csv "C:tmpdata.csv"

Import-CSV will create a custom object that has properties as per the header row. One can access the fields by NoteProperty names like so,

$csvData | % { $_."Header A" } # Works fine

This obviously requires one to know the column name in advance. I'd like to use colunn names I extracted and stored into the $headers. How would I do that?

Some things I've tried so far

$csvData | % { $_.$headers[0] } # Error: Cannot index into a null array.

$csvData | % { $np = $headers[0]; $_.$np } # Doesn't print anything.

$csvData | % { $_.$($headers[0]) } # Doesn't print anything.

I could change the script like so it will write another a script that does know the column names. Is that my only solution?

the first thing ( and the only one... sorry) that came in my mind is:

$csvData | % { $_.$(( $csvData | gm | ? { $_.membertype -eq "noteproperty"} )[0].name) }

for get the first's column values and

$csvData | % { $_.$(( $csvData | gm | ? { $_.membertype -eq "noteproperty"} )[1].name) }

for second column and so on....

is this what you need?

I think you want this:

[string]$headers = $csvdata | gm -MemberType "noteproperty" | 

                        ?{ $_.Name -match "Header [a-zA-Z]$"} | 

                        select -expand Name

$csvdata | select $headers

Choose the headers that match the condition (in this case, ones ending with characters) and then get the csv data for those headers.

you can use custom script to parse csv manually:

$content = Get-Content "C:tmpdata.csv"

$header = $content | Select -first 1

$columns = $header.Split(",")

$indexes = @()

for($i; $i -lt $columns.Count;$i++)

{

    # to verify whether string end with letter matches this regex: "[A-Za-z]$"

    if ($column[$i] -match "[A-Za-z]$")

    {

        $indexes += $i

    }

}



$outputFile = "C:tmpoutdata.csv"

Remove-Item $outputFile -ea 0

foreach ($line in $content)

{

    $output = ""

    $rowcol = $line.Split(",")

    [string]::Join(",", ($indexes | foreach { $rowcol[$_]  })) | Add-Content $outputFile

}