Powershell script to compare excel to AD

spiciestit · 2021-11-17T19:21:41.000Z

Hello,

I am trying to get an idea of where to start on a project.

I want to write a script that will read a certain column of a csv file and compare it wit users in AD then export or write to host a file to see of they are enabled or disabled users and what OU they are in.

I have a massive long roster to go through every week and it has employees that have been terminated and it has a column that has a T identifier in it. Instead of searching through what may be 200 or more lines I want to be able to have it grab every user with that T in the Colum and compare that to what is in AD (as far as in an enabled or disabled account, the have it write host or csv the findings) that then later go as far as to disable and move users via the script.

Im using Googlefu as well to find ways to do this.

Neally · 2021-11-17T19:29:50.000Z

SpiciestIT:

has a column that has a T identifier in it

Can you give a sample what the file looks like?

To import excel files, use the ’ importExcel’ module by Doug Finke

if it’s a CSV, something like so?

$csv = import-csv $filepath

$csv | 
where-object {$_.ColumnName -like "*T*"} |
foreach-object{
    try{
        Get-aduser $_.columnName -ErrorAction Stop
    }
    Catch{
        $error[0].exception.message
    }
}

spiciestit · 2021-11-17T19:36:53.000Z

Right now I have the following.

It is still crude.

load file

$FilePath = ‘jojo set file path’
$xl = New-Object -ComObject Excel.Application
$xl.Visible = $true
$wb = $xl.Workbooks.Open($filepath)

get data from column 6

$data = $wb.Worksheets[‘Sheet1’].UsedRange.Rows.Columns[6].Value6

Neally · 2021-11-17T19:39:41.000Z

If you post code, please use the ‘Insert Code’ button. Please and thank you!

PLEASE READ BEFORE POSTING! Read if you're new to the PowerShell forum! Programming & Development

Hi, and welcome to the PowerShell forum! Don’t apologize for being a “noob” or “newbie” or “n00b.” There’s just no need – nobody will think you’re stupid, and the forums are all about asking questions. Just ask! Use a descriptive subject. Don’t say “Need help” or “PowerShell Help”, actually summarize what the problem is. It helps the rest of us keep track of which problem is which. Don’t post massive scripts. We’re all volunteers and we don’t have time to read all that, nor will we copy…

192033ab-bb8f-4032-88a5-8e2313af0344-codebutton_small.png

Neally Bot

Neally · 2021-11-17T19:42:00.000Z

SpiciestIT:

Right now I have the following.

It is still crude.

load file

$FilePath = ‘jojo set file path’
$xl = New-Object -ComObject Excel.Application
$xl.Visible = $true
$wb = $xl.Workbooks.Open($filepath)

get data from column 6

$data = $wb.Worksheets[‘Sheet1’].UsedRange.Rows.Columns[6].Value6

Yeah… that’s using com-objects and rather painful, as mentioned, use the ‘importExcel’ module by Doug Finke

# install the ImportExcel module
Import-Module ImportExcel

$file = import-Excel $pathToFile -WorkSheet $worksheetName