Comparing CSV 1

[doubleplay1]

Hi, I am fairly new to Powershell, but I am loving it. I am stuck at the end of my little script and am having problems comparing/parsing through two csv files and exporting the difference in the same format. Any help/suggestions are welcomed and appreciated!

Ok, so below you will find so far what kind of compare string has been put together so far with the help of another powershell user.

$Master = import-csv c:\scripts\master.csv
$New = import-csv c:\scripts\new.csv
Compare-Object $Master $New|
Where {$._SideIndicator -eq '=>'}|
ForEach-Object {$_.InputObject}|
Export-Csv c:\scripts\finalnew.csv

Basically, I want it to read each line in the master csv (grabbing all the information from each line) and parse through every line on the new.csv file to make sure none of that information is already on the master. e.g : If line 1 is on master and also on new, do not include it on the finalnew.csv or viceversa; whatever can make the script better would be cool. I basically will be using this as a master list for approved users and new as the new users and make sure that the difference is the output.

I will also post below the code that I am using to export the information from AD and to create the new.csv file I reference above.
Thanks for all your help!

Get-QADUser -enabled -sizelimit 0 |
Select-Object SamAccountName,@{n="LastLogonTimeStamp";e={$_.LastLogonTimeStamp.value}},WhenCreated,PasswordNeverExpires,AccountExpires,Description|
Sort-Object LastLogonTimeStamp |
Export-Csv c:\scripts\new.csv

 

[crobin1]

So what exactly is the problem? Are you getting an error message? Is the output not what you expected, or can you give us an example?

 

[Lundkvist]

I'm sure there is a better solution, but this is a kind of workaround.

So, just handle the files as normal files and not as csv files when you compare them, that seems to work fine.

$Master = Get-Content master.csv
$New = Get-Content new.csv
Set-Content -Path finalnew.csv -Value $MasterCSV[0]
Compare-Object $Master $New | Where {$_.SideIndicator -eq '=>'} | ForEach-Object {$_.InputObject} | Add-Content finalnew.csv

 

[doubleplay1]

Crobin, I am actually getting an output, but if I have SamAccountName's in the Master.csv file they should never show up in the finalnew.csv file, which they do.

I even tried it with simple 3 username new.csv file and one of those in the master.csv file and it still doesn't work.

I am not getting any error messages, I am just not getting the desired output. If it is in the master list, it should not show up in the finalnew.csv .

Lund: I tried that exact code and I get an error

"Cannot index into a null array."

Thank you both for showing interest and helping!

 

[crobin1]

Something about using import-csv really confuses compare-object, though I'm not sure why. Using Lundkvist's solution to just do get-content seems to work much better. I got all kinds of wrong answers when using import-csv on different test files, but with get-content it worked every time.

If you typed the code exactly as above, there is actually a typo which would explain the error. $MasterCSV[0] should just be $Master[0]. This is assuming there is a header row in the master.csv file, and adds that header row as the first line of the output file. If you make that change the code should work and do what you want.

 

[doubleplay1]

Crobin, I will give that a try and report back.

Thanks!

 

[doubleplay1]

Crobin, Lund : THis is almost perfect, except i think we can make this easier in one way. After testing in different ways it seems if any changes are made to any other field (like Description) the whole row gets added to the final file. Really, If we could just check for SamAccountName it would be fine; thus resulting if the SamAccountName is on the master, and it is also on the new.csv then just ignore that whole line and do not add it to the final .

Thanks again for the typo explanation, I am still fairly new so I would have never picked that up as something wrong; I thought it would have been there for a reason!

 

[Lundkvist]

Sorry for the typo

And thanks crobin1 for correcting it

Doubleplay, I know you can do what you need with a bit clever scripting and comparing columns... perhaps if you provide 3-4 rows of each csv file you got and let me know what fields you are interested in comparing and I can have a stab at it.

 

[crobin1]

