PowerShell, Excel and AD stuff

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.

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).