Active Directory and Carriage Returns in streetAddress

gvee · #1 (**permalink**) 07-31-07, 11:38

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

gvee · #2 (**permalink**) 08-01-07, 18:13

I do believe this is a post bump

Pat Phelan · #3 (**permalink**) 08-02-07, 22:33

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

gvee · #4 (**permalink**) 08-03-07, 03:46

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

gvee · #5 (**permalink**) 08-03-07, 05:44

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)!

Pat Phelan · #6 (**permalink**) 08-03-07, 08:24

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

gvee · #7 (**permalink**) 08-03-07, 09:12

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

Pat Phelan · #8 (**permalink**) 08-03-07, 09:24

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

gvee · #9 (**permalink**) 08-06-07, 03:59

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

Pat Phelan · #10 (**permalink**) 08-06-07, 08:44

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

MCrowley · #11 (**permalink**) 08-08-07, 10:56

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).

Internet Services

Web Development

Digital Marketing

Consumer Tech