2

The file at hand lists names, credentials, roles, and privileges for thousands of users. It was passed onto me as .xls but the lines and columns are not arranged in proper form. I have reformated the raw file using awk and sed, now I have a number of lines with consistent format as such:

ID       ;email                     ;role  ;privilege ;access-to
8charID1 ;first.lastname@domain.org ;usr   ;read      ;finance ;HR ;accounting; dev
8charID2 ;first.lastname@domain.org ;mgr   ;rwx       ;finance
8charID3 ;first.lastname@domain.org ;usr   ;rx        ;marketing ;dev ;doc
.
.
n x 1,000 number of users

But I'm stuck on the next step.

Objective: reprint the lines such that where there are multiple access fields like line 1, or line 3, all the preceeding fields be re-printed per number of access field and re-order access field into a SINGLE column.

ID       ;email                     ;role  ;privilege ;access-to
abcuser1 ;first.lastname@domain.org ;usr   ;read      ;finance
abcuser1 ;first.lastname@domain.org ;usr   ;read      ;HR
abcuser1 ;first.lastname@domain.org ;usr   ;read      ;accounting
abcuser1 ;first.lastname@domain.org ;usr   ;read      ;dev
user2def ;first.lastname@domain.org ;mgr   ;rwx       ;finance
zyxuser3 ;first.lastname@domain.org ;usr   ;rx        ;marketing
zyxuser3 ;first.lastname@domain.org ;usr   ;rx        ;dev
zyxuser3 ;first.lastname@domain.org ;usr   ;rx        ;publication
.
.
.
n x 1,000 number of users
  • just noticed the format of my example got mangled after I pressed "post"! – user2793091 Nov 5 '14 at 19:07
  • Are your columns separated by tabs? – Mark Plotnick Nov 5 '14 at 19:17
  • no, field delimiter is a ; but I can easily change it to a \t if needed. – user2793091 Nov 5 '14 at 19:24
2
awk -F';' -v OFS=';' '
    { for (i=5; i<=NF; i++) print $1,$2,$3,$4,$i }
' file

outputs

ID       ;email                     ;role  ;privilege ;access-to
8charID1 ;first.lastname@domain.org ;usr   ;read      ;finance 
8charID1 ;first.lastname@domain.org ;usr   ;read      ;HR 
8charID1 ;first.lastname@domain.org ;usr   ;read      ;accounting
8charID1 ;first.lastname@domain.org ;usr   ;read      ; dev
8charID2 ;first.lastname@domain.org ;mgr   ;rwx       ;finance
8charID3 ;first.lastname@domain.org ;usr   ;rx        ;marketing 
8charID3 ;first.lastname@domain.org ;usr   ;rx        ;dev 
8charID3 ;first.lastname@domain.org ;usr   ;rx        ;doc
  • Well, you obviously know your stuff! first I typed in your command verbose and ran it against my file, worked perfectly. Then I played with it over the past 24hrs until I understood what exactly you're doing and modified the command to make it work with the actual production file. Now it looks great on my linux VM, but when I move the final txt file to windows and try to open it, it's all corrupted or something. Characters either look chinese, or some sort of font I've never encountered before, and occasionally it just fills the screen with squares! But looks just fine in linux. – user2793091 Nov 6 '14 at 20:49
  • To move a unix text file to windows, you'll have to change the line endings from \n to \r\n – glenn jackman Nov 6 '14 at 21:18
  • This is the command I'm using and I know that the predecessor file opens fine in both Linux & Win7, no problem. But after being processed by this command, the char-encoding goes sideways! . . . .. . . . . . . . . . . . [@localhost]# awk -F';' -v OFS=';' ' > { for (i=3; i<=NF; i++) print $1,$2,$i } > ' file-preformat >file-final Perhaps this should be augmented by an additional statement that would ensure either char-set Unicode, or UTF-8 can both open the file legibly. added – user2793091 Nov 6 '14 at 22:56
2

Here's the same basic idea as Glenn's solution but in Perl:

$ perl -F";" -lane '$"=";";print "@F[0..3];", $_ for @F[4..$#F]' file 
ID       ;email                     ;role  ;privilege ;access-to
8charID1 ;first.lastname@domain.org ;usr   ;read      ;finance 
8charID1 ;first.lastname@domain.org ;usr   ;read      ;HR 
8charID1 ;first.lastname@domain.org ;usr   ;read      ;accounting
8charID1 ;first.lastname@domain.org ;usr   ;read      ; dev
8charID2 ;first.lastname@domain.org ;mgr   ;rwx       ;finance
8charID3 ;first.lastname@domain.org ;usr   ;rx        ;marketing 
8charID3 ;first.lastname@domain.org ;usr   ;rx        ;dev 
8charID3 ;first.lastname@domain.org ;usr   ;rx        ;doc

Explanation

  • -a : makes perl act like awk, automatically splitting each line into fields on whitespace (by default, see -F) and saving them in the @F array.
  • -e : lets you give a script on the command line.
  • -n : read the input file line by line.
  • -l : remove trailing newlines and add a newline to each print call.
  • -F : like awk, the input field separator. Here, we're setting it to ;.

The script is admittedly kind of cryptic but the algorithm is very simple: print the first four fields and repeat for each field >4.

  • $"=";" : The special variable $" is the list separator. This is printed between each element of an array of you print the array. Here, I am setting it to ;, so we can get the list of fields printed as desired.

  • @F[0..3] : this is an array slice. It's the first 4 elements of the @F array (the ;-separated fields of the current line)

  • @F[4..$#F] : another array slice. This one is from the 5th element of @F until the last one ($#array is the largest index of an array in Perl).

The whole print command is just idiomatic Perl for:

## For each element of the array slice
foreach $field (@F[4..$#F]){
  ## print the first 4 fields and a ';', The fields 
  ## are printed separated by ";" because of the $" above.
  print "@F[0..3];";
  ## In the golfed version, the $_ is equivalent to $field here
  print "$field\n"
}
  • I wish I could do something like ${1..4} in awk. – glenn jackman Nov 5 '14 at 20:59
  • I wished I could write perl, took a few attempts but didn't get it. For now I'm sticking with awk and sed. But thanks so much for sharing your perl solution. – user2793091 Nov 7 '14 at 1:57
  • @user2793091 sorry, that was pretty cryptic. I didn't have time to add an explanation when I wrote it but I've added one now. I hope it's clearer. Please remember to accept one of these answers (preferably Glenn's since I'm just porting his approach to Perl here) if they solved your problem, that way the question can be marked as answered. – terdon Nov 7 '14 at 2:24
  • thank you for the description/explanation comments. The first solution unlocked the problem for the most part, I'll keep working on the last problem which I think has to do with iterating new lines, so that the file after processed can also be read. BTW I realize now that after I run the awk + for loop, command, the resulting file has the same character set issue on Linux as Win7. Thanks again for your support, I will mark this salt assumes I find that button. – user2793091 Nov 7 '14 at 18:35
  • @user2793091 you're welcome. Thanks for accepting Glenn's answer. Feel free to post a new question about this iteration issue. – terdon Nov 7 '14 at 18:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.