Filter Excel via Powershell

A few thoughts.

- You are starting from and XLS or XLSX file, right, not a CSV file?

- You want the output to be a new XLS or XLSX file, right, not a CSV file?

- What version of Powershell are you working in, and what's your proficiency in Powershell?

- Does the solution have to be Powershell, or could a VBA or VBS approach solve your need?

- Will this be run on a computer that has Excel installed on it?

- If you go the PS route, and are wanting to read and write actual XLS* files, then you really have two choices.  There is no native support in PS for reading and writing XLS* files, so one approach is to do it with Excel automation in PS.  That doesn't require anything extra, but will need a fair amount of code to get the job done.  The other approach is to use an existing library that implements the ability to read and write XLS* files.  This saves you a lot of coding, but requires the usage of an external library.  For example: GitHub - dfinke/ImportExcel: PowerShell module to import/export Excel spreadsheets, without Excel

---

»bp

Hello Bill,

- I'm looking to start from an CSV
- Looking to export as an CSV
- I'm working with PS Version is 4.0, and I'm Beginner to Intermediate...I can start basic powershell scripts, but also modify/edit intermediate scripts to fit my environment.
- This will be ran on a server. The script will be automated and the server doesn't have excel installed on it.

Hope this helps.

I haven't gotten too familiar with GitHub yet, but I'll take a look at the link.

Thank you,

Okay, if you are working with just CSV files then it's a LOT easier, no need for the extra Excel specific stuff.  Powershell can handle reading and writing to CSV files with no problem.

Any chance you could provide a sample file for testing?

---

»bp

I did a simple test here just to demonstrate.  My input file, and one line Powershell are below.  I made up column names of C1, C2, C3 etc.  If you have a header row then use your column name that you want to filter on where I used C2.  Hope this helps.

Import-Csv -Path .\in.csv | ? C2 -like xxxx | Export-Csv -notype out.csv

Select allOpen in new window

C1,C2,C3,C4,C5,C6,C7,C8,C9,C10,C11,C12,C13,C14,C15,C16,C17
1,XXXX,D3,D4,D5,D6,D7,D8,D9,D10,D11,D12,D13,D14,D15,D16,D17
2,XXXX,D3,D4,D5,D6,D7,D8,D9,D10,D11,D12,D13,D14,D15,D16,D17
3,D2,D3,D4,D5,D6,D7,D8,D9,D10,D11,D12,D13,D14,D15,D16,D17
4,D2,D3,D4,D5,D6,D7,D8,D9,D10,D11,D12,D13,D14,D15,D16,D17
5,XXXX,D3,D4,D5,D6,D7,D8,D9,D10,D11,D12,D13,D14,D15,D16,D17

Select allOpen in new window

---

»bp

Oh sweet,

Yes, attached is a sample file. Basically, anything in Test 8 column that is a 'success', i would like that and the other 2 rows that are apart of the 'success' to be exported into a new .csv!
test1.csv

sweetness, yes this works out. i just had to add ' ' after -like

Import-Csv -Path .\in2.csv | ? test8 -like 'success' | Export-Csv -notype out2.csv