rearrange the column names with comma as column delimiter


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting rearrange the column names with comma as column delimiter
# 1  
Old 09-09-2010
rearrange the column names with comma as column delimiter

Hi,
I am new to shell scripting, i have requirement can any one help me out in this regrads,
in directory i have file like invoice1.txt, invoice2.txt in each file i have fixed number of columns, 62 in number but they are randomly arranged.like
for first file invoice1.txt can have columns
col1,col2,col3,col4,col5......col62
secon file can have
col1,col3,col2.....................(upto62cols)
first line of the file has column names.
i need to arrange the columns in fixed columns regard less of the order the files are:
col1,col3,col5...................col61,col2,col4............col62(total column 62)
can any one suggest me script to run on AIX platform.
# 2  
Old 09-10-2010
Hmm..., simple the solution might be, but difficult to understand what you want is.

Could you post portion of the actual relevant files, and an example of the real expected output?
# 3  
Old 09-11-2010
example file:
col1,col2,col3,col4,col5......col62
1,a1,a2,a3,a4...................a61
2,b1,b2,b3,b4...................b61

expected file lets say:
Col62,col61,col60...................col1
a61,a60.................................1
b61,b60.................................2
# 4  
Old 09-11-2010
Hi

Code:
awk '{for(i=NF;i!=0;i--)if(i==1)print $i;else printf("%s,", $i);}' FS=,  file

Guru.
# 5  
Old 09-11-2010
Small variation:
Code:
awk -F, '{for(i=NF;i>1;i--) printf $i FS; print $1}' infile

# 6  
Old 09-11-2010
Code:
$ 
$ # Example file
$ cat f0.example
col1,col2,col3,col4,col5
a1,a2,a3,a4,a5
b1,b2,b3,b4,b5
$ 
$ # File with shuffled columns
$ cat f0.shuffled
col5,col3,col2,col4,col1
a5,a3,a2,a4,a1
b5,b3,b2,b4,b1
c5,c3,c2,c4,c1
d5,d3,d2,d4,d1
e5,e3,e2,e4,e1
$ 
$ # Run the Perl script, passing the example file and shuffled data file.
$ # The script reshuffles the header and data of f0.shuffled so that
$ # it matches the order of the header columns of f0.example.
$ perl -lne 'if ($.==1 && $#ARGV == 0) {
             for $k (split/,/) {$x{$k} = $i++}
           } elsif ($.==1) {
             for $k (split/,/) {push @a,$x{$k}; $b[$x{$k}] = $k}
             print join ",",@b; @b=();
           } elsif ($#ARGV == -1) {$i = 0;
             for $k (split/,/) {$n = $a[$i]; $b[$n] = $k; $i++}
             print join ",",@b; @b = ();
           }
           close ARGV if eof;
          ' f0.example f0.shuffled
col1,col2,col3,col4,col5
a1,a2,a3,a4,a5
b1,b2,b3,b4,b5
c1,c2,c3,c4,c5
d1,d2,d3,d4,d5
e1,e2,e3,e4,e5
$ 
$ # Another file with (differently) shuffled columns
$ cat f1.shuffled
col3,col5,col4,col1,col2
a3,a5,a4,a1,a2
b3,b5,b4,b1,b2
$ 
$ # Run the Perl script, passing the example file and shuffled data file.
$ # The script reshuffles the header and data of f1.shuffled so that
$ # it matches the order of the header columns of f0.example.
$ perl -lne 'if ($.==1 && $#ARGV == 0) {
             for $k (split/,/) {$x{$k} = $i++}
           } elsif ($.==1) {
             for $k (split/,/) {push @a,$x{$k}; $b[$x{$k}] = $k}
             print join ",",@b; @b=();
           } elsif ($#ARGV == -1) {$i = 0;
             for $k (split/,/) {$n = $a[$i]; $b[$n] = $k; $i++}
             print join ",",@b; @b = ();
           }
           close ARGV if eof;
          ' f0.example f1.shuffled
col1,col2,col3,col4,col5
a1,a2,a3,a4,a5
b1,b2,b3,b4,b5
$ 
$ 
$ 

tyler_durden

Last edited by durden_tyler; 09-11-2010 at 05:25 AM..
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

A cleaner way to rearrange column

Hello, I have some tab delimited text data, index name chg_p chg_m 1 name,1 1 0 2 name,2 1 1 3 name,3 1 0 4 name,4 1 0 5 name,5 1 1 I need to duplicate the "index" column, call it "id" and insert it after the... (8 Replies)
Discussion started by: LMHmedchem
8 Replies

2. Shell Programming and Scripting

Bring values in the second column into single line (comma sep) for uniq value in the first column

I want to bring values in the second column into single line for uniq value in the first column. My input jvm01, Web 2.0 Feature Pack Library jvm01, IBM WebSphere JAX-RS jvm01, Custom01 Shared Library jvm02, Web 2.0 Feature Pack Library jvm02, IBM WebSphere JAX-RS jvm03, Web 2.0 Feature... (10 Replies)
Discussion started by: kchinnam
10 Replies

3. Shell Programming and Scripting

Insert a new column with sequence number (Delimiter as comma)

Hi All, I have a file which has data like a,b c,d e,f g,h And I need to insert a new column at the begining with sequence no( 1 to n) 1,a,b 2,c,d 3,e,f 4,g,h Please let me know how to acheive this in unix (3 Replies)
Discussion started by: weknowd
3 Replies

4. Shell Programming and Scripting

How to use regex on particular column (Removing comma from particular column)?

Hi, I have pipe separated file which contains some data having comma(,) in it. I want to remove the comma(,) only from particular column without changing data in other columns. Below is the sample data file, I want to remove the comma(,) only from 5th column. $ cat file1 ABC | DEF, HIJ|... (6 Replies)
Discussion started by: Prathmesh
6 Replies

5. Shell Programming and Scripting

Ignore delimiter within a column

Hi, So I was trying this awk snippet awk -F, '$1 ~ /Match/' File This is my sample file output Name,Age,Nationality,Description Jack,20,American,Tall, caucasian, lean Mary,30,British,Short,white,slim I would expect expected Output to be, when a certain match is met say $1 is... (2 Replies)
Discussion started by: sidnow
2 Replies

6. Shell Programming and Scripting

Substituting comma "," for dot "." in a specific column when comma"," is a delimiter

Hi, I'm dealing with an issue and losing a lot of hours figuring out how i would solve this. I have an input file which looks like this: ('BLABLA +200-GRS','Serviço ','TarifaçãoServiço','wap.bla.us.0000000121',2985,0,55,' de conversão em escada','Dia','Domingos') ('BLABLA +200-GRR','Serviço... (6 Replies)
Discussion started by: poliver
6 Replies

7. Shell Programming and Scripting

Column to row with delimiter

Hi, i have data in a file f1.txt a b c d and i want to print the above column values in single line with a delimiter, say ',' The output should look like: a,b,c,d I could find rows to columns help online but not vice versa Thanks, -srinivas yelamanchili (4 Replies)
Discussion started by: ysrini
4 Replies

8. Shell Programming and Scripting

Transpose field names from column headers to values in one column

Hi All, I'm looking for a script which can transpose field names from column headers to values in one column. for example, the input is: IDa;IDb;IDc;PARAM1;PARAM2;PARAM3; a;b;c;p1val;p2val;p3val; d;e;f;p4val;p5val;p6val; g;h;i;p7val;p8val;p9val; into the output like this: ... (6 Replies)
Discussion started by: popesk
6 Replies

9. Shell Programming and Scripting

Match column 3 in file1 to column 1 in file 2 and replace with column 2 from file2

Match column 3 in file1 to column 1 in file 2 and replace with column 2 from file2 file 1 sample SNDK 80004C101 AT XLNX 983919101 BB NETL 64118B100 BS AMD 007903107 CC KLAC 482480100 DC TER 880770102 KATS ATHR 04743P108 KATS... (7 Replies)
Discussion started by: rydz00
7 Replies

10. Shell Programming and Scripting

Change names in a column based on the symbols in another column

If the 4th column has - sign then the names in 3rd column has to change to some user defined names (as shown in output). Thanx input1 1 a aaaaa + 2 b bbbbb + 3 c ccccc + 4 d ddddd + 5 e eeeee + 6 f xxxxx + 8 h hhhhh +... (8 Replies)
Discussion started by: repinementer
8 Replies
Login or Register to Ask a Question

Featured Tech Videos