36

I want to process a csv file in powershell, but I don't know what the column headings in the CSV file will be when it is processed.

For example:

$path = "d:\scratch\export.csv"
$csv = Import-csv -path $path

foreach($line in $csv)
{ 
    foreach ($head in $line | get-member | where-object {$_.MemberType -eq "NoteProperty"} | select Definition)
    {
        #pseudocode...
        doSomething($head.columnName, $head.value)
    }

}

How do I loop through the line in the csv file, getting the name of the column and the value? Or is there another way I should be doing this (like not using Import-csv)?

simon
  • 1,840
  • 2
  • 18
  • 34

3 Answers3

56
Import-Csv $path | Foreach-Object { 

    foreach ($property in $_.PSObject.Properties)
    {
        doSomething $property.Name, $property.Value
    } 

}
Shay Levy
  • 121,444
  • 32
  • 184
  • 206
  • This skipped the first line for me. I am not using headers in my csv and I only have one row with no commas ex: thing1thing2 ect.. In other words a simple list – Radmation Feb 17 '17 at 21:56
  • @Radmation Import-CSV $path -Header A is the change you need. This tells powershell to add the header "A" to the column and treat every row as a value, rather than thinking row 1 is a header row. – Monica Apologists Get Out Oct 08 '18 at 17:00
  • 1
    Important difference using `ForEach-Object` instead of a simple `ForEach` when working with CSV. – unNamed Nov 15 '19 at 08:04
  • 1
    @unNamed No, it does not matter at all. – stackprotector Jun 09 '20 at 17:26
18

A slightly other way of iterating through each column of each line of a CSV-file would be

$path = "d:\scratch\export.csv"
$csv = Import-Csv -path $path

foreach($line in $csv)
{ 
    $properties = $line | Get-Member -MemberType Properties
    for($i=0; $i -lt $properties.Count;$i++)
    {
        $column = $properties[$i]
        $columnvalue = $line | Select -ExpandProperty $column.Name

        # doSomething $column.Name $columnvalue 
        # doSomething $i $columnvalue 
    }
}

so you have the choice: you can use either $column.Name to get the name of the column, or $i to get the number of the column

user4531
  • 2,525
  • 7
  • 30
  • 38
  • Get-Member sorts the columns alphabetically. If the original order of the CSV columns is required, it will be lost. – Tony Jun 03 '21 at 07:17
2
$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 '*TextWrittenAsNumber*'){

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

        $array.add($record) | out-null  
        #write-host $record                         
}
user3100105
  • 76
  • 3