Avatar of johnj_01201
johnj_01201 asked on

way to query the result set of invoke-sql?

I have  a result set from invoke-sql that I've modified so that I no longer need any record that has a field where $CSV.Path="IGNORE". I want to somehow filter this result set so that the records that contain Path="IGNORE" are not exported to a CSV file.
I was wondering if there was a way to invoke-sql on the modified $CSV or maybe a way to filter the ignored records from being exported? Any other ideas?
thanks!
I've tried .REMOVE and .DELETE, but it seems to error saying $CSV is a fixed object and somebody else told me $CSV is meant to be read-only even thought I can modified the data it returns.

#Read the SQL table
$CSV = Invoke-Sqlcmd -Query $Query
#modify the data and set $CSV.Path to "IGNORE" for obsolete records
...
#export the results to a CSV file
#Need some way to delete, or filter out, records set to be ignored.
$CSV | export-csv -path $sf -NoTypeInformation
Powershell

Avatar of undefined
Last Comment
johnj_01201

8/22/2022 - Mon
ASKER
johnj_01201

Powershell 3+

I was able to find a workaround on the following web page, but I would still like to do before or while exporting the file the first time.
The work around is to export the file with the field E3Path="IGNORE", use Import-CSV with a where-object, and then export-csv to save the results again.
$CSV | export-csv -path $sf -NoTypeInformation
$tempC = Import-csv -path $sf | Where-Object {$_.E3Path -ne 'IGNORE'}
$tempC | export-csv -path $sf -NoTypeInformation


http://www.powershellmagazine.com/2012/12/04/skipping-empty-csv-objects/

COOL! Now I can programmatically delete records that I flag from invoke-sql results!!!
ASKER CERTIFIED SOLUTION
Qlemo

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
johnj_01201

Perfect! Thanks!
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck