Import-CSV Where -notmatch list of array values

Question

I am trying to import a csv file where it doesn't import any values that are listed in an array declared above the import line. The array is made up of certain values that are pulled out of a database and I wan't to import all rows in the csv file that the txnID column values do not match the values in the array however I am having trouble trying to loop through my array.

I am new to using powershell and maybe I am not even implementing the array correctly but I haven't been able to find anything about import-csv Filename |Where column -notmatch $array

$Database = 'Database'
$Server = "Server"
$SqlQuery = 'SELECT DISTINCT WebOrderNumber FROM tbOrders
WHERE WebOrderNumber IS NOT NULL AND Len(WebOrderNumber)>8'
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Data Source=Datasource;Initial Catalog=Database;User ID=ID;Password=Pass;Integrated Security=False;"

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlConnection.Open()
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter


$SqlAdapter.SelectCommand = $SqlCmd


$Reader = $SqlCmd.ExecuteReader()
while ($Reader.Read()) {

     #write-Output($Reader.GetValue($0))
     $Key = $Reader.GetValue($0)

$table += $Key

}

foreach ($Row in $table){
write-output($Row)
$CSVFile = (import-csv "C:\Users\Office-Admin\Documents\Complete Sales Orders.csv") |where {$_.txnID -ne $Row} | select txnID, FirstName, LastName, Cust_Name, mc_Shipping, Payment_Gross, address_street, Address_Zip, quantity, item_name, item_number, payer_email, address_city, address_state, address_country, address_name, Shipping_Method, mc_gross  

 }
 $CSVFile | export-csv "C:\Users\Office-Admin\Documents\Sales Order Import List.csv" -notypeinformation
 remove-item variable:table

 #Send SMTP Message
 $SqlConnection.Close()

I've updated my code slightly however the problem still persists. I'm realizing that I believe with the code now, everytime I loop through and import, the previous condition in the where is forgotten so the only value that is not imported in the end is the last $Row value but I need all of the values in $table to be excluded and I don't know how I can do this.

What happens if you do where {$Row -notcontains $_.txnID} that would only work for exact matches though. Currently you are string to match a string to an array which as you know is not working. -Contains and -notcontains compare single elements to arrays. I think you should also just reverse the comparion { $Row -notmatch $_.txnID} — Matt
– Matt, Commented Mar 5, 2015 at 15:42
that still didn't work. I'm starting to think that maybe it has something to so with my $table array structure. Since there are multiple values being added, when i write-output $Row is lists a long single line string of all the values with no separator. Do you see any problems with how i'm adding values to the $Table array? — user3841709
– user3841709, Commented Mar 5, 2015 at 15:47
I may also add that I just ran a scenario where $table only had 1 item in it and the code did what I was wanting it to do so I think it is definitely something in the way I'm adding values to the array — user3841709
– user3841709, Commented Mar 5, 2015 at 15:51
Now i believe the culprit is that since I am looping through foreach $row in $table, if there are 3 values in my array, it runs the import 3 times but overwrites the changes made each time so the only value that is being taken out of the csv is the last value in my array — user3841709
– user3841709, Commented Mar 5, 2015 at 16:12

Dane Boulton · Accepted Answer · 2015-03-06 16:25:26Z

2

Something list this should work. The main problem is you are over writing your csv every loop.

$table = import-csv file1.csv | % {$_ID} #gets array of just the ID values
$CSVFile = Import-csv file2.csv  | where{$table -notcontains $_.ID} | export-csv output.csv -notypeinformation

To show you how this works I created to files as an example:

File 1: CSV with IDs:
ID,Stuff
123,alittlestuff
234,morestuff
345,evenmore
456,alotmore
567,somemore
678,notsomuch
789,tonesofstuff


File 2: csv with ID and stuff:
ID,stuff
123,hello
ghf,world
234,test
lkj,this

after running the code the only rows that get output are:

ID,Stuff
ghf,world
lkj,this

So I think to fit it into your code use this:

$filter = $table | %{$_.txnID}
$CSVFile = (import-csv "C:\Users\Office-Admin\Documents\Complete Sales Orders.csv") | where{$filter -notcontains $_.txnID} || export-csv "C:\Users\Office-Admin\Documents\Sales Order Import List.csv" -notypeinformation

edited Mar 6, 2015 at 16:25

answered Mar 5, 2015 at 23:34

Dane Boulton

1,3251 gold badge11 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

user3841709 Over a year ago

I think this is definitely closer, but is the import-csv being overwritten inside the loop also? ex. if the first row value = 5 and the first import would exclude the row in the csv where the column was 5. but then the second row has a value of 2, the code would then go through and import all rows except for the row where the corresponding column = 2. But the second time through, this seems to overwrite the previous import and include 5 the second time but not 2. I want it so that all $Row in $Table are excluded completely so that neither 5 or 2 would be in the new csv

user3841709 Over a year ago

Is there anyway that I can include a range of my array like where {$_.txnID -ne $table[0..($table.length)] in the where condition because the biggest problem also is the array size is dynamic as are the values the array will hold

Dane Boulton Over a year ago

Dont import the csv in each loop import it to a variable outside the loop and then use it inside the loop see my edits above.

Dane Boulton Over a year ago

wait scratch that I added some more test data and it didnt work let me play around with it and try and fix it.

user3841709 Over a year ago

Ok, thanks for your help. I'll keep messing with it and see if I can find a solution

|

Collectives™ on Stack Overflow

Import-CSV Where -notmatch list of array values

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related