Hashtable Import/Merge/Export Powershell

This is something I've attempted to do before, but did not succeed.  Now I'm asking for something pretty interesting and I haven't seen much written about it on Google or EE.

Simply put, I want to make a hash table with values like "John Smith", "2" and "Jane Doe", "4".  It starts by taking a survey of all the exchange mailboxes and whoever is over a certain amount of MBs, flags that user name and adds it to a temporary list.  This can be an array since it's only a bunch of names.  No numbers yet.  Here's where it gets interesting.  There will be a text file or csv, whichever is easier in a location accessible by the script.  Let's call it "C:\HashTable.csv".  It imports all the values from Hashtable.csv into a hashtable.  Then it goes down the line and says "If a person exists in this hash table, increment their corresponding value by 1".  Otherwise, just add them to the list, and give the key a value of 1.  So the key is the persons name (because it's unique), and the value is how many days we've found them full on the server.

I tried to use a text file that outputs their names into it, and then counts it all up.  That works but after a while the text file gets so sloppy, it's hard to read it.  And it also pretty large after a few days.

Hopefully someone can cook something up.  The hardest part I'm having is exporting the hashtable.  Once I export it, I suppose it would be easy to import it.  And even easier to do a "Does key exist" on the table, then if they do, increment their value by 1.  Should be a cakewalk, right?

This has been something plaguing me for a few months and I shelved it because I got sidetracked with other projects.  So whoever can fix this will get my 2000 points + my undying gratitude free of charge.

-Dale Harris
LVL 17
Dale HarrisProfessional Services EngineerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bryan ButlerCommented:
How about the names in a DB and then querying to see how many days they are "full" using a select count(name) from table.  You could even add dates to the records for more details.  Another solution would be to store everyone in the DB and each record has the name and count.  You would add one to the count each time they show up in the daily log/csv.  
Bryan ButlerCommented:
Or I guess you could use a flat file for the latter solution too.
Dale HarrisProfessional Services EngineerAuthor Commented:
I can definitely see where you're going with it, but the purpose is to integrate it into my daily scripts.  I'm stuck with using tools native to PowerShell.

I'm also looking not just for a word answer, but actual code itself.
Discover the Answer to Productive IT

Discover app within WatchGuard's Wi-Fi Cloud helps you optimize W-Fi user experience with the most complete set of visibility, troubleshooting, and network health features. Quickly pinpointing network problems will lead to more happy users and most importantly, productive IT.

Bryan ButlerCommented:
So using a flat file as a DB would work.  Continuing some code I've posted on here, the below code will keep a csv flat file with each row starting with the names and a then a "1"(checkmark) for each time they were "full".  Sounds you want to just keep a running total, in this case you would just add some code, but first, is this in the right direction?  (untested)

$aryCounts = Get-Content ".\counts.txt" 
$aryNames = Get-Content ".\allNames.txt" 
$iter1=0
$iter2=0
do {
  do {
      [regex]::Replace($aryCounts[$iter1], $aryNames[$iter2], "$0") >> temp.txt
      ",1`n" >>temp.txt #add comma and another "1" meaning full, and a newline
      iter2++
  } until  ($iter -eq $aryNames.length)
 
   $iter1++
} until ($iter -eq $aryCounts.length)

Open in new window

Bryan ButlerCommented:
Ooops, this will give a "1" everytime...scratch that.  I think it would be:  

[regex]::Replace($aryCounts[$iter1], $aryNames[$iter2], "$0,1") >> temp.txt

Not sure if you need the newline.  If so...

[regex]::Replace($aryCounts[$iter1], $aryNames[$iter2], "$0,1`n") >> temp.txt
BSonPoshCommented:
I think your best option would be a custom object. You can then use import/export-csv to get the info in and out.

Something like below... you will need to add your own logic for what ever checks you want.
# Get old list of names from file
$oldnames =  import-csv oldlist.csv
 
# Collection to store custom objects
$myobjs = @()
 
foreach($name in $names)
{
   # Get old count
   $oldcount = $oldnames | ?{$_.name -eq $name} | %{$_.count}
 
   # create custom object
   $myobj = "" | Select Name,Count
   $myobj.name = $name
   $myobj.count = $oldcount + 1
}
 
$myobjs | export-csv oldlist.csv -noType

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale HarrisProfessional Services EngineerAuthor Commented:
BSonPosh,

I'm just about ready to head off of work.  Thanks for the tip.  I'll have to check it out tomorrow morning.  Don't give up on me yet :)

