Tech Blog

Uploading users from Excel file using PowerShell

PowerShell is installed by default on Windows 10 and can read an Excel file with a few lines of code. It can be used as a shell, replacing Window’s CMD, and can also run scripts written in ps1 files.

Our input will be an Excel file with one sheet, with headers in its first line, and users starting from the 2nd line:

We’ll read the headers in the first line, find the place holders in an XML template file

and replace the place holders, according to the table headers, user by user.

Below is the full code. Just save it as AddUsersToAlmaFromExcel.ps1 and double-click it:

$pathRoot = "C:\dirName\"

$inputUserTemplate = $pathRoot+"AlmaUserXmlTemplate.xml"
$template = [IO.File]::ReadAllText($inputUserTemplate)

$inputExcel = $pathRoot+"input.xlsx"
$excel = New-Object -Com Excel.Application
$sh = $excel.Workbooks.Open($inputExcel).Sheets.Item(1)
$colMax = ($sh.UsedRange.Columns).Count

$outFile = $pathRoot+"out.txt"
"<users>" | out-file -filepath $outFile

# read 1st line (headers) into an array:
$colHeaders = @()
for ($intCol = 1 ; $intCol -le $colMax ; $intCol++) {
    $colHeaders += $sh.Cells.Item(1,$intCol).Text
}

# read the rest of the lines:
for ($intRow = 2 ; $intRow -le ($sh.UsedRange.Rows).Count ; $intRow++) {
    $newUser = $template
    Write-Host "line $intRow"
    for ($intCol = 1 ; $intCol -le $colMax ; $intCol++) {
        $header = "_"+$colHeaders[$intCol-1]+"_"
        $value  = $sh.Cells.Item($intRow,$intCol).Text
        $newUser = $newUser -replace $header, $value
    }
    "$newUser" | out-file -filepath $outFile -append
}
"</users>" | out-file -filepath $outFile -append

$excel.Workbooks.Close()
$excel.Quit()  
echo "Press any key to close"
cmd /c pause | out-null

The output is a file ready for SIS import.

If you like the script to send POST API requests to create the users, after

"$newUser" | out-file -filepath $outFile -append

add this line:

Invoke-WebRequest -Uri "https://api-eu.hosted.exlibrisgroup.com/almaws/v1/users?apikey=yourApiKeyWithWriteAccessToUsersArea" -Method POST -Body $newUser -ContentType "application/xml; charset=utf-8"

3 Replies to “Uploading users from Excel file using PowerShell”

  1. Dear Ori,

    Your post is very informative. By the way, I read another blog by Yoei on “how to use an API to edit a user record”. If I have more user records to be edited, wonder if your method can be applied to do so?

    Best,
    Simon

  2. Thanks you Simon.
    If the Excel file has many users, the output XML file will include them all. You can then use the file for SIS Sync and users will be created/updated.
    If you meant to use the REST API, I used “POST” above to create users. Updating users should be done with “PUT” but it might be complicated because you would want to run GET before PUT and merge the data which exists in Alma with the data you have in the Excel file.

  3. Greetings Ori

    I changed your script to handle empty fields.

    In the example below I changed the script and XML to test for an empty email:

    1. In “AlmaUserXmlTemplate.xml” change this section:

    _email_address_

    personal

    For this line:

    _email_address_

    2. In “AddUsersToAlmaFromExcel.ps1”
    Between:
    $value = $sh.Cells.Item($intRow,$intCol).Text

    And:
    $newUser = $newUser -replace $header, $value

    Add these lines:
    #When a mandatory field is empty
    if ($colHeaders[$intCol-1] -eq “email_address”)
    {
    if ($value -eq “”)
    {
    $value = “”
    $newUser = $newUser -replace $header, $value
    }
    else
    {
    $value = “”+$value+”personal”
    $newUser = $newUser -replace $header, $value
    }
    }

    You can make the same changes for empty address, phones and user identifiers.

    Additionally, you can remove empty fields like middle_name by adding this line:
    $newUser = $newUser -replace “”, “”

    After this line in the script:
    $newUser = $newUser -replace $header, $value

Leave a Reply