0

I have a source file with 3 dynamic columns which I want to split based on a delimiter.

The following image shows my source file and the desired output; both are also reproduced as text below:

Source file and expected output

Source:

Emp,Hire,ILT,Mgr,Pi
123,Y,UB|VY,,BY|LN|VB
435,N,LN,HA-12/09/2019|LA-13/09/2019,BY|LN|OR
657,Y,,MA-12/09/2019|RA-13/09/2019,VB

Desired output:

Emp,hire,ILT,Mgr,Pi
123,Y,UB,,BY
123,Y,VY,,LN
123,Y,,,VB
435,N,LN,HA-12/09/2019,BY
435,N,,LA-13/09/2019,LN
435,N,,,OR
657,Y,,MA-12/09/2019,VB
657,Y,,RA-13/09/2019,VB
4
  • Welcome to the site. Please edit your post to indicate what you have tried so far, and where you faced difficulties; this will help contributors point you in the right direction without proposing something you already know doesn't work. Also, it will help understand which tools (awk, sed, something completely different) you can/want to use.
    – AdminBee
    Jun 15 '20 at 12:14
  • And it looks like the question is mistyped, shouldn't it be ,UB|VY|, not ,UB|VY,to get that output.
    – bu5hman
    Jun 15 '20 at 17:26
  • Hi @fra-san Thank you so much for all your effort, i have edited my question. Also, added a picture to provide more understanding on what i'm looking for. would be helpful if you can check the same and let me know in case u need any inputs. Jun 16 '20 at 6:50
  • I took the liberty of editing your question to make the text samples consistent with the image you added (thank you for your edit). Please, make it clear 1) if the "dynamic" columns are always and only the third, fourth and fifth ones; 2) Why "VB" in the fifth column for Employee ID 657 is repeated in both the output lines, while, for instance, "LN" in the third column for Employee ID 435 is only shown in the first output line.
    – fra-san
    Jun 16 '20 at 9:51
0

Assuming you saved this AWK script as script:

BEGIN {
                                # Populate an array that lists (is indexed
                                # by) the position of "dynamic" fields.
  split(dynamic,temp,",")
  for (i in temp)
    tosplit[temp[i]] = i
}
{
                                # Determine how many times the current
                                # line will be repeated...
  times = 1
                                # by counting how many times, for each field,
  for (f = 1; f <= NF; f++) {
                                # the "|" separator is replaced by itself.
    repl = gsub(/\|/, "&", $f)
    if ( (repl + 1 ) > times)
      times = (repl + 1 )
  }
                                # For each time the line has to be repeated:
  for (i = 1; i <= times; i++) {
    for (f = 1; f <= NF; f++) {
                                # every "dynamic" field is split on "|", and
                                # only the component which belongs to the
                                # current line repetition is printed;
      if (f in tosplit) {
        split($f, p, "|")
        printf( (f == NF ? "%s"ORS : "%s"OFS), p[i] )
      }
                                # all other fields are printed unchanged.
      else
        printf( (f == NF ? "%s"ORS : "%s"OFS), $f )
    }
  }
}

you can then invoke it as:

awk -v FS=',' -v OFS=',' -v dynamic=3,4,5 -f script source_file

The indexes of the columns that are subject to splitting (the "dynamic" fields) is passed to awk as a variable holding a comma-separated list.

8
  • Thank you so much fra-san I tried an above logic shared by you and it is working around 90%. Source: 123,Y,AB|CD|MN,EF|GG,XX Output: row1: 123,Y,AB,EF,XX row2: 123,Y,CD,GG,XX row3: 123,Y,MN,,XX. Last column i.e. XX should be only present in row1 but I am getting in all 3 rows Jun 16 '20 at 5:23
  • Also, I created a sample record as 123,Y,COMB,,BY|LN|XY output: rec1: 123,Y,COMB,,BY rec2: 123,Y,COMB,,LN rec3: 123,Y,COMB,,XY In this case I wanted output as rec1: 123,Y,COMB,,BY rec2: 123,Y,,,LN rec3: 123,Y,,,XY let me knw in case u need any more eg. Jun 16 '20 at 5:58
  • I have observed, if any of a dynamic column is having one value and rest with multiple value then that single value is repeated. E.g. input: 123,Y,,COMB-03/26/2019,BY|LN output: rec1: 123,Y,,COMB-03/26/2019,BY rec2: 123,Y,,COMB-03/26/2019,LN expected: rec1: 123,Y,,COMB-03/26/2019,BY rec2: 123,Y,,,LN Jun 16 '20 at 6:23
  • @Sanjuvarma05 This is easy to set right (you didn't explicitly state which of the five columns were to be considered "dynamic" and, given that they may contain no "|", there was no way for the script to "detect" them). The real issue is that the picture you added to your question is repeating "VB" in the fifth column for Employee ID 657, which contradicts both your comments and the way "LN" in the third column for Employee ID 435 is handled. Are there rules you have not made clear yet?
    – fra-san
    Jun 16 '20 at 8:53
  • @Sanjuvarma05 The edited answer still doesn't produce the desired output as shown in the image in your question, but it takes care of the point you raised here in comments.
    – fra-san
    Jun 16 '20 at 10:09

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.