smh.  I thought this might help some other folks, maybe.  Maybe not.  Anyhow…


Fetch the heading row from a CSV file and strip off those which contain a “_” (underscore) prefix, and those which are null (hint: when saving out of Excel as CSV UTF-8, or many mainframe apps, there is often trailing ,,,,,,,,,,, stuff on each row, even the heading row.  In fact, you can find rows of nothing but ,,,,,,,,,,,,,,,,,,,,,,,,,, at the end of the file, due to poorly written application features, but wtf).

This allows for making a custom spreadsheet that has a mix of column names you want and those you want to ignore (again, just using underscore prefix).  This example fetches only the column names which are valid AD user account attributes.  You can modify this however you want.

function Get-UserAttributes {
  param (
    [string] $InputFile 
  Write-Verbose "[Get-UserAttributes]"
  $attlist = @()
  $csvRaw = Get-Content -Path $InputFile
  # filter out columns with underscore prefix, null columns created by Excel output to CSV
  $attlist = $csvRaw[0].ToLower().Split(",") | ? {$_ -notlike "_*"} | ? {$_ -ne ""}
  $attlist = $attlist | ? {$_ -ne "samaccountname"} | ? {$_ -ne "name"} | ? {$_ -ne "path"}
  return $attlist

Notice I’m leaving out SamAccountName and Path, which is just due to this one particularly weird, glue-sniffing, drano-swilling, squirrel-roasting, car-chasing mess of a task on a messy project.  I fetch those from the actual CSV import.

So, to clarify (putting down the glue and the squirrel for now), this fetches the column names as one array, and fetches the rest of the data as one big chunk via Import-Csv.  But when iterating the big chunk, I only read the columns which are members of the Attributes array.  Sort of like letting anyone into the party, but only giving backstage passes to the ones with the weirdest costume.

Also, the “_LOAD” column, which controls whether the row should be processed (1) or ignored (not 1), and the $RowLimit parameter allows for limiting the CSV input to just the first N rows (or zero for unlimited).


$csvFile = "ADUsersList.csv"
$RowLimit = 0
if (!(Test-Path $csvfile)) {
  Write-Host "error: $csvfile not found"
else {
  $rowNum = 1
  $attlist = Get-UserAttributes -InputFile $CsvFile
  $attcount = $attlist.Length
  Write-Verbose "info: $attcount attributes returned"

  Write-Verbose "info: reading input data file..."
  $csvData = Import-Csv -Path $CsvFile | ? {$_._LOAD -eq 1}
  if ($RowLimit -gt 0) {
    $csvData = $csvData[0..$($RowLimit-1)]
  $csvRows = $csvData.Count
  Write-Verbose "info: loaded $csvRows entries"
  Write-Verbose "info: processing accounts..."
  foreach ($row in $csvData) {
    $sam = $row.sAMAccountName
    $upath = $row.PATH
    $uname = $sam
    Write-Verbose "info: [$rowNum] user = $sam"
    Write-Verbose "info: [$rowNum] path = $upath"
    ... do magical stuff here...

That’s it for now.  The script is much bigger and uglier, just not as ugly as I am, so it gets a weekend pass.

Anyhow, I hope it helps.  Now, where’s that squirrel hiding?…


Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s