• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 414
  • Last Modified:

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
0
johnj_01201
Asked:
johnj_01201
  • 2
1 Solution
 
johnj_01201Author Commented:
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!!!
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Doesn't this work?
$CSV | ? { $_.E3Path -ne 'IGNORE' } |  export-csv -path $sf -NoType

Open in new window

BTW - path or E3Path ?
0
 
johnj_01201Author Commented:
Perfect! Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now