Powershell: get column number from CSV file header

Question

I need to get the column number from an imported CSV based on a particular column name ($_."Status"). Once I have the correct column number, I can assign it to a variable and use it in a foreach loop to write text to the corresponding cells. $wsSource.cells.item($tr,49) = "Added by xyz)" Note that the column position often varies from file to file.

I already have the index/row number via $tr = $source.IndexOf($row) ...but struggling with the col number.

Thanks in advance, Jason

Incomplete code from much larger PS script that writes two different excel files in the one loop:

$source = Import-Csv $csvFile
        $i = 2
        foreach($row in $source.where{$_.Contacted -like "*Invalid"})
        {
        $tr = ($source.IndexOf($row)+2)
        $wsTemp.cells.item($i,4) = $timeStamp
        $wsTemp.cells.item($i,10) = $row."Last Name"
        $wsSource.cells.item($tr,49) = "Added by xyz)"
        $wsSource.cells.item($tr,49).Interior.ColorIndex =19
         $i++
        }               
     }
    elseif ...

$source[0].psobject.properties.IndexOf('Status') will give you the column number of the Status header. Column numbers start at 0. — AdminOfThings
– AdminOfThings, Commented May 20, 2021 at 12:14
sounds to me that you are filling an Excel file with input from a csv.. The index numbers in a csv could very well not match those in the Excel files. Are there common column headers to match them on? — Theo
– Theo, Commented May 20, 2021 at 12:19
@AdminOfThings - Thanks for your help, but that doesn't quite seem to work... ``` $source = Import-Csv $csvFile $tc = $source[0].psobject.properties.IndexOf('Status') Write-Host $tc ``` ..."Method invocation failed because [System.Management.Automation.PSNoteProperty] does not contain a method named 'IndexOf'" — JasBr
– JasBr, Commented May 21, 2021 at 22:41
@Theo - That's a good point, but in this case, the CSV header will always match the excel header. Unfortunately, the column position/number can vary somewhat between different excel files/sheets because users may add a couple extra columns, which breaks my hard-coded column reference (at column 49) $wsSource.cells.item($tr,49) = "Added by xyz)" — JasBr
– JasBr, Commented May 21, 2021 at 22:54
@AdminOfThings I think your code will work with the addition of .name specification — JasBr
– JasBr, Commented May 23, 2021 at 6:43

Theo · Accepted Answer · 2021-05-22 10:22:07Z

0

You need to create a Hashtable to map the Excel column names with their index:

# create a hash with Excel column header names and their indices
$colMax    = $wsSource.UsedRange.Columns.Count
$xlHeaders = @{}
for ($col = 1; $col -le $colMax; $col++) {
    $name = $wsSource.Cells.Item(1, $col).Value()  # assuming the first row has the headers
    $xlHeaders[$name] = $col
}

Now you can match the column from the Csv with the column index in Excel like

if (!$xlHeaders.ContainsKey('Status')) {
    Write-Warning "Excel sheet does not have a column named 'Status'"
}
else {
    $xlColumn = $xlHeaders['Status']
    $wsSource.Cells.Item($tr, $xlColumn) = "Added by xyz)"
    $wsSource.Cells.Item($tr, $xlColumn).Interior.ColorIndex = 19
}

answered May 22, 2021 at 10:22

Theo

61.9k8 gold badges28 silver badges48 bronze badges

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

1 Comment

JasBr Over a year ago

Excellent! Thanks very much for the detailed reply, Theo... Jason

Collectives™ on Stack Overflow

Powershell: get column number from CSV file header

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related