Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Reorder of fields

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Reorder of fields
# 1  
Old 05-04-2017
Reorder of fields
I need to reorder the fields of an input file. I was using the following awk statement but I am stumped on how to get it exactly as I need.

I need the output to be tab delimited except for the CITY, NAME and ID. Those last set of columns should be a single field separated by a space. And they are variable in length, ie, can be 1 or 5+ columns in length.

So basically fields 1-7 and the last 2 fields put in their proper positions tab delimited, then put everything else in the last field space delimited.

Code:
awk '{ print $1,$8,$2,$(NF-1),$(NF),$3,$4,$5$6$7,$1="",$2="",$3="",$4="",$5="",$6="",$7="",$8="",$0 }'

Input
Code:
500 XYZ3849 2 30 1964 09 17 ST CITY NAME AP ID 37 -84
500 XYZ3850 3 40 1998 10 27 ST CITY NAME AP 37 -84
500 XYZ3851 4 50 2013 05 7 ST CITY NAME 37 -84

Needed Output
Code:
500    ST    XYZ3849    37    -84    2    30    19640917    CITY NAME AP ID
500    ST    XYZ3850    37    -84    3    40    19981027    CITY NAME AP 
500    ST    XYZ3851    37    -84    4    50    20130507    CITY NAME

Any help is appreciated.

Thanks!
# 2  
Old 05-04-2017
Where is the "KY" coming from?

You can "cheat" by pasting together multiple separators without commas. They'll be concatenated instead of separated by OFS.

Code:
awk -v OFS="\t" '{ print "tab", "separated", "space separated " $1 " " $2 " " $3 }' input

I don't know why you're printing all the $8="" and things. If you just leave them out, they won't be printed at all, you don't need to unset them.

$0 is a special variable meaning 'entire line'. Not sure why that's in there either.
# 3  
Old 05-04-2017
Quote:
Originally Posted by Corona688
Where is the "KY" coming from?
Oversight that has since been corrected.
# 4  
Old 05-04-2017
Code:
awk -v OFS="\t" '{
        S=""
        for(N=9; N<=(NF-2); N++) S = S " " $N # Paste fields 9 - (NF-2)
        print $1, $8, $2, $(NF-1), $NF, $3, $4, $5 $6 $7, substr(S,2);
}' spacesep

# 5  
Old 05-04-2017
for 2 digits $6 $7:
Code:
print $1, $8, $2, $(NF-1), $NF, $3, $4, $5 sprintf("%02d%02d",$6,$7), substr(S,2);

# 6  
Old 05-04-2017
Thanks to both of you.

I incorporated both your suggestions and had to create a tab separation between
Code:
37 -84

This is what I think will work for the long term.

Code:
awk -v OFS="\t" '{
        S=""
        for(N=9; N<=(NF-2); N++) S = S " " $N # Paste fields 9 - (NF-2)
        print $1, $8, $2, $(NF-1),"\t"$NF, $3, $4, $5 sprintf("%02d%02d",$6,$7), substr(S,2);
}'

This User Gave Thanks to ncwxpanther For This Post:
Corona688
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Is there a UNIX command that can compare fields of files with differing number of fields?

Hi, Below are the sample files. x.txt is from an Excel file that is a list of users from Windows and y.txt is a list of database account. $ head -500 x.txt y.txt ==> x.txt <== TEST01 APP_USER_PROFILE USER03 APP_USER_PROFILE TEST02 APP_USER_EXP_PROFILE TEST04 APP_USER_PROFILE USER01 ... (3 Replies)
Discussion started by: newbie_01
3 Replies

2. Shell Programming and Scripting

awk to reorder lines in file

The output of an awk script is the below file. Line 1,3 that starts with the Ion... need to be under line 2,4 that starts with R_. The awk runs but no output results. Thank you :). file IonXpress_007 MEV37 R_2016_09_20_12_47_36_user_S5-00580-7-Medexome IonXpress_007 MEV40... (6 Replies)
Discussion started by: cmccabe
6 Replies

3. Shell Programming and Scripting

awk sort based on difference of fields and print all fields

Hi I have a file as below <field1> <field2> <field3> ... <field_num1> <field_num2> Trying to sort based on difference of <field_num1> and <field_num2> in desceding order and print all fields. I tried this and it doesn't sort on the difference field .. Appreciate your help. cat... (9 Replies)
Discussion started by: newstart
9 Replies

4. UNIX for Dummies Questions & Answers

Reorder column in Unix

I have a flat file with the 3 columns and separate by tab delimiter, and the last column with special character. A B C D F G Now I would like to swap the third column with second column to following format: A B C D F G I know this cannot be done in Unix command with using cut... (3 Replies)
Discussion started by: newbie2011
3 Replies

5. Shell Programming and Scripting

Reorder and insert column using awk

Hi friends, I am beginner in shell scripting. I am trying to modify (Reorder and insert column) a BIG .txt file using awk to create a .bim file. My start file has 25 columns But I need to make a new file with only 5 columns from the start file and insert a new column in position 3 with value... (5 Replies)
Discussion started by: smitra
5 Replies

6. Shell Programming and Scripting

Reorder the Cut characters

Hi, I have a fixed width flatfile, I want to view this file specific to it's character position and in order I want to...example as below ABCDE.txt 01COLTSMANNING18 02PATS BRADY 12 03PACKSROGERS 12I used unix cut command to see specific field based on length but unable to order them as... (6 Replies)
Discussion started by: okkadu
6 Replies

7. Shell Programming and Scripting

reading comma separated data and reorder

hey guys! i need to read data from a file that are comma separated then reorder them in another file to be generated for example: x,y,z a,b,c l,m,n o,p,q and transform this into: x,a,l,o y,b,m,p z,c,n,q Will appreciate your fast reply Regards! (5 Replies)
Discussion started by: maiooi90
5 Replies

8. Shell Programming and Scripting

Bash script to reorder csv

hi guys, im fairly new to unix and bash scripts and therefore your help would really be appreciated. i need to write a bash script that will take a csv file, and reorder the data and output to another csv file. The source csv file will look something like this: HEAD,671061,Add,SS... (3 Replies)
Discussion started by: daz_20
3 Replies

9. Shell Programming and Scripting

awk sed cut? to rearrange random number of fields into 3 fields

I'm working on formatting some attendance data to meet a vendors requirements to upload to their system. With some help on the forums here, I have the data close. But they've since changed what they want. The vendor wants me to submit three fields to them. Field 1 is the studentid field,... (4 Replies)
Discussion started by: axo959
4 Replies

10. Shell Programming and Scripting

Reorder the sequence of line groupings/QIF export

Hi All, I need to reorder the sequence of line groupings - specifically the output from a bank QIF (Quicken Interchange Format) export. Sample is like this: !Type:Bank D12/05/2008 T-10.00 N1 Details of Charge 1 ^ D07/05/2008 T-20.00 N2 Details of Charge 2 ^ D17/04/2008 T-30.00 (0 Replies)
Discussion started by: mark101
0 Replies
Login or Register to Ask a Question