PowerShell CSV comparison

Question

I am very, very, very new to Powershell. I was wondering if any one an help me with the following script:

The idea is to have two excel spreadsheets.

1.csv

QCODE PC1009 PC1009 PC1011 PC1012

2.csv

QCODE PC1009 PC1009 PC1009 PC1012

I am trying to compare values between the two CSV documents. If the value in cell1 in 1.csv is equal to any cell in 2.csv the script must perform a certain action, once the action is finished it must loop over to cell2 in 1.csv and compare it again with all the values in 2.csv

This is about as far as I have managed yo get:

$CSV=Import-Csv C:\1.csv
$COMP=Import-Csv C:\2.csv
$count=0
$cnt=0

while($count -le $CSV.Count)
{



while($validator -eq $false)
{
if($CSV[$count].QCODE -eq $COMP[$cnt].QCODE)
{
Write-Host "Exiting"
$validator=$true
}

else{
$cnt++
}
}



$count++

 }

It's a mess, I apologize. Any help would be greatly appreciated

Hey there, could you share a little bit more about the structure of your CSV files? — user189198
– user189198, Commented Dec 31, 2013 at 5:29
Thanks for the quick reply. Each sheet is a single column, with QCODE as the header for the column in each sheet. — user3148374
– user3148374, Commented Dec 31, 2013 at 5:31
Sorry, I didn't see this comment until after I worked on a solution. It will still work the same way, regardless of the number of columns though. — user189198
– user189198, Commented Dec 31, 2013 at 6:00

score 2 · Accepted Answer · 2013-12-31 06:01:04Z

Here is a solution for you. I have created two CSV files with matching headers. The column names are:

Prop1
Prop2
Prop3
Prop4
Prop5

When these lines are imported into PowerShell, it will automatically create a PSObject for each line. The property names on the PSObject will be the column headers. These two CSV files exist in the folder named c:\test.

NOTE: There is a single, mismatching value between the two files, in the dead middle. This will be our test.

CSV files

The code looks like this. There are some in-line comments to help guide you. Basically, we're dynamically querying all of the property (column) names, getting the value of each one (the cell values), and comparing them. If they do not match, we throw a warning. Based on the single, mismatching "cell" in this example, the output I get is in a screenshot below. It seems to be working quite well in my testing.

NOTE: Even though it says that line #1 is mismatching, and you might think it's line #2, that's because arrays are zero-based. Therefore, in array terminology, #1 is actually #2, because it starts counting at zero.

# Import both CSV files
$Csv1 = Import-Csv -Path C:\test\csv1.csv;
$Csv2 = Import-Csv -Path C:\test\csv2.csv;

# For each line in CSV1 ...
foreach ($Line1 in $Csv1) {
    $LineNumber = $Csv1.IndexOf($Line1);
    # Get the same line from CSV2
    $Line2 = $Csv2[$LineNumber];

    # For each property (column) ...
    foreach ($Property in (Get-Member -InputObject $Line1 -MemberType NoteProperty)) {
        # Get the property's name
        $PropertyName = $Property.Name;
        # If the value of the property doesn't match each CSV file ..
        if ($Line1.$PropertyName -ne $Line2.$PropertyName) {
            # Warn the user
            Write-Warning -Message ('Value of property {0} did not match for line # {1}' -f $PropertyName, $LineNumber);
            # PERFORM SOME CUSTOM ACTION HERE
        };
    }
}

PowerShell ISE Results

Collectives™ on Stack Overflow

PowerShell CSV comparison

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related