CSVDE Help Needed - Too much data exported

Hi Guys,

I have a RL situation at work I need some help with. I recently studied and passed the 70-290 and so when the boss came and asked me for a list of all the Groups and who belonged to them, I suggested that we use CSVDE to export the names and groups to a spreadsheet.

Anyway, as I don't have access to any of the DC's here, I told the Sys Admin about the request, who initially, thought we'd have to go thorugh manually. I reminded him about CSVDE!

Now a week later I have the CSV export back from him and I opened it up in Excel and "Whoa!" - Way too much info and very confusing to look at in it's current format.

The column that has the User names in it, each name is formatted like this:

CN=Sam Smith,OU=Users,OU=Team Project 1,OU=Timbukto AQUATIC CENTRE,OU=COMMUNITY & ENVIRONMENTAL SERVICES,OU=BOARD,DC=SUMMERFIELD,DC=dingle,DC=com,DC=au;

I was asked if I could change this column so that just the user name (CN) exists.

That way it would just be a plain list of users like : Sam Smith, Mike Jones, Ben Brown etc

and much easier to read.

Is this possible?

Any tips would be greatly appreciated!

Thanks guys,

All the best,
Sam

Find more posts tagged with

Free for TechExams community: Cybersecurity salary guide

Compare cert salaries and plan your next career move

Button

Comments

tanix

Honky007 wrote: »

Hi Guys,

I have a RL situation at work I need some help with. I recently studied and passed the 70-290 and so when the boss came and asked me for a list of all the Groups and who belonged to them, I suggested that we use CSVDE to export the names and groups to a spreadsheet.

Anyway, as I don't have access to any of the DC's here, I told the Sys Admin about the request, who initially, thought we'd have to go thorugh manually. I reminded him about CSVDE!

Now a week later I have the CSV export back from him and I opened it up in Excel and "Whoa!" - Way too much info and very confusing to look at in it's current format.

The column that has the User names in it, each name is formatted like this:

CN=Sam Smith,OU=Users,OU=Team Project 1,OU=Timbukto AQUATIC CENTRE,OU=COMMUNITY & ENVIRONMENTAL SERVICES,OU=BOARD,DC=SUMMERFIELD,DC=dingle,DC=com,DC=au;

I was asked if I could change this column so that just the user name (CN) exists.

That way it would just be a plain list of users like : Sam Smith, Mike Jones, Ben Brown etc

and much easier to read.

Is this possible?

Any tips would be greatly appreciated!

Thanks guys,

All the best,
Sam

I am not an Excel buff, but can't you create a macro that parses the data based on the criteria you choose and then format it to display in format that suits your needs?

Also might try importing the file into a generic database using something like Access and then creating a simple template that will produce the output you need for simple printing or searching.

Another option I guess would to write up a quick loop that parses the file and displays the fields you want in the format you want it using a web language, Java,VB, C++, etc...

Might be some tools out there for free as well with similar capabilities, though I am pretty sure Excel and Access have that ability.

Honky007

Thanks for the advice...

I'm a novice with programming, but I do have someone I can ask about writing a loop...that'd be a good way of dealing with this.

I tried to make a Macro to do the dirty work in Excel but it didn't work out very well.

Search and Replace gave "Formula too long".

I reckon the loop would be the go, will try and work it out.

Have a good one and thanks for responding!

sprkymrk

Might be kind of a convoluted method, but if you copy/paste that column into a text file, then import it into another spreadsheet but use whatever additional delimiters you need to break out the data into seperate columns where just the user name is by itself. For instance, use commas, equal signs, and tabs. Then copy/paste that column into the original spreadsheet.