select data from excel using powershell

Question

I am writing a script to select specific data from a .xlsx file using PowerShell v7.0.

The Excel worksheet contains the following data:

+------------------------------+------------------+---------+
|           Company            |     Contact      | Country |
+------------------------------+------------------+---------+
| Alfreds Futterkiste          | Maria Anders     | x       |
| Centro comercial Moctezuma   | Francisco Chang  | x       |
| Ernst Handel                 | Roland Mendel    | x       |
| Island Trading               | Helen Bennett    | UK      |
| Laughing Bacchus Winecellars | Yoshi Tannamuri  | Canada  |
| Magazzini Alimentari Riuniti | Giovanni Rovelli | x       |
+------------------------------+------------------+---------+

To get a representation of the table that you can copy and paste as-is into an Excel sheet, click on Show code snippet and then on Run code snippet.

<table>
<thead>
    <tr>
        <th>Company</th>
        <th>Contact</th>
        <th>Country</th>
    </tr>
</thead>
<tbody>
    <tr>
        <td>Alfreds Futterkiste</td>
        <td>Maria Anders</td>
        <td>x</td>
    </tr>
    <tr>
        <td>Centro comercial Moctezuma</td>
        <td>Francisco Chang</td>
        <td>x</td>
    </tr>
    <tr>
        <td>Ernst Handel</td>
        <td>Roland Mendel</td>
        <td>x</td>
    </tr>
    <tr>
        <td>Island Trading</td>
        <td>Helen Bennett</td>
        <td>UK</td>
    </tr>
    <tr>
        <td>Laughing Bacchus Winecellars</td>
        <td>Yoshi Tannamuri</td>
        <td>Canada</td>
    </tr>
    <tr>
        <td>Magazzini Alimentari Riuniti</td>
        <td>Giovanni Rovelli</td>
        <td>x</td>
    </tr>
</tbody>
</table>

What i did:

$Output = [object][order]@{
                Name = $WorkSheet.Range("C1:C1000").Formula = "=x"
        }
        $Output

Expected result: list of company where Country = 'x'

Alfreds Futterkiste
Centro comercial Moctezuma
Ernst Handel
Magazzini Alimentari Riuniti

Thanks!

mklement0 · Accepted Answer · 2023-10-07 13:36:45Z

You should consider using specialized PowerShell modules to interact with Excel workbooks - e.g., ImportExcel.

If that is not an option, and you must use Excel's COM Automation Application interface, as in your question:

The following (easily tweaked) code assumes that your table is in the first worksheet of an .xlsx file (and that that worksheet contains nothing else).

# Note: Be sure to specify a *full path*
$xlFile = Convert-Path 'sample.xlsx'

& {

 $xl = New-Object -ComObject Excel.Application

 $ws = $xl.Workbooks.Open($xlFile).Worksheets(1)

 $ws.UsedRange.Rows | 
   Where-Object   { $_.Cells(1, 3).Value2 -eq 'x' } |
   ForEach-Object { $_.Cells(1, 1).Value2 }

 $xl.Quit()

}

Note: .Worksheets(1) is short for .Worksheets.Item(1), and .Cells(1, 3) for .Cells.Item(1, 3). In earlier versions of PowerShell (Core), only the latter, explicit form was supported,^[1] but since (at least) v7.2 the shorthand form works too (it always did in Window PowerShell).

^{[1] See GitHub issue #4554 for background information.}

Collectives™ on Stack Overflow

select data from excel using powershell

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related