How do I convert data using powershell and then grab the users I want

Question

How do I create a variable to store data for further manipulation and then Convert the data coming out of the Excel document into a PSCustomObject and then filter it down to the people I care about

Here's what I have so far:

$excel = new-object -comobject Excel.Application
$path = "C:\Users\Test.xlsx"
$excel.workbooks.open($path)
$excel.Visible = $True

$variable = $excel.workbooks.open($path)
$worksheet = $variable.activeSheet


For ($j = 6; $j -lt 124; $j++) {
    For ($i = 1; $i -lt 11; $i++) {
     $worksheet.Cells.Item($j,$i).text
    }   
}

[Everything you wanted to know about PSCustomObject](https://kevinmarquette.github.io/2016-10-28-powershell-everything-you-wanted-to-know-about-pscustomobject/) depending on the excel file using a csv could a way, too — Olaf Reitz, Dec 21 '17 at 16:35

score 0 · Accepted Answer · answered Dec 21 '17 at 17:11

If you don't require adding additional properties to your PSCustomObject during its creation, the first part of your question may be answered by using PSExcel module from RamblingCookieMonster. Checkout the example Export and import data and the Import-XLSX module.

Once the .XLSX has been imported to a variable, you can pipe it to ForEach-Object to retrieve only the people you care about. In the example below, I have a column in my .XLSX called 'Name' which is used with the Where-Object cmdlet.

Import-Module \\pathToModule\PSExcel

$FileContents = Import-XLSX -path "myFile.xlsx"

$FileContents | ForEach-Object {
    $_ | Where-Object Name -EQ 'User I care about'
}

How do I convert data using powershell and then grab the users I want

1 Answers1