Ldap extract to ldif file - how to extract from a multivlaue field 1

[Kate1020]

I am trying to write a crystal report against an ldap extract. (ldif file) I have imported the file in excel. My problem is that the fileds of Groups and Roles have multiple values and I don't know how to extract each value and put it in a column. I want to list the user and in one column give a list of the users groups and in a second column give the list of the users roles. This can probably be done directly in the excel file or in Access. I've gone into help for crystal, excel and Access and I still don't know how to do this. I am formula writing impaired.

Any help is much appreciated. I don't care if I have to do this in crystal, excel or access.

Thanks
Kate

 

[lbass]

We need to see sample data. Please show us a sample of what your data looks like in Excel, with column headings and sample data. Then show us how you want it to look.

-LB

 

[Kate1020]

I can't upload a file or paste a picture.
In excel the data looks like this. This is in one field/cell. A space separates each value.

"FinRegionalController PRODAccess LawsonQueryToolsRole
RptGenRole REQUESTER RSSAPPROVER PortalBookmarkAdminRole
BatchRole"

I need it to to have one value on each row. This is a user audit report and each value must be ticked off as either okay, change or delete. This can't be done when the data is all scrunched in one cell.
Should look like this.

FinRegionalController
PRODAccess
LawsonQueryToolRole
RptGenRole
REQUESTER
RSSAPPROVER
PortalBookmarkAdminRole
BatchRole


Thanks so much for your help.

Kate

 

[lbass]

In Crystal, you could insert additional detail sections, a, b, c, etc., and then add a formula to each section like this:

//{@deta}:
stringvar array x := split({table.field}," ");
if instr(x, " ") <> 0 then
x[1] else
{table.field}

//{@detb}:
stringvar array x := split({table.field}," ");
if ubound(x) >= 2 then
x[2]

//{@detc}:
stringvar array x := split({table.field}," ");
if ubound(x) >= 3 then
x[3]

//etc., up to the maximum number of elements in the array. Not sure how the user field fits in since you didn't show it.

-LB

 

[Kate1020]

Thanks LB I'll try this. It Should look like this.

User Groups
MHalle FinRegionalController
PRODAccess
LawsonQueryToolRole
RptGenRole
REQUESTER
RSSAPPROVER
PortalBookmarkAdminRole
BatchRole

The only problem that I can see is that there is no max # of values or elements that can be in a field so I won't know how many detail areas to add. Some users can have one group and some can have multiples. I really appreciate your help. Thanks again.

Kate

 

[lbass]

You should format each section to "suppress blank section." There must be some logical maximum that you can use, i.e., you might conclude that there wouldn't ever be more than 20 roles.

-LB