CODEWITHSUNDEEP
excelpowershell
danglesauce19
danglesauce19

Reputation: 133

Check header row of Excel sheet for particular column

I have over 150 excel files where some have an extra column (let's call it "ExtraColumn"), while some do not have this column. Instead of opening each file manually to see which ones have the extra column, I want to use powershell to figure it out.

The code I have tried so far hasn't seemed to have gotten me anywhere. If you have any suggestions or can point me to the correct answer, that would be very wonderful and much appreciated!

gci -Path C:\Test -Recurse | % {
    $ExcelFile = (Get-Content $_.FullName -TotalCount 1)

    if ($ExcelFile -like "ExtraColumn") {
        Write-Host "$_ has the extra column"
    } else {
        Write-Host "$_ does not have the extra column" 
    }
}

Upvotes: 0

Views: 5540

Answers (1)

Avshalom
Avshalom

Reputation: 8889

You can use Excel ComObject, for the code simplicity just name the sheet otherwise you can find the sheet as well, add foreach section to run it on all files,

For the example i named the column - 'extracol'

$excel = New-Object -ComObject excel.application
$WB = $excel.Workbooks.Open('C:\exceltest.xlsx') 
$WS = $Excel.WorkSheets.item("Sheet1")

$ExtraCol = ($ws.Columns.Find('extracol'))
if ($ExtraCol) {$ExtraCol.Delete()}
$wb.Save()
$wb.Close()
$excel.Quit()

Upvotes: 1

Related Questions