February 13, 2017 at 8:08 pm
hi all,
basically i am comparing 2 .csv files and output the result to another .csv file.
i can't seem to figure out how to further handle the compare-object result to be use in the if..elseif...else{} statement to print the result base on the sideIndicator result. Please further advice. Not too sure is there any catch i need to include to make sure the script will be handle properly?
clear-host
$northdb = Import-Csv "C:\temp\cep\Book1.csv" -Header "InstanceName", "DatabaseName", "PrinicipalName", "PrinicipalType", "Permission", "ObjectTypeLevel", "ObjectName" | Sort-object -Property "InstanceName", "DatabaseName", "PrinicipalName", "PrinicipalType", "Permission", "ObjectTypeLevel", "ObjectName" -unique
$sdb = Import-Csv "C:\temp\cep\Book2.csv" -Header "InstanceName", "DatabaseName", "PrinicipalName", "PrinicipalType", "Permission", "ObjectTypeLevel", "ObjectName" | Sort-object -Property "InstanceName", "DatabaseName", "PrinicipalName", "PrinicipalType", "Permission", "ObjectTypeLevel", "ObjectName" -unique
$result = Compare-Object $northdb $sdb -Property "InstanceName", "DatabaseName", "PrinicipalName", "PrinicipalType", "Permission", "ObjectTypeLevel", "ObjectName" -PassThru
if (where-Object{$_.SideIndicator -eq '<='}) {Select-Object "InstanceName", "DatabaseName", "PrinicipalName", "PrinicipalType", "Permission", "ObjectTypeLevel", "ObjectName" | export-csv C:\temp\cep\gogo.csv -NoTypeInfo}
elseif ($result.sideIndicator -eq '=>') {Select-Object "InstanceName", "DatabaseName", "PrinicipalName", "PrinicipalType", "Permission", "ObjectTypeLevel", "ObjectName" | export-csv C:\temp\cep\gogo.csv -NoTypeInfo}
else {write-host "there is no changes"}
February 14, 2017 at 1:09 am
What part, exactly, is not working and what is? What have you tried?
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
February 14, 2017 at 4:46 am
what is working is
clear-host
$northdb = Import-Csv "C:\temp\cep\Book1.csv" -Header "InstanceName", "DatabaseName", "PrinicipalName", "PrinicipalType", "Permission", "ObjectTypeLevel", "ObjectName" | Sort-object -Property "InstanceName", "DatabaseName", "PrinicipalName", "PrinicipalType", "Permission", "ObjectTypeLevel", "ObjectName" -unique
$sdb = Import-Csv "C:\temp\cep\Book2.csv" -Header "InstanceName", "DatabaseName", "PrinicipalName", "PrinicipalType", "Permission", "ObjectTypeLevel", "ObjectName" | Sort-object -Property "InstanceName", "DatabaseName", "PrinicipalName", "PrinicipalType", "Permission", "ObjectTypeLevel", "ObjectName" -unique
$result = Compare-Object $northdb $sdb -Property "InstanceName", "DatabaseName", "PrinicipalName", "PrinicipalType", "Permission", "ObjectTypeLevel", "ObjectName" -PassThru
i need to pass the compare result to a if ....else if ...else ......to determine the final result that will output to a .cvs, so thot of the below but is still not working yet.
if (where-Object{$_.SideIndicator -eq '<='}) {Select-Object "InstanceName", "DatabaseName", "PrinicipalName", "PrinicipalType", "Permission", "ObjectTypeLevel", "ObjectName" | export-csv C:\temp\cep\gogo.csv -NoTypeInfo}
elseif ($result.sideIndicator -eq '=>') {Select-Object "InstanceName", "DatabaseName", "PrinicipalName", "PrinicipalType", "Permission", "ObjectTypeLevel", "ObjectName" | export-csv C:\temp\cep\gogo.csv -NoTypeInfo}
else {write-host "there is no changes"}
February 14, 2017 at 5:50 am
if ($result -eq $null) {Write-Host "Empty"} else {Write-Host "not"}
So you may need to replace:
if (where-Object{$_.SideIndicator -eq '<='}) {Select-Object "InstanceName", "DatabaseName", "PrinicipalName", "PrinicipalType", "Permission", "ObjectTypeLevel", "ObjectName" | export-csv C:\temp\cep\gogo.csv -NoTypeInfo}
elseif ($result.sideIndicator -eq '=>') {Select-Object "InstanceName", "DatabaseName", "PrinicipalName", "PrinicipalType", "Permission", "ObjectTypeLevel", "ObjectName" | export-csv C:\temp\cep\gogo.csv -NoTypeInfo}
else {write-host "there is no changes"}
With something more like:if (result -eq $null) {write-host "there are no changes"}
else {Select-Object "InstanceName", "DatabaseName", "PrinicipalName", "PrinicipalType", "Permission", "ObjectTypeLevel", "ObjectName" | export-csv C:\temp\cep\gogo.csv -NoTypeInfo}
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
February 15, 2017 at 11:28 pm
hi,
but i need to also check if there is diff on the 2 files, not only when they are same infor.
So how do i actually expand it to that $result -eq '=>' write-host "there are changes in file2 but not in file1}
February 16, 2017 at 1:42 am
Here is how to count up the changes attributed to either the left or right hand side:$left = 0
$right = 0
foreach($r in $result){if($r.SideIndicator -eq '<=') {$left += 1} elseif($r.SideIndicator -eq '=>') {$right += 1}}
From that you should be able to work out what you want to display.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
February 17, 2017 at 3:08 am
hi,
tks for ur input will have to try it later. I somehow got it to work but somehow the diff cannot be write to another .csv file.
i was able to print out the diff base on the following testing code: i was able to print out "book1" to prove that my if else is working.
clear-host
$errorCode = 0
$object = "InstanceName", "DatabaseName", "PrinicipalName", "PrinicipalType", "Permission", "ObjectTypeLevel", "ObjectName"
$logfile = "C:\temp\cep\gogo.csv"
$oldFile = "C:\temp\cep\Book1.csv"
$newFile = "C:\temp\cep\Book2.csv"
if ((Test-Path $oldFile) -And (Test-Path $newFile)) {
Add-Content $logfile "DATABASE ACCESSES (OBJECT) IN CEPXHWDB01\CEPP instance)"
$impFileOld = Import-Csv $oldFile -Header $object | Sort-object -Property $object -unique
$impFileNew = Import-Csv $newFile -Header $object | Sort-object -Property $object -unique
$result = Compare-Object $impFileOld $impFileNew -Property $object -PassThru
$result | %{if ($_.SideIndicator -eq '=>') {write-host "book2"}
elseif($_.SideIndicator -eq '<=') {write-host "book1"}
else {Add-Content $logfile "no changes found."
#$errorCode = 0
}
}
} else {
Add-Content $logfile "files does not exist!"
#$errorCode = 1
exit 1
};
BUT in actual fact i cannot write the difference to the .csv file.
clear-host
$errorCode = 0
$object = "InstanceName", "DatabaseName", "PrinicipalName", "PrinicipalType", "Permission", "ObjectTypeLevel", "ObjectName"
$logfile = "C:\temp\cep\gogo.csv"
$oldFile = "C:\temp\cep\Book1.csv"
$newFile = "C:\temp\cep\Book2.csv"
if ((Test-Path $oldFile) -And (Test-Path $newFile)) {
Add-Content $logfile "DATABASE ACCESSES (OBJECT) IN CEPXHWDB01\CEPP instance)"
$impFileOld = Import-Csv $oldFile -Header $object | Sort-object -Property $object -unique
$impFileNew = Import-Csv $newFile -Header $object | Sort-object -Property $object -unique
$result = Compare-Object $impFileOld $impFileNew -Property $object -PassThru
$result | %{if ($_.SideIndicator -eq '=>') {Select-Object $object | export-csv $logfile -NoTypeInfo}
elseif($_.SideIndicator -eq '<=') {Select-Object $object | export-csv $logfile -NoTypeInfo} #cannot print result to .csv file ????
else {Add-Content $logfile "no changes found."
#$errorCode = 0
}
}
} else {
Add-Content $logfile "files does not exist!"
#$errorCode = 1
exit 1
};
February 17, 2017 at 3:47 am
This is a SQL Server forum and you're asking for a Powershell solution; allow me to propose a SQL solution instead. Why not import each CSV file (using bcp, BULK INSERT or SSIS) to a database table, and compare the data there? There's an article republished today that explains how to use the EXCEPT operator to do the comparison.
John
February 17, 2017 at 10:07 am
Hi John,
i posted my question under powershell section on this forum plus is related to SQL. Executing t-sql etc later. just i am stuck.
i will not import the data into the database due to certain constraint.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply