Change 10th column formatting.


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Change 10th column formatting.
# 1  
Old 05-16-2018
Change 10th column formatting.

Hi Experts,

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

HTML Code:
"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
HTML Code:
"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.

Last edited by shekhar_4_u; 05-16-2018 at 02:56 PM.. Reason: Change subject line
# 2  
Old 05-16-2018
Hello shekhar_4_u,

If interested in awk then following may help you too on same.
Code:
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[i] s1}}'  Input_file

EDIT: Adding a non-one liner form of solution too now.
Code:
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[i] s1
  }
}
'   Input_file

Thanks,
R. Singh

Last edited by RavinderSingh13; 05-16-2018 at 03:12 PM..
This User Gave Thanks to RavinderSingh13 For This Post:
# 3  
Old 05-16-2018
Try also
Code:
awk -F, '
        {gsub (/;/, "\"&\"", $3)
         n = split ($3, T, ";")
         for (i=1; i<=n; i++)   {$3 = T[i]
                                 print
                                }
        }
' OFS=, file

# 4  
Old 05-17-2018
Quote:
Originally Posted by RavinderSingh13
Hello shekhar_4_u,

If interested in awk then following may help you too on same.
Code:
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[i] s1}}'  Input_file

EDIT: Adding a non-one liner form of solution too now.
Code:
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[i] s1
  }
}
'   Input_file

Thanks,
R. Singh

Thanks Ravinder. This works like a charm. But that was a sample file gave. My actual file looks like this
HTML Code:
"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 ----------

Quote:
Originally Posted by RudiC
Try also
Code:
awk -F, '
        {gsub (/;/, "\"&\"", $3)
         n = split ($3, T, ";")
         for (i=1; i<=n; i++)   {$3 = T[i]
                                 print
                                }
        }
' OFS=, file

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.

Last edited by shekhar_4_u; 05-17-2018 at 01:32 AM..
# 5  
Old 05-17-2018
Hi, please in future provide the right sample in post #1 from the start. That saves everyone time and effort.

Try this approach:
Code:
awk 'NF{n=split($(NF-1),F,/;/); for(i=1; i<=n; i++) {$(NF-1)=F[i]; print}}' FS=\" OFS=\" file


Last edited by Scrutinizer; 05-17-2018 at 02:00 AM..
This User Gave Thanks to Scrutinizer For This Post:
# 6  
Old 05-17-2018
Thanks @Scrutinizer ..it works. Apologies for not posting correct format earlier.
# 7  
Old 05-17-2018
Try replacing ALL $3 occurrences in my proposal with $NF and report back.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

How to replace the 10th column?

how to replace the 10th colum? Each line begins similarly, but they all ends variously. Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col10,Col11,Col12,Col13,Col14,Col15,Col16,Col17 2015/03/14 15:00:04,2015/03/14 15:00:04,0,Server05,Omega-God,Beta_G,101011247,1,1,24,7,0,,339083645,1,1,... (9 Replies)
Discussion started by: ooilinlove
9 Replies

2. Shell Programming and Scripting

Change column to row base on column 2

Hi Guys, Input.txt L194 A -118.2 L194 B -115.1 L194 C -118.7 L196 A 0 L196 C 0 L197 A -111.2 L197 B -118.9 L197 C -119.9 L199 A -120.4 L199 B -119.9 ... (2 Replies)
Discussion started by: asavaliya
2 Replies

3. Shell Programming and Scripting

awk or sed: change the color of a column w/o screwing up column spacing

Hey folks. I wrote a little awk script that summarizes /proc/net/dev info and then pipes it to the nix column command to set up column spacing appropriately. Here's some example output: Iface RxMBytes RxPackets RxErrs RxDrop TxMBytes TxPackets TxErrs TxDrop bond0 9 83830... (3 Replies)
Discussion started by: ryran
3 Replies

4. Shell Programming and Scripting

Change file content 4 column to one Column using script

Hi Gurus, I have file content sample: ,5113955056,,TAgent-Suspend ,5119418233,,TAgent-Suspend ,5102119078,,TAgent-Suspend filenames 120229H5_suspend, 120229H6_unsuspend I receive those files one of directory /home/temp/ I need following: 1. Backup first /home/temp/ file to... (5 Replies)
Discussion started by: thepurple
5 Replies

5. Shell Programming and Scripting

Change value in a column

Hello! Please could anyone help me in writing a code to modify the value in column two of a file with more than two tab-separated column (but the lines may have a different number of columns, i.e. more or less columns), according to the following conditions. When the value of column two is... (5 Replies)
Discussion started by: mjomba
5 Replies

6. Shell Programming and Scripting

(Formatting)new Column adding

Hi guys i have a variable called hostname which contains hostname of my machine. How would i add the hostname to output of other command . For eg. if a output of command is . command : xm list How would i add hostname column to it. My output should look like ... (1 Reply)
Discussion started by: pinga123
1 Replies

7. UNIX and Linux Applications

Replace string in unix from 10th column onwards

Hi All, I need to replace the last 19 bytes of the following string My_Org_Testing_20090102_231124.txt (Text_Date_Time.txt). I would like to derive the current time using "date +%Y%m%d_%H%M%S.txt" and replace the last 19 bytes of the above string I would appreciate if someone could... (3 Replies)
Discussion started by: rpk2008
3 Replies

8. Shell Programming and Scripting

Change names in a column based on the symbols in another column

If the 4th column has - sign then the names in 3rd column has to change to some user defined names (as shown in output). Thanx input1 1 a aaaaa + 2 b bbbbb + 3 c ccccc + 4 d ddddd + 5 e eeeee + 6 f xxxxx + 8 h hhhhh +... (8 Replies)
Discussion started by: repinementer
8 Replies

9. UNIX for Dummies Questions & Answers

Change one column using sed??

Is there way to use sed to change only one occurence in a colon separated line? I would like to change a file from *:*:rex:rex *:*:simon:rex to *:*:rex:mabry *:*:simon:rex (2 Replies)
Discussion started by: rexmabry
2 Replies

10. Shell Programming and Scripting

Replace 10th column with a new column--- Terriblly hurry

Hi Can any one tell me how to replace the 10th column in a file(comma delimted) with a new file with a single column. Can any one Help me out of the please as soon as possible as i am in a terribley hurry!!!!!! Many THanks, (2 Replies)
Discussion started by: ahmedwaseem2000
2 Replies
Login or Register to Ask a Question