CODEWITHSUNDEEP
csvpowershellpowershell-3.0
Fiddle Freak
Fiddle Freak

Reputation: 2051

Matching values in hash tables while ignoring case sensitivity betwen two CSV files

I'm trying to append a column by seeing if the value of the column from CSV file2 is contained in CSV file1.

I have a CSV file1 (test1.csv):

csv1ColumnOne,csv1ColumnTwo
1,dF3aWv
2,
3,ka21p
4,NAE31
5,dsafl
6,nv02k
7,qng02
8,xcw3r
9,dF3aW

I have a CSV file2 (test2.csv):

csv2ColumnOne,csv2ColumnTwo
bbetfe,DF3AW
asdf,dsafl
qwer,
zxcv,NAE31
poiu,nbrwp1

Given the following code...

$hashTable = @()
Import-Csv C:\path\test1.csv | ForEach-Object {
  $hashTable[$_.csv1ColumnOne] = $_.csv1ColumnTwo
}

(Import-Csv C:\path\test2.csv) |
  Select-Object -Property *, @{n='csv1ColumnThree';e={
    if ($hashTable.ContainsKey($_.csv2ColumnTwo)) {
      $_.csv2ColumnTwo
    } elseif (-not ($_.csv2ColumnTwo)) {
      'No value found from csv file2'
    } else {
      'No value found from csv file1'
    }
  }} | Export-Csv "C:\path\testresults.csv" -NoType

The results look like this:

csv2ColumnOne,csv2ColumnTwo,csv1ColumnThree
bbetfe,DF3AW,"No value found from csv file1"
asdf,dsafl,dsafl
qwer,,No value found from csv file2
zxcv,NAE31,NAE31
poiu,nbrwp1,"No value found from csv file1"

When instead it should look like this:

csv2ColumnOne,csv2ColumnTwo,csv1ColumnThree
bbetfe,DF3AW,dF3aW
asdf,dsafl,dsafl
qwer,,"No value found from csv file2"
zxcv,NAE31,NAE31
poiu,nbrwp1,"No value found from csv file1"

The reason I see bbetfe,DF3AW,"No value found from csv file1" ins't bbetfe,DF3AW,dF3aW is because of the case sensitivity of the value. Anyway to ignore the case sensitivity with alpha-numeric values?

Upvotes: 1

Views: 3327

Answers (4)

Ansgar Wiechers
Ansgar Wiechers

Reputation: 200463

Lookups with ContainsKey() already are case-insensitive. You're just using the wrong data structure, and using it in the wrong way, too.

If you want to look up a key in a hashtable you need to actually use the data you want to look up as the key of the hashtable:

$hashTable[$_.csv1ColumnTwo] = $_.csv1ColumnOne

For looking up something in the values of a hashtable use ContainsValue().

However, since you just want to check if the second column of the first CSV contains a the value from the second column of the second CSV you don't need a hashtable in the first place. A simple array will suffice.

$list = Import-Csv 'C:\path\test1.csv' | Select-Object -Expand csv1ColumnTwo

Import-Csv 'C:\path\test2.csv' |
  Select-Object -Property *, @{n='csv1ColumnThree';e={
    if ($list -contains $_.csv2ColumnTwo) {
      $_.csv2ColumnTwo
    } elseif (-not ($_.csv2ColumnTwo)) {
      'No value found from csv file2'
    } else {
      'No value found from csv file1'
    }
  }} | Export-Csv 'C:\path\testresults.csv' -NoType

If you don't want empty strings "found" in the second CSV simply exclude the element from $list:

$list = Import-Csv 'C:\path\test1.csv' |
        Select-Object -Expand csv1ColumnTwo |
        Where-Object { $_ }  # allow only non-empty values

Not every problem is a nail, so don't try to fix everything with a hammer.

Upvotes: 4

Fiddle Freak
Fiddle Freak

Reputation: 2051

Ansgar basically had the right answer, but with a bug. it was printing the row in the second file as qwer,, when instead it should have printed qwer,,No value found from csv file2. There is another condition that needed to be added in the first if statement as shown below.

$list = Import-Csv 'C:\path\test1.csv' | Select-Object -Expand csv1ColumnTwo

Import-Csv 'C:\path\test2.csv' |
  Select-Object -Property *, @{n='csv1ColumnThree';e={
    if (($list -contains $_.csv2ColumnTwo) -and ($_.csv2ColumnTwo)) {
      $_.csv2ColumnTwo
    } elseif (-not ($_.csv2ColumnTwo)) {
      'No value found from csv file2'
    } else {
      'No value found from csv file1'
    }
}} | Export-Csv 'C:\path\testresults.csv' -NoType

The empty values in the 2nd file were being checked as true, so the elseif was never being reached.

Upvotes: 1

zdan
zdan

Reputation: 29450

To avoid having to convert the string to lower case, just use the -icontains comparison operator (the "i" means case insenstive comparison):

So instead of

If ($hashTable.ContainsKey($_.csv2ColumnTwo)){

try this:

If ($hashTable.keys -icontains $_.csv2ColumnTwo){

Upvotes: 3

Anthony Stringer
Anthony Stringer

Reputation: 2001

can you just make them all lowercase?

$a = ipcsv 'C:\path\test1.csv'
$a | % {$_.csv1columntwo = $_.csv1columntwo.tolower()}
$a

$b = ipcsv 'C:\path\test2.csv'
$b | % {$_.csv2ColumnOne = $_.csv2ColumnOne.tolower(); $_.csv2ColumnTwo = $_.csv2ColumnTwo.tolower()}
$b

Upvotes: 1

Related Questions