LDIFDE csv import to SQL - looking for Ideas

This is a tuffie, but I think I'll learn new techniques in SQL.
I wish to put data from MS Active Directory and put it into a table.
Specificly I want user information (first name, last name and so forth)
and the groups that they belong into a SQL table.
LDIFDE is a utility that can create a csv file from an AD server. This
is a sample output:

dn: CN=rob  camarda,OU=Corporate
,OU=Geographic
 Locations,DC=strayer
,DC=edu
changetype: add
objectClass: top
objectClass: person
objectClass:  organizationalPerson

objectClass: user
cn: rob camarda
givenName: rob
memberOf: CN=Arlington Admin,OU=Campus Domain  Admin,DC=strayer,DC=
edu
memberOf:
 CN=Arlington,OU=Arli
 ngton,OU=Region2,OU=
Geographic
 Locations,DC=strayer
,DC=edu
memberOf: CN=RN Report Consumers,OU=Cognos  ReportNet,DC=strayer
,DC=edu
sAMAccountName: rob.camarda

dn: CN=Robert A.  Camarda,OU=Technolog
 yGroup,DC=strayer,DC
=edu
changetype: add
objectClass: top
objectClass: person
objectClass:  organizationalPerson

objectClass: user
cn: Robert A. Camarda
givenName: Robert
memberOf: CN=Role Regional  Director,OU=Roles,DC
=strayer,DC=edu
memberOf: CN=Role Campus  Director,OU=Roles,DC
=strayer,DC=edu
memberOf:  CN=TLSAdmin,OU=Talis
ma- Users,DC=strayer,DC=
edu
memberOf:  CN=ASPTestReports,OU
 =Roles,DC=strayer,DC
=edu
memberOf: CN=IT Report  Authors,OU=Roles,DC=
strayer,DC=edu
memberOf:  CN=Developers,OU=Tec
 hnologyGroup,DC=stra
yer,DC=edu
memberOf: CN=SQL Backup  Admin,OU=TechnologyG
 roup,DC=strayer,DC=e
du
memberOf: CN=RN Report MetaData Modelers,OU=Cognos
 ReportNet,DC=strayer
,DC=edu
memberOf:
CN=RN  Corporate,OU=Corpora
 te,OU=Region2,OU=Geo
graphic
 Locations,DC=strayer
,DC=
edu
memberOf:
 CN=Arlington,OU=Arli
 ngton,OU=Region2,OU=
Geographic
 Locations,DC=strayer
,DC=edu
memberOf: CN=RN Administrator System,OU=Cognos
 ReportNet,DC=strayer
,DC=edu
memberOf: CN=RN Administrator Server,OU=Cognos
 ReportNet,DC=strayer
,DC=edu
memberOf: CN=RN Report Authors,OU=Cognos  ReportNet,DC=strayer
,DC=edu
memberOf: CN=Backup  Operators,CN=Builtin
,DC=strayer,DC=edu
memberOf: CN=Domain  Admins,CN=Users,DC=s
trayer,DC=edu
memberOf:  CN=Administrators,CN
 =Builtin,DC=strayer,
DC=edu
sAMAccountName: robert.camarda

In this output, each user is separated by a blank line. sAMAccountName
is the user's login ID to ADS. Lines starting with memberOf: shows the
path for each group the user belongs.

My thought is to load the text data into a SQL table with the PK being
the line number. This way the data will stay together. The second
column would be just text, varchar(100).
I'd like to end up with a table something like
USER_ID, GROUP_MEMBERSHIP, GIVENNAME
In the example of robert.camarda, that user belongs to 7 groups, so
there would be 7 records, one for each group. I think once I have this
part, I can build my final table with PK's an all the good-house
keeping of a SQL Table.

Now the part that I have no idea how to solve:
How do I convert the data from unfriendly for databases, to something I
can use?
1. I know I have a new user when I find dn:
2. I know I am done with the user when I get a blank (null) line.
3.  I know what I want to populate rows with name and the contents once
I find rows starting with memberOf:
4. It appears there is a max line length that LDIFDE will export, and
starts a new line. So, it will be necessary to join lines.
I would think this is a combo of CURSORS, a do/while loop and other
assorted magic.

If someone can help me get started, I would have something to reseach
or model from. As of now, im staring and a blank page and not sure how
to start. Maybe someone knows of a simular problem that can share the
SQL.
TIA
Rob

Hi

Instead you may want to check out:
http://www.rlmueller.net/ List%20Us...oups
.htm

John

