Unix awk scripting to convert columns to rows

Question

Need help to convert rows to columns in unix scripting. My source is file system.

Tried the below script:

 `perl -nle '
    if($. == 1)
    { (@a)=/([\w - .]+)(?=,|\s*$)/g }
    else
    {
        (@b)=/([\w - .]+)(?=,|\s*$)/g;
        print "$a[0]|$b[0]|$b[1]|$b[2}|$a[$_]|$b[$_+3]" foreach (0..$#a)
    }
    ' ip.txt >op.txt

input data from file: 

src,FI,QMA,PCG,PCC,PREI,G T
PIM2016.csv,MMR.S T - RED,334,114,120,34,123,725

output with latest script:

SRC|PIM2016.csv|MMRPPS|RED|SRC|334 SRC|PIM2016.csv|MMRPPS|RED|FI|114 SDRC|PIM2016.csv|MMRPPS|RED|QMA|120 SRC|PIM2016.csv|MMRPPS|RED|PCG|34 SRC|PIM2016.csv|MMRPPS|RED|PCC|123 SRC|PIM2016.csv|MMRPPS|RED|PREI|725 SRC|PIM2016.csv|MMRPPS|RED|G T|

Required output:

SRC|PIM2016.csv|MMRPPS|S T -RED|FI|334 SRC|PIM2016.csv|MMRPPS|S T -RED|QMA|114 SRC|PIM2016.csv|MMRPPS|S T -RED|PCG|120 SRC|PIM2016.csv|MMRPPS|S T -RED|PCC|34 SRC|PIM2016.csv|MMRPPS|S T -RED|PREI|123 SRC|PIM2016.csv|MMRPPS|S T -RED|G T|725

Perhaps you could share what you have tried so far and where you are struggling specifically? — wwkudu, Sep 21 '16 at 09:32
does your actual data have more than two rows? if so, can you modify your sample input with atleast two such data and expected output? — Sundeep, Sep 21 '16 at 09:52
@Sundeep : yes actual data as more than two rows. i will modify the sample data — udayadevan, Sep 21 '16 at 09:54
is `perl` solution ok? can there be blank lines between rows? — Sundeep, Sep 21 '16 at 10:03
@Sundeep perl solution is ok. i will remove the blank lines in further processing — udayadevan, Sep 21 '16 at 10:06
@Sundeep this script works are desired. Thanks much. But is taking only second part of words if spaces are in between (ex : Input : Grand Total , Ouput: Total). Is it possible to resolve this? — udayadevan, Sep 21 '16 at 10:32
@Sundeep : im getting new line or blank line in my file generation before executing this script . is there a way to remove them? is it possible to generate pipe delimited file with this script? — udayadevan, Sep 22 '16 at 09:26
see http://stackoverflow.com/a/16414489 for removing blank lines, and replace space with `|` while printing — Sundeep, Sep 22 '16 at 09:32
@Sundeep im able to print with '|', not able to validate its new line or blank line. is it possible to upload picture of data in here? — udayadevan, Sep 22 '16 at 09:49
@Sundeep: need your help with minor changes in script. edited question with latest requirement. — udayadevan, Sep 23 '16 at 08:51
@udayadevan, avoid changing your question often, it invalidates earlier answers and not everybody may be ready to change their answers accordingly.. you could roll back the changes made and ask a new question (along with what you've tried).. — Sundeep, Sep 23 '16 at 08:57

Sundeep · Accepted Answer · 2016-09-21T12:21:24.137

$ cat ip.txt 
HDR :FI,QA,PC,PM,PRE,G T
Detail row: MMRPPS,ST - RED,334,114,120,34,123,725
            UP,UPR,0,0,0,0,0,0

Assuming no blank lines between rows:

$ perl -nle '
s/^.*:\s*|^\s*|\s*$//;
if($. == 1)
{ (@a) = /[^,]+/g }
else
{
    (@b) = /[^,]+/g;
    print "$b[0] $a[$_] $b[1] $b[$_+2]" foreach (0..$#a);
}
' ip.txt
MMRPPS FI ST - RED 334
MMRPPS QA ST - RED 114
MMRPPS PC ST - RED 120
MMRPPS PM ST - RED 34
MMRPPS PRE ST - RED 123
MMRPPS G T ST - RED 725
UP FI UPR 0
UP QA UPR 0
UP PC UPR 0
UP PM UPR 0
UP PRE UPR 0
UP G T UPR 0

Input lines are pre-processed to remove leading text upto :, any leading and trailing white-spaces
From first line, extract comma separated values into @a array. The regex looks for string of non , characters
For all other lines,
- same regex to extract comma separated values into @b array
- print in desired order

@ sundeep: this script works are desired. Thanks much. But is taking only second part of words if spaces are in between (ex : Input : Grand Total , Ouput: Total). Is it possible to resolve this? — udayadevan, Sep 21 '16 at 10:30
@udayadevan try replacing `(\w+)` with `([\w ]+)`.. and edit your question to reflect this sample data — Sundeep, Sep 21 '16 at 10:38

score 0 · Answer 2 · answered Sep 21 '16 at 11:24

0

@sundeep : thanks for your answer. Below script works

perl -nle '
if($. == 1)
{ (@a)=/([\w -]+)(?=,|\s*$)/g }
else
{
    (@b)=/([\w -]+)(?=,|\s*$)/g;
    print "$b[0] $a[$_] $b[1] $b[$_+2]" foreach (0..$#a)
}
' ip.txt

answered Sep 21 '16 at 11:24

udayadevan

23
5

2

see my edited answer and [What should I do when someone answers my question?](http://stackoverflow.com/help/someone-answers) – Sundeep Sep 21 '16 at 12:23

Unix awk scripting to convert columns to rows

2 Answers2

Linked