-Dale Harris
BSonPoshCommented:
no rush on my side :) take your time
Bryan ButlerCommented:
Excellent script.  Does he need to initialize $names?  Such as:

$names = Get-Content ".\allNames.txt"
BSonPoshCommented:
yes... There is a good bit that should be added, but without the entire source I cannot provide a complete script.

I was hoping to illustrate the options he has :)
Bryan ButlerCommented:
Options....that's the best way to go!  

That importcsv....very nice.
BSonPoshCommented:
Yes... import-csv and import-xml are lifesavers
Dale HarrisProfessional Services EngineerAuthor Commented:
BSonPosh,

I've almost got it figured out (thank you so much), but I'm having a couple issues.

You may have typed it a little hasty.

The logic behind the loop replaces the $myobj information on each iteration without exporting to a csv.

So the fix behind this would be to either
A) Append to the csv file (takes more time to do this) or
B) Create a little custom object array

I tried to make $myobj[2] = " " | Select Name,Count work, but it threw an error.

What can I do to make this work for me?

-Dale Harris
# Get old list of names from file
$oldnames =  import-csv oldlist.csv
 
# Collection to store custom objects
$myobjs = @()
 
foreach($name in $names)
{
   # Get old count
   $oldcount = $oldnames | ?{$_.name -eq $name} | %{$_.count}
 
   # create custom object
   $myobj = "" | Select Name,Count
   $myobj.name = $name
   $myobj.count = $oldcount + 1
}
 
$myobjs | export-csv oldlist.csv -noType

Open in new window

BSonPoshCommented:
whoops... forgot to add the custom object to my array


foreach($name in $names)
{
   # Get old count
   $oldcount = $oldnames | ?{$_.name -eq $name} | %{$_.count}
 
   # create custom object
   $myobj = "" | Select Name,Count
   $myobj.name = $name
   $myobj.count = $oldcount + 1
   $myobjs += $myobj     #### <==== added this
}

Open in new window

Dale HarrisProfessional Services EngineerAuthor Commented:
Getting the error message:

Method invocation failed because [System.Management.Automation.PSObject] doesn't contain a method named 'op_Addition'.
At line:1 char:11
+ $myobjs += <<<<  $myobj

So I'm thinking I'll have to do a force object type [something], or I'll have to do something other than just attempt to make $myobj get stored into $myobjs

-Dale Harris
Dale HarrisProfessional Services EngineerAuthor Commented:
BSonPosh,
 
Nevermind.  It worked once I had done the proper $myobjs = @() that you put in the top of the other script.  It's all good now.  I'll report back if I have any more issues.

Thanks.

-Dale Harris
BSonPoshCommented:
Hows this going?
Dale HarrisProfessional Services EngineerAuthor Commented:
BSonPosh,

Sorry to keep you waiting.  I finally implemented the solution and it's working as I had hoped.  I'm going to close this question out with the assumption that if I need help to modify it later, I'll be putting in another question.

Thanks again for all the help!

-Dale Harris
Dale HarrisProfessional Services EngineerAuthor Commented:
Thanks again!  Just to give you an update, I'm leaving in a couple months, so if this is my last question before I go, I definitely would like to give you an emphatic thanks and I'm amazed that you give away your services for free to people like me :)

With all of your help, I was able to implement various scripts to streamline my job and do you remember that script we worked on where I was trying to make someone's expiration date take effect through Set-QADObject that you gave me the final answer to?  Well, we ended up losing 1300 accounts one day due to an abominable Gal Sync (I was not doing said Gal Sync), and because of that script I made from help with you, my co-worker and I were able to recreate all 1300 accounts in 10 hours.  That would've taken probably two days without a script and probably a bunch more errors.  I wish I could tell you more about it, but it's classified as SECRET unfortunately.  Anyways, just know that you, Brandon Shell, contributed to the overall readiness of the 3rd Brigade Combat Team, 4th Infantry Division in FOB War Eagle, Iraq.  When my bosses thought we were sunk, my (your) script helped get us back into the fight.

I salute you.

-Dale Harris
BSonPoshCommented:
@Dale, I am glad I could help, but to compare what I do what you guys do on a daily basis it is a joke at best. You guys are true Heros. I imagine you guys often don't feel the appreciation from the ungrateful people back here running things, but I personally want to let you know. I appreciate you!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Powershell

From novice to tech pro — start learning today.