[SOLVED] Spit a column into multiple columns in bash

Issue

I am working on VM Linux Ubuntu and my goal is to form this input

4,4
3,8
4,2
4,8
6,8
5,1
4,5
6,9
4,2
5,4
5,9
5,9
7,2
4,9
4,4
5,1

to this output

4,4 6,8 4,2 7,2
3,8 5,1 5,4 4,9
4,2 4,5 5,9 4,4
4,8 6,9 5,9 5,1

And copy these values to an Excel file on Windows.

I wrote this command

cat text.txt |cut -f3| awk '1 ; NR%4==0 {printf"\n"} '|pr  -4 -t|column -t| tr -s '[:blank:]' '\t'

which works fine!

I counted the blanked lines to create each column as below : cat text.txt |cut -f3| awk '1 ; NR%4==0 {printf"\n"} '| awk '!NF {sum += 1} END {print sum}' --> I got 4 blanked lines, so I am adding 4 to this command |pr -4 -t|

My issue was when I had a larger file, let's say 160 lines (40 blanked lines). Initially, the modification on this part |pr -40 -t gave an error of pr: page width too narrow so I changed to

 cat text.txt |cut -f3| awk '1 ; NR%4==0 {printf"\n"} '| pr -W200 -40 -t | column -t| tr -s '[:blank:]' '\t'

which seems to work; however, the copy and paste merges 4 values in the 26th column, like

  4,83,6     
  5,66,2     
  6,57,6     
  8,18,1    

instead of

  4,8  3,6   
  5,6  6,2   
  6,5  7,6   
  8,1  8,1  

Even though I tried different values on the W option, I assume that -W200 is responsible for this issue. I have two questions.

1. Can a good proportion between the wide and blanked cells be found to fix this merging issue?
2. As my goal is to “paste” 4 lines next to each other, could someone help me to paste the values directly instead of counting the number of blanked lines first and then modifying the wide range as I did?

I really appreciate any help you can provide.

Solution

Instead of trying to reverse-engineer and understand OP's current code, and since OP is already using awk, I'd like to propose a single awk script to replace all of OP's current code:

awk -v n=4 '                   # set awk variable 'n' to the number of output lines
BEGIN { OFS="\t" }
      { sub(/\r$/,"")          # strip out a trailing dos/windows line ending "\r"
        ndx = NR%n
        lines[ndx] = lines[ndx] (NR <= n ? "" : OFS) $1
      }
END   { for (i=1; i<=n; i++) {
            print lines[i%n]
        }
      }
' text.txt

NOTE: you can remove the sub(/\r$/,"") if you know for a fact the input file will never contain dos/windows line endings (\r); if the file does not include \r line endings then the sub(/\r$/,"") becomes a no-op that does nothing (other than use up a few cpu cycles)

This generates:

4,4     6,8     4,2     7,2
3,8     5,1     5,4     4,9
4,2     4,5     5,9     4,4
4,8     6,9     5,9     5,1

Answered By - markp-fuso

Answer Checked By - Pedro (WPSolving Volunteer)