Change 10th column formatting.

Shell Programming and Scripting
1

Hi Experts,

I created a powershell script to fetch user details from active directory and export it to a csv/excel.

"abc","bcd","devloper;admin"
"bcd","dca","tester;QA;admin"

So here user abc is member of devloper and admin group and user bcd is a member of tester,QA and admin group. I want a output for each group to which a user is a member of in separate rows. Groups are separated by semicolon.

something like this

"abc","bcd","devloper"
"abc","bcd","admin"
"bcd","dca","tester"
"bcd","dca","QA"
"bcd","dca","admin"

Note: it is not necessary to write a PowerShell script for formatting. Basj, Python will also work.
Thanks in advance.

2

Hello shekhar_4_u,

If interested in awk then following may help you too on same.

awk -v s1="\"" 'match($0,/"[a-zA-Z]+\;.*\"/){num=split(substr($0,RSTART+1,RLENGTH-1),array,";");for(i=1;i<=num;i++){print substr($0,1,RSTART-1) s1 array s1}}'  Input_file

EDIT: Adding a non-one liner form of solution too now.

awk -v s1="\"" '
match($0, /"[a-zA-Z]+\;.*\"/) {
  num = split(substr($0, RSTART + 1, RLENGTH - 1), array, ";");
  val = substr($0,1,RSTART - 1);
  for (i = 1; i <= num; i++) {
    print val s1 array s1
  }
}
'   Input_file

Thanks,
R. Singh

1 Like
3

Try also

awk -F, '
        {gsub (/;/, "\"&\"", $3)
         n = split ($3, T, ";")
         for (i=1; i<=n; i++)   {$3 = T
                                 print
                                }
        }
' OFS=, file
4

Thanks Ravinder. This works like a charm. But that was a sample file gave. My actual file looks like this

"ABC","ABC@xyz.com","Mr ABC","odabc",,"CN=ABC,OU=Admins,OU=Users,OU=Accounts,DC=webod,DC=com",,"5/11/2018 12:17:33 AM","4/6/2018 3:49:46 AM","WEBOD_Admins;Domain Admins"

"Sri","sri@xyz.com","Mr Sri","odbcd",,"CN=Sri,OU=Admins,OU=Users,OU=Accounts,DC=webod,DC=com",,"3/27/2018 1:53:10 AM","9/20/2016 10:23:01 PM","WEBOD_Admins;WEBDEV"

One more point, there might be some blank field also in between e.ge balank fieled after odabc filed. Total number of columns are fixed though. There are 10 columns in total.
"ABC","ABC@xyz.com","Mr ABC","odabc",,"C

It is not working for this. Can you please advise.

---------- Post updated at 10:04 AM ---------- Previous update was at 09:50 AM ----------

Hi RudiC,
Your code also runs perfect. But aplogies for torouble. My original file differs a bit. Please see my comment in reply to Ravinder's solution.

5

Hi, please in future provide the right sample in post #1 from the start. That saves everyone time and effort.

Try this approach:

awk 'NF{n=split($(NF-1),F,/;/); for(i=1; i<=n; i++) {$(NF-1)=F; print}}' FS=\" OFS=\" file
1 Like
6

Thanks @Scrutinizer ..it works. Apologies for not posting correct format earlier.

7

Try replacing ALL $3 occurrences in my proposal with $NF and report back.