locked
Assign available Lync numbers from excel doc RRS feed

  • Question

  • Hi

    I have created a new user script which is working great, last piece of the puzzle is assigning the Lync number automatically.

    I have an excel/csv doc with the allocated Lync numbers and the free ones, what would be the easiest way to assign the free ones to a variable, i could have a table in the csv marked 'unassigned', or if it could just select the next free one based on the name table being empty that would be great, also would it be easy to update the csv again with the new user name from a variable.

    Thx

    John



    Sunday, June 17, 2018 5:32 PM

All replies

  • There is no way to answer your question.  Are you asking how to read an Excel document?  Are you asking how to determine a free "Lync" number?

    We cannot guess at what you want to do.  Post your code and any complete errors.


    \_(ツ)_/

    Sunday, June 17, 2018 5:42 PM
  • Hi 

    Sorry not very well explained!

    I am trying to work out how to get a free Lync number from the below which is a csv/xls file, and use it as a variable in my new user script, i don't have any code at all for this part as i have no idea where to begin apart from importing the csv.

    I would like to search the csv, find the next blank in the name column and then use the +44number and EXT as variables.

    +44 40 1234 9641	+44 1234 71 9641	9641	User 1 		+44123409641	
    +44 40 1234 9642	+44 1234 71 9642	9642			+44123409642	
    +44 40 1234 9643	+44 1234 71 9643	9643			+44123409643	
    +44 40 1234 9644	+44 1234 71 9644	9644	User2		+44123409644	
    

    Hopefully that explains it better

    Sunday, June 17, 2018 5:51 PM
  • Sorry not very well explained!

    That did not really change with this post ...  ;-)

    What's the problem importing your csv file with Import-CSV and filter the elements with Where-Object where the "user" column is empty.

    If you have problems with your code you should show your code.


    Best regards,

    (79,108,97,102|%{[char]$_})-join''


    • Edited by BOfH-666 Sunday, June 17, 2018 11:03 PM
    Sunday, June 17, 2018 11:02 PM
  • Oh well, i'm trying...

    Like i said i don't have any code for this part, the problem is i don't know anything about the where-object command, my PS experience is about 4 days in, but i will go read up and see what i can make happen, thx for the pointer.

    Monday, June 18, 2018 8:35 AM
  •  my PS experience is about 4 days in,

    You could have mentioned that in your initial post. I would recommend to learn the basics of Powershell from scratch. That's even free of charge and slightly entertaining with the free Microsoft Virtual Academy course Getting Started with Powershell. That would save you a lot of wasted time and frustration I think.

    Best regards,

    (79,108,97,102|%{[char]$_})-join''

    Monday, June 18, 2018 11:00 AM
  • Thx, i will get stuck in to that.
    Monday, June 18, 2018 3:41 PM
  • So just to follow up with my results, i ended up with

    $txt = import-csv $env:temp\lynctemp.csv
    $txt2 = $txt | Where-Object { $_.'Allocated to' -eq "$null" } | get-random | % {
    $_.'Allocated to' = $_.'Allocated to' -replace "", "$giv $sur"

    }

    I watched a fair bit of the Virtual Academy, but got a bit bored of it, by that time i had picked up a lot of great tips though.

    Tuesday, November 20, 2018 8:51 PM
  • So just to follow up with my results, i ended up with

    $txt = import-csv $env:temp\lynctemp.csv
    $txt2 = $txt | Where-Object { $_.'Allocated to' -eq "$null" } | get-random | % {
    $_.'Allocated to' = $_.'Allocated to' -replace "", "$giv $sur"

    }

    I watched a fair bit of the Virtual Academy, but got a bit bored of it, by that time i had picked up a lot of great tips though.

    Keep going back to MVA.  Do all examples and exercises.  Don't do more than on section a day.  It will do more than just give you tips.


    \_(ツ)_/

    Tuesday, November 20, 2018 8:53 PM