parsing/comparing csv files

Ask your PowerShell-related questions, including questions on cmdlet development!
Forum rules
Do not post any licensing information in this forum.

Any code longer than three lines should be added as code using the 'Select Code' dropdown menu or attached as a file.
This topic is 15 years and 4 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked
User avatar
doubleplay1
Posts: 6
Last visit: Thu Nov 13, 2008 4:55 am

parsing/comparing csv files

Post by 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. Ok, so below you will find so far what kind of
compare string has been put together so far with the help of Dmitri.
Now somehow wrong spacing was freaking out the box and causing the
script to use up 50% cpu and never stop, that has been fixed. But, the
script still doesn't perform the functions needed.

Code: Select all

$Master = import-csv c:scriptsmaster.csv$New = import-csv c:scriptsnew.csvCompare-Object $Master $New|Where {$._SideIndicator -eq '=>'}|ForEach-Object {$_.InputObject}|Export-Csv c:scriptsfinalnew.csv[CODE]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.Any help/suggestions are welcomed
	and appreciated![CODE]Get-QADUser -enabled -sizelimit 0 |Select-Object
	SamAccountName,@{n="LastLogonTimeStamp";e={$_.LastLogonTimeStamp.value}},WhenCreated,PasswordNeverExpires,AccountExpires,Description|Sort-Object LastLogonTimeStamp |Export-Csv c:scriptsnew.csv[CODE]
User avatar
doubleplay1
Posts: 6
Last visit: Thu Nov 13, 2008 4:55 am

parsing/comparing csv files

Post by 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. Ok, so below you will find so far what kind of
compare string has been put together so far with the help of Dmitri.
Now somehow wrong spacing was freaking out the box and causing the
script to use up 50% cpu and never stop, that has been fixed. But, the
script still doesn't perform the functions needed.

Code: Select all

$Master = import-csv c:scriptsmaster.csv$New = import-csv c:scriptsnew.csvCompare-Object $Master $New|Where {$._SideIndicator -eq '=>'}|ForEach-Object {$_.InputObject}|Export-Csv c:scriptsfinalnew.csv[CODE]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.Any help/suggestions are welcomed
	and appreciated![CODE]Get-QADUser -enabled -sizelimit 0 |Select-Object
	SamAccountName,@{n="LastLogonTimeStamp";e={$_.LastLogonTimeStamp.value}},WhenCreated,PasswordNeverExpires,AccountExpires,Description|Sort-Object LastLogonTimeStamp |Export-Csv c:scriptsnew.csv[CODE]
User avatar
jhicks
Posts: 1789
Last visit: Mon Oct 19, 2015 9:21 am

parsing/comparing csv files

Post by jhicks »

What doesn't work? I think I'm getting a difference CSV file using your code.
User avatar
doubleplay1
Posts: 6
Last visit: Thu Nov 13, 2008 4:55 am

parsing/comparing csv files

Post by doubleplay1 »

J, just wondering here now that you suggested that and looking at my other piece of code that i use to export the new.csv.Could I not compare by the same paramaters that I use to extract, i.e SamAccountName, Description, etc.I am going to try your suggestion, I am just trying to see the true power of powershell :)Thanks again and I will let you know very soon if it works!'Edit : Just tried it and it runs through the code, but the file created is 0KB and nothing is in it.
doubleplay12008-11-13 09:33:17
User avatar
doubleplay1
Posts: 6
Last visit: Thu Nov 13, 2008 4:55 am

parsing/comparing csv files

Post by doubleplay1 »

J, no luck. I tried it with the -SamAccountname and took out the pipe in order to just stop the check there and show it to me on the screen and as you can see on the bottom right, something is just not working! Im starting to wonder if Powershell can do this!Let me know your thoughts,Thanks
User avatar
jhicks
Posts: 1789
Last visit: Mon Oct 19, 2015 9:21 am

parsing/comparing csv files

Post by jhicks »

I'm going to have to create some csv files with user names and try. It looks like it should work. Or can you post some small samples of your csv files, sanitized as needed.
User avatar
jhicks
Posts: 1789
Last visit: Mon Oct 19, 2015 9:21 am

parsing/comparing csv files

Post by jhicks »

Is this the type of result you are after:

Code: Select all

PS C:temp> $m=import-csv master.csv
PS C:temp> $n=import-csv new.csv
PS C:temp> diff $m $n -property samaccountname | where {$_.sideindicator -eq "=>"}

samaccountname                                              SideIndicator
--------------                                              -------------
test2                                                       =>
test3                                                       =>
test4                                                       =>
test5                                                       =>
test6                                                       =>
test7                                                       =>
test8                                                       =>
test9                                                       =>
test10                                                      =>

jhicks2008-11-13 11:19:19
User avatar
doubleplay1
Posts: 6
Last visit: Thu Nov 13, 2008 4:55 am

parsing/comparing csv files

Post by doubleplay1 »

j, maybe I am doing something wrong here , but here is how I put the code together and I still get an empty 0KB file as output.

Code: Select all

$m = import-csv c:scriptsmaster.csv$n = import-csv c:scriptsnew.csvdiff $m $n -property samaccountname | where {$_.sideindicator -eq "=>"} | foreach {$n | where {$_.samaccountname -match $_.samaccountname}} |export-csv finalnew.csv[CODE]The above code would grab the samaccountname property and where each time it was in the m$ and it matched on the n$ it would include it in the final copy. If so, I actually want whatever is on the master to never be on the final , THanks!
User avatar
jhicks
Posts: 1789
Last visit: Mon Oct 19, 2015 9:21 am

parsing/comparing csv files

Post by jhicks »

If you think the PowerGUI behavior is buggy, be sure to post something in their forums. I always test right from the PowerShell console if I'm not getting the results I expect in a third party PowerShell tool.I think you've got the DIFF pipeline basics working if you follow my example. Tweak as needed.
User avatar
jhicks
Posts: 1789
Last visit: Mon Oct 19, 2015 9:21 am

parsing/comparing csv files

Post by jhicks »

I see what you're saying. let me dig again.
This topic is 15 years and 4 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked