CODEWITHSUNDEEP
powershell
Mani
Mani

Reputation: 195

Powershell - Compare values by specific columns from two text files

I have 2 text files that I want to compare by specific columns and output the unmatched records to separate files.

File1:

sns_clientattendeesId   sns_name    sns_appointment sns_cancel  sns_contact sns_irs_accompanyid sns_irs_meetingplace    sns_irs_notes   sns_irs_numberofcopies  sns_irs_salesid sns_irs_visitaddressid  sns_irs_visitplaceid    statecode   statuscode  ownerid temp_createdon  temp_createdby  temp_modifiedon temp_modifiedby
"088CA69B-68E5-E711-814A-8CDF9D9826F4"  "048ca69b-68e5-e711-814a-8cdf9d9826f4"  "048ca69b-68e5-e711-814a-8cdf9d9826f4"  0   "8D69E34E-B936-E511-80CB-8CDF9D982706"  ""  ""  ""  "0" "EM1512000013   87169585-e835-e511-80cb-8cdf9d982706    **  ******  **************  **" "CM1306000024"  "CM1306000024"  0   1   SYSTEM  2017-12-20 09:31:52.000 "SYSTEM"    2017-12-20 09:31:52.000 "SYSTEM"
"178CA69B-68E5-E711-814A-8CDF9D9826F4"  "138ca69b-68e5-e711-814a-8cdf9d9826f4"  "138ca69b-68e5-e711-814a-8cdf9d9826f4"  0   "AAB5CB32-B936-E511-80CB-8CDF9D982706"  ""  ""  ""  "0" "EM1503000006   b5159585-e835-e511-80cb-8cdf9d982706    **  **  ********    ********"   "CM1511000080"  "CM1511000080"  0   1   SYSTEM  2017-12-20 09:31:54.000 "SYSTEM"    2017-12-20 09:31:54.000 "SYSTEM"
"D70A7A2D-9449-E611-80FA-8CDF9D9826F4"  "d40a7a2d-9449-e611-80fa-8cdf9d9826f4"  "d40a7a2d-9449-e611-80fa-8cdf9d9826f4"  0   "0516182E-6649-E611-80FC-8CDF9D982706"  ""  ""  ""  "0" ""  ""  ""  0   1   SYSTEM  2016-07-14 07:25:45.000 "SYSTEM"    2016-07-14 07:25:45.000 "SYSTEM"

File2:

sns_clientattendeesId   sns_name    sns_appointment sns_cancel  sns_contact sns_irs_accompanyid sns_irs_meetingplace    sns_irs_notes   sns_irs_numberofcopies  sns_irs_salesid sns_irs_visitaddressid  sns_irs_visitplaceid    statecode   statuscode  ownerid temp_createdon  temp_createdby  temp_modifiedon temp_modifiedby
"F1FD121E-5858-E611-8103-8CDF9D982705"  "ebfd121e-5858-e611-8103-8cdf9d982705"  "ebfd121e-5858-e611-8103-8cdf9d982705"  0   "8571E34E-B936-E511-80CB-8CDF9D982706"  ""  ""  "*********************" "4" "EM1104000012   fb159585-e835-e511-80cb-8cdf9d982706    **  **  ********    *****"  "CM1205000003"  "CM1205000003"  0   1   SYSTEM  2016-08-02 02:23:40.000 "SYSTEM"    2016-08-02 02:23:40.000 "SYSTEM"
"08FE121E-5858-E611-8103-8CDF9D982705"  "02fe121e-5858-e611-8103-8cdf9d982705"  "02fe121e-5858-e611-8103-8cdf9d982705"  0   "F8BFCB32-B936-E511-80CB-8CDF9D982706"  ""  ""  ""  "0" "EM1310000005   bc3c908b-e835-e511-80cb-8cdf9d982706    **  *   ******* *******"    "CM1104000149"  "CM1104000149"  0   1   SYSTEM  2016-08-02 02:23:40.000 "SYSTEM"    2016-08-02 02:23:40.000 "SYSTEM"
"D70A7A2D-9449-E611-80FA-8CDF9D9826F4"  "d40a7a2d-9449-e611-80fa-8cdf9d9826f4"  "d40a7a2d-9449-e611-80fa-8cdf9d9826f4"  0   "0516182E-6649-E611-80FC-8CDF9D982706"  ""  ""  ""  "0" ""  ""  ""  0   1   SYSTEM  2016-07-14 07:25:45.000 "SYSTEM"    2016-07-14 07:25:45.000 "SYSTEM"

From File1 and File2, I want to be able to compare the records by 'sns_appointment' and 'sns_contact' columns and output the unmatched records to different files.

I have tried something like this but unsuccessful in getting the correct results.

# Compare the records of File1 and File2
$initialFile = Get-Content $dialog1.FileName
$differentialFile = Get-content $dialog2.FileName
$compareResultsContact = compare-object $initialFile $differentialFile -includeequal -Property sns_appointment,sns_contact

$totalRecordsCount = $compareResultsContact.Count
$matchingRecordsCount = $compareResultsContact.Where{$_.SideIndicator -eq '=='}.Count
$createUpdateRecordsCount = $compareResultsContact.Where{$_.SideIndicator -eq '=>'}.Count
$deleteRecordsCount = $compareResultsContact.Where{$_.SideIndicator -eq '<='}.Count

