Convert Phone Numbers For Use In Lync

Having done my fair share of Lync Implementations, I find that there are so many little tasks that take time, and one of the biggest time-bandits (nod to Terry Gilliam) is sort and converting data. Not the least of which is the conversion of telephone numbers, which when given to me from clients can come in any sort of format. It's always my goal to automate whatever I can, and I always bulk add users from a csv. It just makes sense. That said, getting the proper information into a csv in the proper format can be a real pain in the rear. Today's post will share a script I use to take a phone number provided to me in almost any format, normalize it to a string of numbers only, then format it into the LineURI and Display Number for Lync. PowerShell is really easy to use for such tasks.

So, the first task is to input your data as provided. I really wanted to modify an existing csv by adding 2 empty columns if they didn't already exist: LineUri and DisplayNumber. Doing that programmatically is a huge pain (at least as far as I can figure it out). So, once you have your CSV modified, You can begin.

Typically, I require my CSVs to have the user's Display name, ADName, and DID at a minimum. If you are building out different policies, you may want to have a column for each policy (Client, Conferencing, Archiving, Mobile, External Access, etc.), it really depends on what you are scripting. For me, I am a huge proponent of automating any task.

A truer diagram has never been made. I'm not sure who created the above diagram, but I found it here. Anyway, Back to the subject.

Once your CSV is ready, time to run the script. It first will ask for the CSV file to get the content from:

PS C:\Users\me\SkyDrive\Area51\Playground> C:\Users\me\SkyDrive\Area51\Playground\Convert-DIDtoUriDisplay.ps1
Enter file path for conversion: 

Enter your CSV file name, and hit enter:

PS C:\Users\me\SkyDrive\Area51\Playground> C:\Users\me\SkyDrive\Area51\Playground\Convert-DIDtoUriDisplay.ps1
Enter file path for conversion: test.csv
....................................................................................................

Conversion complete. Please look in C:\Users\me\SkyDrive\Area51\Playground\Conversionresults.csv for results.

That's all you need do, you now have a new CSV file in the local directory called Conversionresults.csv that will have the LineURI and DisplayNumber columns populated, and leave all other columns from your original CSV untouched. It will carry over any columns you had in the original. 

Below is a representation of the original CSV opened in Notepad:

Name,DID,LineURI,DisplayNumber

Aileen Smith,1 (123) 456-7890,,

Amy Jones,1 (123) 456-1111,,

Angel Johnson,1 (123) 456-1112,,

Adam Garcia,1 (123) 456-1122,,

Conference Room 1,1 (123) 456-9999,,

Switchboard,1 (123) 456-0000,,

Reception Copy,,,

Note the Reception Copy entry doesn't have any DID info. This is to demonstrate that if the DID field is empty for a user, the script will insert "TBD" in the DID, LineYri and DisplayNumber fields for that user in the output. 

After the script has completed, the CSV looks like:

I will be working on the next version that will be more flexible with the extension bit in the LineURI. I have clients that don't use it at all, others that use the last 4 digits of their DID (as shown here) and some use other, non-DID related numbers. I'll be adding logic to make some choices with those in mind. If you need to modify the current script to accommodate your needs, you really only need to worry about changing one line, Line 41, of the script. To not use and ";ext=" just comment or delete that code: 

Take the code:  $item.LineURI = "tel:+" + $DashFree + ";ext=" + $DashFree.substring(7) 

insert a "#" as shown: $item.LineURI = "tel:+" + $DashFree #+ ";ext=" + $DashFree.substring(7)

this will comment out that portion of the line. 

To set the extension as, say, the last 3 digits of the DID, but add a digit to the beginning (in this case, a 2), say to designate a location:

Take the code:  $item.LineURI = "tel:+" + $DashFree + ";ext=" + $DashFree.substring(7) 

Modify it like so:  $item.LineURI = "tel:+" + $DashFree + ";ext="  + "2" + $DashFree.substring(8) 

I hope this post proves to be of some assistance to some of you out there. 

The Script:

<#

 NAME:  Convert-DIDtoUriDisplay.ps1

 AUTHOR: Sean William McNamara

 EMAIL: sean.mcnamara@outlook.com

 COMMENT: Input a CSV file path, the script converts a field called DID, formated in most phone number

            formats, into a field called LineURI in a format like: tel:+19182345566;ext=5566 AND a field 

            called DisplayNumber in a format like: 1(918)234-5566. It will then output 

            the resultant CSV called "LineURIConversionresults.csv" to the current working directory.

            This file can then be used to populate a script to enable EV users.

            The input csv must have 2 columns called LineURI and DisplayNumber to convert. 

            

NOTE: This script assumes US locale. If using for a locale with more than single digit country code, 

        modifications to string manipulation code will be needed.

          

      

 You have a royalty-free right to use, modify, reproduce, and

 distribute this script file in any way you find useful, provided that

 you agree that the creator, owner above has no warranty, obligations,

 or liability for such use.

 VERSION HISTORY:

 1.0 2/4/2014 - Initial release (no regex matching, that would make this much more flexible)

 2.0 1/5/2015 - Added DisplayNumber and RegEx Matching. Will now take almost any phone number format

                as DID and convert.

#>

$InFile = Read-Host 'Enter file path for conversion' 

$filedata = ipcsv $InFile

ForEach ($item in $filedata){

    If ([string]::IsNullOrWhiteSpace($item.DID)){

        $item.DID = "TBD"

        $item.LineURI = "TBD"

        $item.DisplayNumber = "TBD"

    }

    Else{

        #format number for manipulation (remove all non numbers)

        $DashFree = $item.DID

        $DashFree = $DashFree  -replace "[+).( -]"

        $item.LineURI = "tel:+" + $DashFree + ";ext=" + $DashFree.substring(7) #if not using the ext= portion, comment it out.

        $item.DisplayNumber = $DashFree.substring(0,1) + "(" + $DashFree.substring(1,3) + ")" + $DashFree.substring(4,3) + "-" + $DashFree.substring(7)

        Write-host "." -NoNewline

    }

}

#$filedata | select Name, DID, LineURI, DisplayNumber | FT

$filepath = Get-Location

Write-Host ""

Write-Host ""

Write-Host "Conversion complete. Please look in $filepath\Conversionresults.csv for results."  -foregroundcolor Green

$filedata | Export-csv -NoTypeInformation .\Conversionresults.csv