Loading...

Tech Talk Live Blog

PowerShell and Exporting a Hash Value to a CSV File

Shawn Mellinger


Recently we made a change to our student account process. We are now automatically creating student accounts via a PowerShell script based on the information entered in the student identification system. This was pretty easy to accomplish since we have a similar PowerShell script to create our staff member accounts. The next logical step was to extend this automation. Currently, we have a few different external services that we can integrate using LDAP authentication. Most of them will create the user accounts, if one does not exist when the user first logs in. However, we have a few outside services that still require a CSV file upload to create the accounts before a user can even log in. Rather than have a staff member manually fill in this CSV file, why not have our PowerShell script do it for us?

Currently, the user information is stored in a hash table. This hash table contains many properties for the user that are used to create their AD account, Exchange account, enable Lync, and add them to groups. So getting that information should be as easy as piping the hash table to Export-csv, correct?

$NewUser | export-csv c:\export\blog.csv

Here is what the CSV file contains. It exported information about our object, but not usable data.

#TYPE System.Collections.Hashtable

“IsReadOnly”,”IsFixedSize”,”IsSynchronized”,”Keys”,”Values”,”SyncRoot”,”Count”

“False”,”False”,”False”,”System.Collections.Hashtable+KeyCollection”,”System.Collections.Hashtable+ValueCollection”,”System.Object”,”23″

The next logical step would be to enumerate the hash table so that the properties are each objects and can be exported. The following command can be run, which enumerates and sorts the objects.

$newuser.GetEnumerator() | sort-object Name

When this is run, it returns two columns for Name and Value. The properties and the associated values are all listed. This can be exported to CSV and should return the data.

$newuser.GetEnumerator() | sort-object Name | export-csv c:\export\blog.csv

This returns the following CSV output which is abbreviated here.

#TYPE System.Collections.DictionaryEntry

“Name”,”Key”,”Value”

“ArchiveFlag”,”ArchiveFlag”,”N”

“Assessment”,”Assessment”,”N”

As you can see it returned the correct data. However, this format will make it really difficult to export multiple students’ information. The column names are just Name, Key, and Value which will not let us import the data in a meaningful fashion.

The next step is to make a variable that holds an object for each hash table property. This can be accomplished by using the following command.

$Output += New-Object PSObject -Property $NewUser

This results in formatting that is much friendlier. Each property is listed with its corresponding value. This can then be exported to CSV using the following command.

$output | export-csv C:\Export\blog.csv -NoTypeInformation

Finally, this provides the information needed in usable formatting. Each column corresponds with a property of the original hash table, and the row is the value of that property. This makes it easy to use export-csv –append to add additional students.

For most people this would be all that is needed. However, there are 2 different csv files that need to be exported and, of course, they both have strict column names. Neither format matches up exactly with the naming structure of the hash table properties. Thankfully, the labels of the columns can be changed while retaining their values.

$Output | select-object @{expression={$_.SitePasscode}; label=’Site Passcode’}, @{expression={$_.AccountName}; label=’Account Name’}, @{expression={$_.SiteName}; label=’Site Name’}, @{expression={$_.FirstName}; label=’First Name’}, @{expression={$_.LastName}; label=’Last Name’}, @{expression={$_.samaccountname}; label=’UserName’}, @{expression={$_.FakePassword}; label=’Password’}, @{expression={$_.Grade}; label=’Grade’}, @{expression={$_.TeacherID}; label=’Teacher ID’}, @{expression={$_.Email}; label=’Email’}, @{expression={$_.Assessment}; label=’Assessment Access Flag’}, @{expression={$_.ArchiveFlag}; label=’Archive Flag’} | export-csv $DiscoveryPath -NoTypeInformation -Append

As you can see in this command, I am taking the variable that contains all the objects created from the hash table. Now, select the object and set a corresponding label to that object. Then, when the $output object is piped back into export-csv it will use those labels as the column names instead of the object name.

A command will need to be set up for both CSV files that are exported to ensure that they have the specific column names that are requested for upload. All in all, this provides the exact output needed and it is automated daily. Future plans include adding a function to automatically upload those files via WinSCP.

Tech Talk Live Blog Comment Guidelines:

One of our main goals at Tech Talk Live is to build a community. It is our hope that this blog can be a forum for discussion around our content. We see commenting as an integral part of this community. It allows everyone to participate, contribute, connect, and share relevant personal experience that adds value to the conversation. Respect counts. We believe you can disagree without being disagreeable. Please refrain from personal attacks, name calling, libel/defamation, hate speech, discriminatory or obscene/profane language, etc. Comments should keep to the topic at hand, and not be promotional or commercial in nature. Please do not link to personal blog posts, websites, or social media accounts that are irrelevant to the conversation. This is considered self-promotion. We welcome links that help further the conversation and reserve the right to delete those we deem unnecessary. The appearance of external links on this site does not constitute official endorsement on behalf of Tech Talk Live or Lancaster-Lebanon Intermediate Unit 13. You are solely responsible for the content that you post – please use your best judgment. We reserve the right to remove posts that do not follow these guidelines.

Leave a Reply

Your email address will not be published. Required fields are marked *

CONTACT

Tech Talk Live is the only conference of its kind in the region specifically designed for IT pros in education.


techtalklive@iu13.org
1020 New Holland Avenue, Lancaster, PA 17601

(717) 606-1770