Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

AD Bulk Import Multiline

Options
  • 14-11-2015 10:48am
    #1
    dpjmie
    Registered Users Posts: 263 ✭✭


    Hi Guys,

    Has anyone ever bulk imported using a csv file to Active Directory, which has a multiline attribute. An example of this is the street address. Say you want it to be:
    Apartment 11a,
    O Connell Street,
    Dublin,
    Ireland.

    I have tried encapsulating it all in double quotes "" and escaping and using `r`n but it just prints the characters or closes the object.

    Its such a simple thing, I've read about having to break it out into an array but that seems like overkill. Also to note we are using Windows Server 2012.

    Any help is greatly appreciated
    Tagged:


Comments

  • Options
    #2
    Fysh
    Moderators, Arts Moderators, Regional Abroad Moderators Posts: 11,016 Mod ✭✭✭✭Fysh


    dpjmie wrote: »
    Hi Guys,

    Has anyone ever bulk imported using a csv file to Active Directory, which has a multiline attribute. An example of this is the street address. Say you want it to be:
    Apartment 11a,
    O Connell Street,
    Dublin,
    Ireland.

    I have tried encapsulating it all in double quotes "" and escaping and using `r`n but it just prints the characters or closes the object.

    Its such a simple thing, I've read about having to break it out into an array but that seems like overkill. Also to note we are using Windows Server 2012.

    Any help is greatly appreciated

    There isn't a one-line way to do this because AD doesn't assign user objects a single "postal address" attribute, they get a collection of attributes like City, Postal Code etc which collectively constitute the postal address for that object.

    Having said that, it's not going to be that difficult to make it work. Be careful about the delimiter you use for the CSV, because if you're using comma as your delimiter and also have commas in your multiline postal address (as in your example) the values read from the CSV may not be split as you would expect. (Powershell will let you import your CSV specifying any delimiter you choose - the bigger question is whether you can get the relevant change made to your source CSV file).

    You may also need to give some thought to the formatting of your addresses, because in your above example, "Apartment 11a, O'Connell Street" is what you want as the StreetAddress attribute, but it spans two lines of the input file. It's probably possible to do something with regular expressions or per-line conditional tests for the addresses to determine where to split each attribute, but whether it's worth the time to make it work depends on the size and variety of your input files.

    Once you've got that, use Set-ADUser and the various switches for each part of the address to update the account. Then just wrap that in a foreach loop to iterate through every line in the CSV file and that's it done. You need the AD PowerShell provider installed for this cmdlet to be available.


  • Options
    #3
    Fysh
    Moderators, Arts Moderators, Regional Abroad Moderators Posts: 11,016 Mod ✭✭✭✭Fysh


    Right, I had a quick look at this and it's not as bad as I thought. If Excel recognises your multiline address as a single value it'll import that way in PowerShell, so no worries about the delimiter. Which then means you can use something like this as a first iteration: 
    foreach ($r in $csv) {
$addr=$r.Address
$count=($addr.split("`r`n")).count
$country=$addr[$($count -1)]
$city=$addr[$($count -2)]
for ($i=$($count -3); $i -ge 0; $i--) {
    $streetaddr=$addr[$($count -$i)]
    $streetaddr+=", "
}
$streetaddr=$streetadr.TrimEnd(", ")
Set-ADUser -Identity {username} -StreetAddress $streetaddr -City $city -Country $country

    Now, this assumes that the format you outlined above holds throughout ie starting from the bottom you have Country, City, Street Address. If you've also got some addresses with counties in them, you want to add in some positional logic like checking if the second last value in the array starts with "Co." or "County". If you need to do this what you'd probably be better off decreasing the value of the $count variable after you've set each variable and build it up that way.

    Hope this helps - let me know if you have trouble getting the above to work for you.


  • Options
    #4
    Ctrl Alt Del
    Registered Users Posts: 357 ✭✭Ctrl Alt Del


    Hi,

    I've posted a link to software that does all the hard script work much easier and with much more fun !
    It disappeared...
    Is some censorship around here,please let me know so that i'll stop wasting my time if i "offend" some body(s) !

    Regards


Advertisement