"rcamarda" <rcamarda@cablespeed.com> wrote in message
news:1135198823.474342.97500@g43g2000cwa.googlegroups.com...
> This is a tuffie, but I think I'll learn new techniques in SQL.
> I wish to put data from MS Active Directory and put it into a table.
> Specificly I want user information (first name, last name and so forth)
> and the groups that they belong into a SQL table.
> LDIFDE is a utility that can create a csv file from an AD server. This
> is a sample output:
>
> dn: CN=rob  camarda,OU=Corporate
,OU=Geographic
>  Locations,DC=strayer
,DC=edu
> changetype: add
> objectClass: top
> objectClass: person
> objectClass:  organizationalPerson

> objectClass: user
> cn: rob camarda
> givenName: rob
> memberOf: CN=Arlington Admin,OU=Campus Domain  Admin,DC=strayer,DC=
edu
> memberOf:
>  CN=Arlington,OU=Arli
 ngton,OU=Region2,OU=
Geographic
>  Locations,DC=strayer
,DC=edu
> memberOf: CN=RN Report Consumers,OU=Cognos  ReportNet,DC=strayer
,DC=edu
> sAMAccountName: rob.camarda
>
> dn: CN=Robert A.  Camarda,OU=Technolog
 yGroup,DC=strayer,DC
=edu
> changetype: add
> objectClass: top
> objectClass: person
> objectClass:  organizationalPerson

> objectClass: user
> cn: Robert A. Camarda
> givenName: Robert
> memberOf: CN=Role Regional  Director,OU=Roles,DC
=strayer,DC=edu
> memberOf: CN=Role Campus  Director,OU=Roles,DC
=strayer,DC=edu
> memberOf:  CN=TLSAdmin,OU=Talis
ma- Users,DC=strayer,DC=
edu
> memberOf:  CN=ASPTestReports,OU
 =Roles,DC=strayer,DC
=edu
> memberOf: CN=IT Report  Authors,OU=Roles,DC=
strayer,DC=edu
> memberOf:  CN=Developers,OU=Tec
 hnologyGroup,DC=stra
yer,DC=edu
> memberOf: CN=SQL Backup  Admin,OU=TechnologyG
 roup,DC=strayer,DC=e
du
> memberOf: CN=RN Report MetaData Modelers,OU=Cognos
>  ReportNet,DC=strayer
,DC=edu
> memberOf:
> CN=RN  Corporate,OU=Corpora
 te,OU=Region2,OU=Geo
graphic
>  Locations,DC=strayer
,DC=
> edu
> memberOf:
>  CN=Arlington,OU=Arli
 ngton,OU=Region2,OU=
Geographic
>  Locations,DC=strayer
,DC=edu
> memberOf: CN=RN Administrator System,OU=Cognos
>  ReportNet,DC=strayer
,DC=edu
> memberOf: CN=RN Administrator Server,OU=Cognos
>  ReportNet,DC=strayer
,DC=edu
> memberOf: CN=RN Report Authors,OU=Cognos  ReportNet,DC=strayer
,DC=edu
> memberOf: CN=Backup  Operators,CN=Builtin
,DC=strayer,DC=edu
> memberOf: CN=Domain  Admins,CN=Users,DC=s
trayer,DC=edu
> memberOf:  CN=Administrators,CN
 =Builtin,DC=strayer,
DC=edu
> sAMAccountName: robert.camarda
>
> In this output, each user is separated by a blank line. sAMAccountName
> is the user's login ID to ADS. Lines starting with memberOf: shows the
> path for each group the user belongs.
>
> My thought is to load the text data into a SQL table with the PK being
> the line number. This way the data will stay together. The second
> column would be just text, varchar(100).
> I'd like to end up with a table something like
> USER_ID, GROUP_MEMBERSHIP, GIVENNAME
> In the example of robert.camarda, that user belongs to 7 groups, so
> there would be 7 records, one for each group. I think once I have this
> part, I can build my final table with PK's an all the good-house
> keeping of a SQL Table.
>
> Now the part that I have no idea how to solve:
> How do I convert the data from unfriendly for databases, to something I
> can use?
> 1. I know I have a new user when I find dn:
> 2. I know I am done with the user when I get a blank (null) line.
> 3.  I know what I want to populate rows with name and the contents once
> I find rows starting with memberOf:
> 4. It appears there is a max line length that LDIFDE will export, and
> starts a new line. So, it will be necessary to join lines.
> I would think this is a combo of CURSORS, a do/while loop and other
> assorted magic.
>
> If someone can help me get started, I would have something to reseach
> or model from. As of now, im staring and a blank page and not sure how
> to start. Maybe someone knows of a simular problem that can share the
> SQL.
> TIA
> Rob
>