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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy