Unanswered: Active Directory and Carriage Returns in streetAddress
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,
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'
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:
-- 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 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.
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.
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.
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
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.