# If records to be created exists, create a seperate file, write the headerline and records to that file
if($createUpdateRecordsCount -gt 0){
    $createUpdateTextfile = $path + "31_CompareResultOutput_Clientattendee_Differential_create_update.txt"
    $sw1 = New-Object System.IO.StreamWriter($createUpdateTextfile, $false, $enc)
    $sw1.WriteLine(""+$headerLine1)
    $compareResultsContact | ?{$_.SideIndicator -eq '=>'} | ForEach-Object {$sw1.WriteLine($_.InputObject)}
    $sw1.Close()
    Write-Output ("Output file for 'Create & Update' operation: " + ($createTextfile))
}

# If records to be deleted exists, create a seperate file, write the headerline and records to that file
if($deleteRecordsCount -gt 0){
    $deleteTextfile = $path + "31_CompareResultOutput_Clientattendee_Differential_delete.txt"
    $sw2 = New-Object System.IO.StreamWriter($deleteTextfile, $false, $enc)
    $sw2.WriteLine(""+$headerLine1)
    $compareResultsContact | ?{$_.SideIndicator -eq '<='} | ForEach-Object {$sw2.WriteLine($_.InputObject)} 
    $sw2.Close()
    Write-Output ("Output file for 'Delete' operation: " + ($deleteTextfile))
}

The Compare-Object above returns the following output

sns_appointment sns_contact SideIndicator
--------------- ----------- -------------
                            ==           
                            ==           
                            ==           
                            ==

But the expected output should contain all the columns of unmatched rows

Expected Output for '31_CompareResultOutput_Clientattendee_Differential_create_update.txt' file:

sns_clientattendeesId   sns_name    sns_appointment sns_cancel  sns_contact sns_irs_accompanyid sns_irs_meetingplace    sns_irs_notes   sns_irs_numberofcopies  sns_irs_salesid sns_irs_visitaddressid  sns_irs_visitplaceid    statecode   statuscode  ownerid temp_createdon  temp_createdby  temp_modifiedon temp_modifiedby
"F1FD121E-5858-E611-8103-8CDF9D982705"  "ebfd121e-5858-e611-8103-8cdf9d982705"  "ebfd121e-5858-e611-8103-8cdf9d982705"  0   "8571E34E-B936-E511-80CB-8CDF9D982706"  ""  ""  "*********************" "4" "EM1104000012   fb159585-e835-e511-80cb-8cdf9d982706    **  **  ********    *****"  "CM1205000003"  "CM1205000003"  0   1   SYSTEM  2016-08-02 02:23:40.000 "SYSTEM"    2016-08-02 02:23:40.000 "SYSTEM"
"08FE121E-5858-E611-8103-8CDF9D982705"  "02fe121e-5858-e611-8103-8cdf9d982705"  "02fe121e-5858-e611-8103-8cdf9d982705"  0   "F8BFCB32-B936-E511-80CB-8CDF9D982706"  ""  ""  ""  "0" "EM1310000005   bc3c908b-e835-e511-80cb-8cdf9d982706    **  *   ******* *******"    "CM1104000149"  "CM1104000149"  0   1   SYSTEM  2016-08-02 02:23:40.000 "SYSTEM"    2016-08-02 02:23:40.000 "SYSTEM"

Expected Output for '31_CompareResultOutput_Clientattendee_Differential_delete.txt' file:

sns_clientattendeesId   sns_name    sns_appointment sns_cancel  sns_contact sns_irs_accompanyid sns_irs_meetingplace    sns_irs_notes   sns_irs_numberofcopies  sns_irs_salesid sns_irs_visitaddressid  sns_irs_visitplaceid    statecode   statuscode  ownerid temp_createdon  temp_createdby  temp_modifiedon temp_modifiedby
"088CA69B-68E5-E711-814A-8CDF9D9826F4"  "048ca69b-68e5-e711-814a-8cdf9d9826f4"  "048ca69b-68e5-e711-814a-8cdf9d9826f4"  0   "8D69E34E-B936-E511-80CB-8CDF9D982706"  ""  ""  ""  "0" "EM1512000013   87169585-e835-e511-80cb-8cdf9d982706    **  ******  **************  **" "CM1306000024"  "CM1306000024"  0   1   SYSTEM  2017-12-20 09:31:52.000 "SYSTEM"    2017-12-20 09:31:52.000 "SYSTEM"
"178CA69B-68E5-E711-814A-8CDF9D9826F4"  "138ca69b-68e5-e711-814a-8cdf9d9826f4"  "138ca69b-68e5-e711-814a-8cdf9d9826f4"  0   "AAB5CB32-B936-E511-80CB-8CDF9D982706"  ""  ""  ""  "0" "EM1503000006   b5159585-e835-e511-80cb-8cdf9d982706    **  **  ********    ********"   "CM1511000080"  "CM1511000080"  0   1   SYSTEM  2017-12-20 09:31:54.000 "SYSTEM"    2017-12-20 09:31:54.000 "SYSTEM"

Upvotes: 1

Views: 185

Answers (1)

iRon
iRon

Reputation: 23653

If the data of these files is delimited by one or more spaces and the fields are properly quoted when they contain spaces, you might load (still using the ConvertFrom-Csv parser) the data like this:

Get-Content $File |ForEach-Object -begin { $Header = $Null } -process {
    if ($Header) {
        $Data = ($_ |ConvertFrom-Csv -Header @(0..99) -Delimiter ' ').PSObject.Properties.Value.Where{ $_ }
        $Properties = [Ordered]@{}
        for ($i = 0; $i -lt $Header.Count; $i++) { $Properties[$Header[$i]] = $Data[$i] }
        [pscustomobject]$Properties
    }
    else {
        $Header = ($_ |ConvertFrom-Csv -Header @(0..99) -Delimiter ' ').PSObject.Properties.Value.Where{ $_ }
    }
}

Upvotes: 2

Related Questions