Add Users To Alma From Excel file

AddUsersToAlmaFromExcel.ps1

<#

.SYNOPSIS

Creating an input file of users for uploading to Alma.
Details in https://developers.exlibrisgroup.com/blog/uploading-users-from-excel-file-using-powershell/

#>

Set-StrictMode -Version Latest

Add-Type -AssemblyName System.Web

$pathRoot = "C:\dirName\"

$template = Get-Content $pathRoot"AlmaUserXmlTemplate.xml" -Raw

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

$outFile = $pathRoot+"out.xml"
"<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
        $valueEscaped = [System.Web.HttpUtility]::HtmlEncode($value)
        $newUser = $newUser -replace $header, $valueEscaped
    }
    "$newUser" | out-file -filepath $outFile -append
}
"</users>" | out-file -filepath $outFile -append

$excel.Workbooks.Close()
$excel.Quit()  

# removing <phone> when <phone_number> is empty:
$XSLInputElement = New-Object System.Xml.Xsl.XslCompiledTransform;
$XSLInputElement.Load($pathRoot+"cleanEmptyPhone.xsl")
$XSLInputElement.Transform($outFile, $pathRoot+"out.cleaned.xml")

AlmaUserXmlTemplate.xml

<user>
	<primary_id>_primaryId_</primary_id>
	<first_name>_firstName_</first_name>
	<last_name>_lastName_</last_name>
	<contact_info>
		<phones>
			<phone preferred="true">
				<phone_number>_phoneNumber_</phone_number>
				<phone_types>
					<phone_type>mobile</phone_type>
				</phone_types>
			</phone>
		</phones>
	</contact_info>
</user>

cleanEmptyPhone.xsl

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:output omit-xml-declaration="yes"/>

    <xsl:template match="node()|@*">
      <xsl:copy>
         <xsl:apply-templates select="node()|@*"/>
      </xsl:copy>
    </xsl:template>

                <xsl:template match="phone[phone_number = '']"/>
</xsl:stylesheet>