Vista - How do I filter rows in one csv file matching a value in another

Hi,

Can someone please help me get the following done in Powershell:

I'v got this in a csv file (RefVal.csv):

RefVal
01
02
07
05
09
03

and I'v got this in another csv file (RefValContent.csv):

RefValC,Col1,Col2
01,Col1Content1,Col2Content1
02,Col1Content2,Col2Content2
03,Col1Content3,Col2Content3
04,Col1Content4,Col2Content4
05,Col1Content5,Col2Content5
06,Col1Content6,Col2Content6
07,Col1Content7,Col2Content7
08,Col1Content8,Col2Content8
09,Col1Content9,Col2Content9

The csv output I'm trying to pipe out:

RefValC Col1 Col2
------- ---- ----
01 Col1Content1 Col2Content1
02 Col1Content2 Col2Content2
03 Col1Content3 Col2Content3
05 Col1Content5 Col2Content5
07 Col1Content7 Col2Content7
09 Col1Content9 Col2Content9

I'v tried some variations like the following but without success.

PS >Import-Csv RefValContent.csv | ?{$_.RefValC -match $(Import-CSV
refval.csv | %{$_.RefVal})} | export-csv output.csv

Thanks!

Art-De

Mmm data parsing!

Perhaps do it the same way one
would do this in almost any Windows
process - by using a data parsing tool.

Here using the automation tool
powershell.exe
_automating_ a data parsing tool
logparser.exe:

$headers = LogParser "SELECT text
FROM RefValContent.csv
WHERE index = 1" `
-i textline -q on

" "
LogParser.exe "SELECT $headers
FROM RefValContent.csv
WHERE RefValC IN
(Select RefVal FROM RefVal.csv) " `
-i csv -stats off

" "
"Done!"
" "
Exit

Returns:

RefValC Col1 Col2
------- ------------ ------------
1 Col1Content1 Col2Content1
2 Col1Content2 Col2Content2
3 Col1Content3 Col2Content3
5 Col1Content5 Col2Content5
7 Col1Content7 Col2Content7
9 Col1Content9 Col2Content9

Done!

Have some fun
data parsing,
plus charting,
and report making,
all by using Log Parser!

Thanks for the solution Flowering Weeds! And thanks for introducing me to
Log Parser. This is the second data parsing solution you've given me with the
Log Parser tool. It's a great tool and I'm trying to get famiiar with it.

"Flowering Weeds" wrote:

hi,
maybe it's because of "-match"

i can't tell you why , but it can work

object -match array (wrong?)
array -match object (right?)
maybe...maybe...

Here are two ways, three including v2:

# v1 and v2: using the -Contains operator
$refVal = ipcsv RefVal.csv | % {$_.RefVal} | sort
ipcsv RefValContent.csv | ? {$refVal -contains $_.RefValC}

# v1: using the -Match operator and creating a regex pattern by joining
# the array through string expansion and the Output Field Separator '$OFS'
$refVal = ipcsv RefVal.csv | % {$_.RefVal} | sort
$pat = &{$ofs = '|'; "$refVal"}
ipcsv RefValContent.csv | ? {$_.RefValC -match $pat}

# v2: using the -Match operator and creating a regex pattern by joining
# the array through the -Join operator
$refVal = ipcsv RefVal.csv | % {$_.RefVal} | sort
ipcsv RefValContent.csv | ? {$_.RefValC -match ($refVal -join '|')}

--
Kiron

Thanks Kiron! The solution with the -Contains operator gives me the correct
number of matched records. However the -Match operator solutions are
resulting in a greater number of matched records. Ì've not yet taken the time
to check out the reason.

Thanks for the help!

"Kiron" wrote: