[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Merge data from two text files

Posted on 2011-10-19
12
Medium Priority
?
527 Views
Last Modified: 2012-05-12
I have two text files.  First one will have a username with the format of the SAMid (UserOne).  There will only be one entry in this file.  The 2nd file has particular groups the user belongs too with the CN name "(cn=groupname.ou=groups,dc=domain,dc=ext)".  There may be two or more entries in this file.  I want to merge these entries into one text file that would prefereably look like

"(cn=groupname1.ou=groups,dc=domain,dc=ext)";UserOne
"(cn=groupname2.ou=groups,dc=domain,dc=ext)";UserOne

OR

UserOne;"(cn=groupname1.ou=groups,dc=domain,dc=ext)"
UserOne;"(cn=groupname2.ou=groups,dc=domain,dc=ext)"

In this case the delimiter needs to be something other than a comma, such as simicolon.  The entries can be reversed, I don't really care.  I'd prefer a DOS command (FOR), since that would fit in with the rest of the script.  We either create a new file with the merged data or append one of the existing files with the data from the other.
0
Comment
Question by:BigmacMc
  • 6
  • 5
12 Comments
 
LVL 85

Expert Comment

by:oBdA
ID: 36995633
Something like this? It expects the user file name as first argument, the group file name as second argument, and creates a file with the name of both the user and the group file.
@echo off
setlocal
set Delim=;
set UserFile=%~1
set GroupFile=%~2
set ResultFile=%~n1-%~nx2
if exist "%ResultFile%" del "%ResultFile%"
for /f "delims=" %%a in ('type "%UserFile%"') do set UserID=%%~a
for /f "delims=" %%a in ('type "%GroupFile%"') do (
  >>"%ResultFile%" echo %UserID%%Delim%%%~a
)

Open in new window

0
 

Author Comment

by:BigmacMc
ID: 36995889
Does line 4 & 5 need the actual path/filename for the user & group files?  I'm confused as to where the actual file names are defined.  The file names are Users_UserOne.txt and GroupsFor_UserOne.txt

0
 
LVL 85

Expert Comment

by:oBdA
ID: 36995985
Currently, you'll have to call the script with the two arguments (in case you have more than one of each file and want to process them with another script):
whatever.cmd "Users_UserOne.txt" "GroupsFor_UserOne.txt"
But nothing keeps you from setting the variables directly in lines 4 and 5. If the script is in the same folder as the files, the names are enough.
@echo off
setlocal
set Delim=;
set UserFile=Users_UserOne.txt
set GroupFile=GroupsFor_UserOne.txt
set ResultFile=%~n1-%~nx2
if exist "%ResultFile%" del "%ResultFile%"
for /f "delims=" %%a in ('type "%UserFile%"') do set UserID=%%~a
for /f "delims=" %%a in ('type "%GroupFile%"') do (
  >>"%ResultFile%" echo %UserID%%Delim%%%~a
)

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:BigmacMc
ID: 36996173
It's working kindof.  I was mistaken in that the User file may have more than one entry
UserOne
UserTwo

When I run the script I'm getting  the following in the ResultFile

UserTwo ;CN=Firewall.Two,OU=Groups,OU=Development,DC=bigmac,DC=home
UserTwo ;CN=Firewall.One,OU=Groups,OU=Development,DC=bigmac,DC=home
UserTwo ;CN=Firewall.Two,OU=Groups,OU=Development,DC=bigmac,DC=home
UserTwo ;CN=Firewall.One,OU=Groups,OU=Development,DC=bigmac,DC=home

Two should say UserOne and two should say UserTwo.  Also there's a space after the Usertwo.
0
 
LVL 85

Expert Comment

by:oBdA
ID: 36996215
The space after the user names is in the initial text file.
And if there are several entries in the user file, try this:
@echo off
setlocal
set Delim=;
set UserFile=Users_UserOne.txt
set GroupFile=GroupsFor_UserOne.txt
set ResultFile=%~n1-%~nx2
if exist "%ResultFile%" del "%ResultFile%"
for /f %%a in ('type "%UserFile%"') do (
  for /f "delims=" %%g in ('type "%GroupFile%"') do (
    >>"%ResultFile%" echo %%a%Delim%%%~g
  )
)

Open in new window

0
 

Author Comment

by:BigmacMc
ID: 36996379
Ok...almost there.  It looks good and would be workable.  The ResultFile is not exacty right, though usable.  There are two issues with the ResultFile.

1.  There's multiple entries.  For instance if UserOne is a member of two groups (GroupName1,
     Groupname2) and UserTwo is a member of three groups (Groupname1, Groupname2 and
     Groupname3).  I see 10 entries for each user (total of 20 lines).
2.  The ResultFile contains entries for Groupname3 for UserOne, even though he's not a member.

Here's what's in the ResultFile

UserOne;CN=Firewall.Two,OU=Groups,OU=Development,DC=bigmac,DC=home
UserOne;CN=Firewall.One,OU=Groups,OU=Development,DC=bigmac,DC=home
UserOne;CN=Firewall.Three,OU=Groups,OU=Development,DC=bigmac,DC=home
UserOne;CN=Firewall.Two,OU=Groups,OU=Development,DC=bigmac,DC=home
UserOne;CN=Firewall.One,OU=Groups,OU=Development,DC=bigmac,DC=home
UserOne;CN=Firewall.Two,OU=Groups,OU=Development,DC=bigmac,DC=home
UserOne;CN=Firewall.One,OU=Groups,OU=Development,DC=bigmac,DC=home
UserOne;CN=Firewall.Three,OU=Groups,OU=Development,DC=bigmac,DC=home
UserOne;CN=Firewall.Two,OU=Groups,OU=Development,DC=bigmac,DC=home
UserOne;CN=Firewall.One,OU=Groups,OU=Development,DC=bigmac,DC=home
UserTwo;CN=Firewall.Two,OU=Groups,OU=Development,DC=bigmac,DC=home
UserTwo;CN=Firewall.One,OU=Groups,OU=Development,DC=bigmac,DC=home
UserTwo;CN=Firewall.Three,OU=Groups,OU=Development,DC=bigmac,DC=home
UserTwo;CN=Firewall.Two,OU=Groups,OU=Development,DC=bigmac,DC=home
UserTwo;CN=Firewall.One,OU=Groups,OU=Development,DC=bigmac,DC=home
UserTwo;CN=Firewall.Two,OU=Groups,OU=Development,DC=bigmac,DC=home
UserTwo;CN=Firewall.One,OU=Groups,OU=Development,DC=bigmac,DC=home
UserTwo;CN=Firewall.Three,OU=Groups,OU=Development,DC=bigmac,DC=home
UserTwo;CN=Firewall.Two,OU=Groups,OU=Development,DC=bigmac,DC=home
UserTwo;CN=Firewall.One,OU=Groups,OU=Development,DC=bigmac,DC=home
0
 
LVL 85

Accepted Solution

by:
oBdA earned 2000 total points
ID: 36996510
Uhm - based on which information is the script supposed to determine which of the entries in the groups file belongs to which entry in the user file?
This should query the group membership of the users in the text file for any group containing "Firewall" directly from AD:
@echo off
setlocal
set Delim=;
set UserFile=Users_UserOne.txt
set ResultFile=%~n1-%~nx2
if exist "%ResultFile%" del "%ResultFile%"
for /f "%%a" in ('type "%UserFile%"') do (
  for /f "delims=" %%d in ('dsquery user -samid "%%a" ^| dsget user -memberof -expand ^| find /i "firewall"') do (
    >>"%ResultFile%" echo %%a%Delim%%%~d
  )
)

Open in new window

0
 
LVL 10

Expert Comment

by:ReneGe
ID: 36997207
Nice script oBdA

As oBdA was sharing, his script is based on the information you shared, assuming there will not be any spaces.

If spaces could happen, change line 7 to:
for /f "delims=" %%a in ('type "%UserFile%"') do (

Cheers,
Rene
0
 

Author Comment

by:BigmacMc
ID: 37003318
Both of these fail in different ways.

If I use Line 7 as written by oBdA I get an 'IN' not expected at this time...and it is referring to 1st IN.  Here's the result with @echo on
___________________________________________________________________________

C:\Scripts\DisableAccts\Test>disablenyaccts

C:\Scripts\DisableAccts\Test>setlocal

C:\Scripts\DisableAccts\Test>set Delim=;

C:\Scripts\DisableAccts\Test>set UserFile=c:\scripts\DisableAccts\Test\Users.txt


C:\Scripts\DisableAccts\Test>set ResultFile=c:\scripts\DisableAccts\Test\Process
.txt

C:\Scripts\DisableAccts\Test>if exist "c:\scripts\DisableAccts\Test\Process.txt"
 del "c:\scripts\DisableAccts\Test\Process.txt"
IN was unexpected at this time.

C:\Scripts\DisableAccts\Test>for /f "%a" IN ('type "c:\scripts\DisableAccts\Test
\Users.txt"') do (

C:\Scripts\DisableAccts\Test>
____________________________________________________________________

If I use it as ReneGe: wrote it I get a 'Target object missing'  Here's what happens.


C:\Scripts\DisableAccts\Test>disablenyaccts

C:\Scripts\DisableAccts\Test>setlocal

C:\Scripts\DisableAccts\Test>set Delim=;

C:\Scripts\DisableAccts\Test>set UserFile=c:\scripts\DisableAccts\Test\Users.txt


C:\Scripts\DisableAccts\Test>set ResultFile=c:\scripts\DisableAccts\Test\Process
.txt

C:\Scripts\DisableAccts\Test>if exist "c:\scripts\DisableAccts\Test\Process.txt"
 del "c:\scripts\DisableAccts\Test\Process.txt"

C:\Scripts\DisableAccts\Test>for /F "delims=" %a IN ('type "c:\scripts\DisableAc
cts\Test\Users.txt"') do (for /F "delims=" %d in ('dsquery user -samid "%a" | ds
get user -memberof -expand | find /I "firewall"') do (echo %a;%~d 1>>"c:\scripts
\DisableAccts\Test\Process.txt" ) )

C:\Scripts\DisableAccts\Test>(for /F "delims=" %d in ('dsquery user -samid "Uone
 " | dsget user -memberof -expand | find /I "firewall"') do (echo Uone ;%~d 1>>"
c:\scripts\DisableAccts\Test\Process.txt" ) )
dsget failed:'Target object for this command' is missing.
type dsget /? for help.
C:\Scripts\DisableAccts\Test>(for /F "delims=" %d in ('dsquery user -samid "UTwo
 " | dsget user -memberof -expand | find /I "firewall"') do (echo UTwo ;%~d 1>>"
c:\scripts\DisableAccts\Test\Process.txt" ) )
dsget failed:'Target object for this command' is missing.
type dsget /? for help.C:\Scripts\DisableAccts\Test>
______________________________________________________________________

Sorry this being such a pain.

Bigmac
0
 
LVL 85

Expert Comment

by:oBdA
ID: 37003341
Sorry, the quotes around %%a shouldn't have been there.
@echo off
setlocal
set Delim=;
set UserFile=Users_UserOne.txt
set ResultFile=Users_Groups.txt
if exist "%ResultFile%" del "%ResultFile%"
for /f %%a in ('type "%UserFile%"') do (
  for /f "delims=" %%d in ('dsquery user -samid "%%a" ^| dsget user -memberof -expand ^| find /i "firewall"') do (
    >>"%ResultFile%" echo %%a%Delim%%%~d
  )
)

Open in new window

0
 

Author Comment

by:BigmacMc
ID: 37003437
I removed the " " from around "%%a" on Line 7 and it worked perfectly.  Don't know why I didn't see that before.

Thanks so much oBdA
0
 

Author Closing Comment

by:BigmacMc
ID: 37003444
Elegantly simple
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I don't know if many of you have made the great mistake of using the Cisco Thin Client model with the management software VXC. If you have then you are probably more then familiar with the incredibly clunky interface, the numerous work arounds, and …
Sometimes a user will call me frantically, explaining that something has gone wrong and they have tried everything (read - they have messed it up more and now need someone to clean up) and it still does no good, can I help them?!  Usually the standa…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question