Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

    Unanswered: Active Directory and Carriage Returns in streetAddress

    Hey peeps.

    We have a program that modify AD Users via information stored in a csv file and is well... Except when it comes to the streetAddress column.

    The data is being pulled from a table with the following structure
    Branch(BranchID, AddressLine1, AddressLine2, AddressLine3, City, PostCode)
    And the problem lies in putting all 3 address lines into AD separated by carriage returns.
    It's easy enough to produce something like "123 Fake Street, TestLand, Down South" but it's been requested that we separate it by carriage returns as well as commas...
    "123 Fake Street,
    TestLand,
    Down South"

    I've tried using escape characters (\OD \n \r \n/ etc) to no avail. Any suggestions are more than welcomed because I'm at a complete mental block on what to try next!

    It may be worth noting that when you export the data in CSV from this field, if there are no carriage returns then you get a lovely "123 Fake Streed, Testland, Down South" but a single carriage return sends the things something like this X'12324153414142'

    What can be done?!
    E-hugs for a solution
    George
    Home | Blog

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I do believe this is a post bump



    George
    Home | Blog

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Depending on which language you are using to process your string, the syntax can be a wee bit gnarly, but the general concept from a Microsoft SQL syntax would be:
    Code:
    --  ptp  20070802  Code snippet to show how to "inject" a pseudo-newline in MS-SQL
    
    DECLARE @c		NVARCHAR(100)		-- Sample string
    DECLARE @cCommaCRLF	NVARCHAR(3)		-- editing string containing a comma, Carriage Return, and Line Feed
    
    SET @cCommaCRLF = ',' + Char(13) + Char(10)
    SET @c = '123 Fake Street, TestLand, Down South'
    
    SELECT @c
    SELECT Replace(@c, ',', @cCommaCRLF)
    Note that in order to see and understand this, you almost always need to be running in some flavor of "text" mode, not using a grid of any kind. Nearly all grid components will fail to properly display the newline(s) within the string. This is a "feature" of the grid component/controls that drives low level guys like me almost mad until we discover that we're fighting a "feature" instead of a bug in our code!

    There are ways to condition the grid components/controls to properly display newlines, but nearly all of them have some special "magic incantation" to do this, I don't know of any generic way to get grids to actually show you what a newline looks like.

    -PatP

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Thanks for the reply Pat - I shall have a play with the above code on the test system. Unfortunately I have my doubts, but only because I am using a CSV file and carriage returns in this instance indicate the end of a row.

    There are other places in AD where I have been able to escape characters using a forward slash preceeding the character to escape - but I've tried all the combinations of \r and \n and even tried \OD \n/ \| and a few others, but to no avail so far.

    Perhaps I need to change my row delimiter to a semi-colon? That just depends on whether I can trick the import program to let that slide.

    Many thanks,
    George
    George
    Home | Blog

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Harumf...
    Nope, it did not work!
    I even tried changing the row delimiter, but guess what - you can't!

    Back to the drawing board (again)!
    George
    Home | Blog

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Do you have better control of the CSV file's export than you do of the AD import? If so, you can probably use something like the pipe character or some other character or character sequence that is highly unlikely to appear in your file to represent where you want the newlines to appear. Then you can massage the imported data in either your existing import program (doing the string replace on the fly there), or even in a separate program after your import runs.

    Just an observation, but Microsoft actually makes a tool that does exactly what you are describing (and much, MUCH more) called MIIS that makes this kind of manipulation almost trivial. There is also a Yahoo group for MIIS that has regular visitors that probably already know six dozen ways to do this.

    -PatP

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Semi-colons are the default sub-delimiter, and unfortunately it's something that I've tried and it's failed.

    Thanks for the suggestion on MIIS but it doesn't quite do what we want (or so I was told). It was one of the possible purchases which were discovered.
    Plus the price tag of the software we bought was next to nothing
    George
    Home | Blog

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Too bad about the semicolons.

    Just FYI, if MIIS can't do something, it is because either the source or destination can't support what you want done... MIIS is capable of doing far more than AD, Exchange, SQL Server, PeopleSoft, SAP, Oracle, or *ix can support. I've never seen (and frankly can't imagine) any case where MIIS was the "weak link" in a process.

    -PatP

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I think, for now anyway, that I'm going to have to admit defeat on this issue!
    If I ever find a fix then I will post it right back here!

    Thanks for the help Pat
    George
    Home | Blog

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This is just a thought, and not a really pretty one at that...

    You could scan your AD values (possibly using a follow-on script) for a semi-colon that is not followed by a CRLF and if you find one then add the CRLF after it.

    -PatP

  11. #11
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Pat probably knows this, but for the benefit of others, we found that MIIS is being discontinued...as a name, anyway. Now it is ILM (Identity Lifecycle Manager).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •