Bash: split single column of text into multiple columns

robcar · 07-22-2015, 04:40 AM

Hello,
I got a text file with a single column of numbers and separators (','):

Code:

I would like to split this single line into multiple columns, with the "," as separator, like this:

Code:

1,224332026,0
2,476719,0
3,103296846,0
4,199382801,0
5,95473897,30537653
6,313718457,106365914
7,90530066,0
8,103352501,0

How can I do that above?
Thank in advance.
--
rob

syg00 · 07-22-2015, 05:36 AM

Read each set into an array until you find the separator, then start next array. When all done, print the equivalent entry from all arrays.

Try it, when you have problems ask for assistance.

rtmistler · 07-22-2015, 08:14 AM

You can import that using spreadsheet program as data delimited by newlines versus spaces or commas and then export it back out as CSV.

eklavya · 07-23-2015, 03:49 AM

Is this what you are asking?

Code:

awk 'BEGIN {FS="\n"; RS=",\n";}{ORS="\n"; print $1,$2,$3,$4,$5,$6,$7,$8;}' file.txt | awk '{for(c = 1; c <= NF; c++) {a[c, NR] = $c}if(max_nf < NF) {max_nf = NF}}END {for(r = 1; r <= max_nf; r++) {for(c = 1; c <= NR; c++) { printf("%s ", a[r, c])}print ""}}'

robcar · 07-23-2015, 04:19 AM

Wow! Seems a little complicated to me

I'll have to study it well.
It doesn't seem to work, what I got is this:

Quote:

s s s
s s s
s s s
s s s
s s s
s s s
s s s
s s s

More important is that I could have different files, with more than 8 'rows' (not more than 150 though - they are ethernet switch ports) and it seems that your code is not contemplating that.
Thanks btw.

eklavya · 07-23-2015, 04:22 AM

There is issue with linuxquestions.org text editor, you can not write percent s, percent disappears automatically.
Only Advanced editor writes percent symbol.

robcar · 07-23-2015, 05:58 AM

You're right!
Your script works as expected with a file with 8 rows/records:

Quote:

1 224332026 0
2 476719 0
3 103296846 0
4 199382801 0
5 95473897 30537653
6 313718457 106365914
7 90530066 0
8 103352501 0

Now I only have to understand how to modify it when I have files with more rows/records.
thank you.

eklavya · 07-23-2015, 08:27 AM

Very bad way to use pipes with awk but..... I hope Awk creators will not see this.
Try this for different number of columns and rows.

Code:

awk '{for(c = 1; c <= NF; c++) {a[c, NR] = $c}if(max_nf < NF) {max_nf = NF}}END {for(r = 1; r <= max_nf; r++) {for(c = 1; c <= NR; c++) { printf("%s ",a[r,c])}print ""}}' file.txt | awk 'BEGIN {RS=","; FS=" ";}{print $0;}' | awk '{for(c = 1; c <= NF; c++) {a[c, NR] = $c}if(max_nf < NF) {max_nf = NF}}END {for(r = 1; r <= max_nf; r++) {for(c = 1; c <= NR; c++) { printf("%s ", a[r, c])}print ""}}'

Replace file.txt with your file path.

schneidz · 07-23-2015, 08:46 AM

i would find out the line number where the first ^,$ is located (grep -n).
then use split -l to create three different files.
then combine them with paste -d ,.