Absolutely PowerShell can do this, but you can also do it quickly using another free tool from Microsoft called LogParser. It allows you to run SQL-like queries against all kinds of files, including CSV files.

The basics:
1. Download and install LogParser. Add the LogParser folder to your PATH
2. Save the following as diff.sql

SELECT
    SamAccountName,
    LastLogonTimeStamp,
    WhenCreated,
    PasswordNeverExpires,
    AccountExpires,
    Description
INTO
    finalnew.csv
FROM
    new.csv
WHERE
    SamAccountName NOT IN
    (SELECT SamAccountName
     FROM master.csv)

3. Run the following at a command prompt (or even within the PowerShell console). This also assumes you've navigated to the folder with all of the CSV files.

logparser -i:csv -o:csv file:diff.sql

I think PowerShell and LogParser are two of the best tools Microsoft has put out.

 

[doubleplay1]

Crobin, I have never dealt with Logparser, nor would I have thought of another utility, Thank you and I will give it a try and report back.

Thanks!

 

[doubleplay1]

Crobin, LogParser did the job and man does that little util fly through those csv's!!

Now that you cracked that little powershell weakness and found a way to compare. It brings up another question,
how do I integrate that into my code . what I am asking is how do I execute logparser with those parameters inside of the same script. I am fairly new and I was reading on Invoke-item ( which didnt work), also adding it to the windows path ( didnt work) . Now "didn't work" could mean I am probably missing a quotation mark or a single quote somewhere, but that would really be cool to do!!

SO here is the code so far .


$now=get-date
$daysSinceLastLogon=60
$a = new-object -comobject wscript.shell
$b = $a.popup("Please be patient, retrieving records.",0,"This could take a while",1)

if ($b -eq 2)
{
$c = $a.popup("Script Terminated")
#break
}
else
{
"Processing will Continue"
}

Get-QADUser -Enabled -sizeLimit 0 |
where {$_.lastlogontimestamp.value -and (($now-$_.lastlogontimestamp.value).days -gt $daysSinceLastLogon)} |
where {$_.dn -notmatch 'OU=OrgUnit,DC=west,DC=verizon,DC=com'}|
where {$_.dn -notmatch 'OU=Secondary OU,=Top OU,DC=west,DC=verizon,DC=com'}|
Select-Object SamAccountName,@{n="LastLogonTimeStamp";e={$_.LastLogonTimeStamp.value}},WhenCreated,PasswordNeverExpires,AccountExpires,Description|
Sort-Object LastLogonTimeStamp |
Export-Csv c:\scripts\excluded.csv -NoTypeInformation


#logparser -i:csv -o:csv file:c:\scripts\diff.sql



The second where-object statement is not working to exclude a secondary OU ( couldnt find that on google anywhere)

The top is to only export users who have not logged in the past 60 days.

Feel free to check my code and it may be a way to make this easier, this is just what I have come up and have been helped with in the past week.

THanks again for logparser!!

 

[crobin1]

It looks like you can just call LogParser straight from the script, no need to use invoke-item. I haven't tried this myself yet, so I'll have to point to some external resources for this:

http://muegge.com/blog/

- only a few posts, but specifically about PowerShell and LogParser

http://zachbonham.blogspot.com/2008/01/powershell-logparser.html

http://blogs.technet.com/mscom/arch...you-just-need-more-power-for-your-parser.aspx

- talks about using the LogParser COM object

http://www.eggheadcafe.com/software/aspnet/31909142/log-parser-gui-those-repo.aspx

 

[doubleplay1]

Crobin, I apologize for not posting earlier I was away for a bit. I made the call to logparser and it is now final!
Thank you all for your help and I look forward to more powershelling!!

 

[Zelandakh]

An excellent example of Tek-Tips at work.

Constructive original post showing what has been tried, great resource feedback, progress made on both sides and a final solution.

Thank you both for giving us a great thread that will surely help other people in the future.