Import multiples data of column from xlsx to a powershell cmd

Question

In input : i have an \users\myself\desktop\test\file.xslx containing multiples column like this :

ColumnA ColumnB ... ColumnQ (for a total of 17 columns)

each column have some data.

In output :

I would like to have a cmd like this :

New-ADUser -Name $(columnAdata) -GivenName "$(columnBdata)" -Surname "$(columnCdata)" -DisplayName "$(columnDdata)" -SamAccountName "$(columnEdata)" ... etc until -blabla "$(ColumnQdata)"

Is that possible to store de columndata in variables to insert them in a command ?

Thanks a lot.

Answer 1

I would suggest to first change the column headers to be the same as the parameters you intend to use with the New-ADUser cmdlet.
Having matching headers would help greatly in not making mistakes.

Next, save your Excel file as CSV, let's say a file called NewUsers.csv

The code then can be quite simple and easy to maintain:

# import the CSV file using the same separator character as Excel uses on your system
Import-Csv -Path 'X:\NewUsers.csv' -UseCulture | ForEach-Object {
    # use Splatting: create a Hashtable with all properties needed taken from the CSV
    # see: https://learn.microsoft.com/en-us/powershell/module/microsoft.powershell.core/about/about_splatting
    $userProperties = @{
       Name           = $_.Name       # as opposed to $_.columnAdata
       GivenName      = $_.GivenName  # as opposed to $_.columnBdata
       Surname        = $_.Surname
       DisplayName    = $_.DisplayName
       SamAccountName = $_.SamAccountName

       # etcetera
    }
    New-ADUser @userProperties
}