Need help to format one txt file to required format

Shell Programming and Scripting
1

Hello Everyone,

I have one source file which is genarated by SAP in different format(Which I've never seen). I need to convert that file to required format and I need to read this target file from Datastage to use this in my Jobs. So I do not have any other options except to use Unix script to modify this file. I am not that expert in Unix so I would like to take Ideas from all of you, it may help us to complete this task.

I am attaching my source file to this thread and below is my required output format.

 
TCode|Sales deal|Date|User|Cond|Time|NewVal|Validity period
VK11||10-31-2013|ET8IVND|ZDEC|06:47:51|4.529- %|10-31-2013-12-31-9999
VK11||10-31-2013|ET8IVND|ZDEC|06:47:51|4.527- %|10-31-2013-12-31-9999
VK11||10-31-2013|ET8IVND|ZDEC|06:47:51|4.527- %|10-31-2013-12-31-9999
VK11||10-31-2013|ET8IVND|ZDEC|06:47:51|4.528- %|10-31-2013-12-31-9999
VK11||10-31-2013|ET8IVND|ZDEC|08:28:35|0.200- %|11-01-2013-12-31-9999
VK11||10-31-2013|ET8IVND|ZDEC|08:28:35||11-01-2013-12-31-9999

Can anyone please let me know is it possible to do this through Unix or not?

Thanks in Advance!!

2

you can try something like this to start with: awk -f pra.awk ConditionChanges.txt
pra.awk:

BEGIN {
  FS=OFS="|"
  tab=sprintf("\t")
}
function trim(str)
{
    sub("^([ ]*|" tab "*)", "", str);
    sub("([ ]*|" tab "*)" "$", "", str);
    return str;
}

NF && $2 ~ / *-$/ {
     date=$3
     user=$4
     cond=$5
     validity=$(NF-1)
}
NF && $2~/^ *$/ && $3~/^ *$/ {
     if ($4 ~ /[0-9:]/) {
         time=$4
         tcode=$5
         salesDeal=$9
     }
     else if ($(NF-1) ~ "%")
        print trim(tcode), trim(salesDeal), trim(date), trim(user), trim(cond), trim(time), trim($(NF-1)), trim(validity)
}
3

Thanks for the quick response vgersh99!! As I am starter in Unix scripting, I think it may take lot time to complete the testing for the code which you given. But I will definetly try your code to get the results.

Thanks Again!!

4

I've updated the code with one more condition - take the latest version.

5

Okay. Thank you.