52541

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:

<blockquote>

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|

</blockquote>

Required output:

<blockquote>

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

</blockquote>

Answer1:

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

Answer2:

@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

Recommend

  • Reduce left joins complexity
  • freegeoip doesn't work anymore
  • bash, zsh : declare [*] (oh my)
  • T-SQL recursive query - how to do it?
  • How to check a string if it contains a special character (!@#$%^&*()_+)
  • How do you test if a window (by title) is already open from the command prompt?
  • how to trim audio file with specific time from text file by using SoX in Mac terminal?
  • UNet randomly disconnects
  • Why Android LocationManager has long delay before location updates start if setting accuracy
  • How to install the guess_language package in Python
  • c# - how to improve this “file explorer” startup speed?
  • How to handle concurrency with StorageFile operations?
  • My 'Explicit Wait' doesn't work but 'Implicit Wait' Works?
  • What is better way to delete file with condition
  • how to enable Wi-Fi and search for networks on windows phone emulator
  • Check if a program exists in bash
  • Fiware response 503 - Service unavailable
  • Automapper Enum Description Attribute
  • Shell Function string variables result blank after While loop and case?
  • Pandas - Counting quantity of commas in character field
  • BASH: Ctrl+C during input breaks current terminal
  • why does bash tell me that the command if is not found when I run the following code?
  • Git cherry pick those commits that contain a keyword (tracking id)
  • Can't get image::load_from_memory() to work when compiled to WebAssembly
  • Delete multiple lines - from “patternA” match, through second occurrence of “patternB”
  • Apple Reachability Notifications for Network OR Wi-Fi
  • C#, “Object reference not set to an instance of an object.” error
  • How to remove comma or any characters from Python dataframe column name
  • SSH in Bash Script Messing Up File Read
  • C# fibonacci function returning errors
  • How to view images from protected folder with php?
  • Textfile Structure (tables)
  • Display images in Django
  • Resize panoramic image to fixed size
  • Importing jscolor library in angular 2
  • embed rChart in Markdown
  • Can't mass-assign protected attributes when import data from csv file
  • How to get NHibernate ISession to cache entity not retrieved by primary key
  • How can I use `wmic` in a Windows PE script?
  • Unable to use reactive element in my shiny app