Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Sorting complex file with awk

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Sorting complex file with awk
# 1  
Old 02-11-2011
Sorting complex file with awk
i have a file ddd.txt
its delimiter is : but has , and "" within each column as below and also each line ends with ;

Code:
I_EP,"29":I_US,"120":I_P_ID,"2020":I_NEW,"600":I_OLD,"400":I_POW,"4.5":I_NAME,"TOM";
I_ZP,"39":I_PEN,"234":I_US,"120":I_P_ID,"2020":I_NEW,"700":I_OLD,"250":I_POW,"4.5":I_NAME,"TOM":I_LO,"123";
I_KP,"39":I_NOW,"234":I_RAT,"000":I_US,"120":I_P_ID,"2020":I_NEW,"800":I_OLD,"650":I_POW,"4.5":I_NAME,"TOM":I_LO,"123";

IF I_US,"120" and I_P_ID,"2020" can be found in any column of each line then print its corresponding value in double quotes
in the format

Code:
I_US,I_P_ID,I_NEW,I_OLD,I_NEW - I_OLD

So it should finally be

Code:
120,2020,600,400,200
120,2020,700,250,450
120,2020,800,650,150

# 2  
Old 02-11-2011
Code:
awk '{
x=gensub(/(.*":I_US,")([0-9]+)(":I_P_ID,")([0-9]+)(":I_NEW,")([0-9]+)(":I_OLD,")([0-9]+)(.*)/,"\\2,\\4,\\6,\\8",$0)}{split(x,a,",")
}
{
for (i=1;i<=4;i++) printf a[i]","
}
{print a[3]-a[4]}' file
120,2020,600,400,200
120,2020,700,250,450
120,2020,800,650,150

Last edited by yinyuemi; 02-11-2011 at 06:09 PM..
# 3  
Old 02-11-2011
Yinyueni,
The last column does not match what i want, i need column 3 - column 4, yours is just a repetition of column 4
# 4  
Old 02-11-2011
Hi,

I have modified as you said, please try it.

Y
# 5  
Old 02-11-2011
i got it right, thank you.
if the lines become more that is more columns are added, will i have to use the same script to get my results?

what does ,"\\2,\\4,\\6,\\8",$0)}{split(x,a,",") do?

---------- Post updated at 06:04 PM ---------- Previous update was at 05:41 PM ----------

i want to add the column I_NAME to the fifth column as
Code:
I_US,I_P_ID,I_NEW,I_OLD,I_NAME,I_NEW - I_OLD

so output should be

Code:
120,2020,600,400,TOM,200
120,2020,700,250,TOM,450
120,2020,800,650,TOM,150

Last edited by blackzinga80; 02-11-2011 at 06:50 PM..
# 6  
Old 02-11-2011
try this way:

Code:
 awk '{gsub("\"","")}/I_US/{printf $2","}/I_P_ID/{printf $2","}/I_NEW/{I_NEW=$2;printf $2","}/I_OLD/{I_OLD=$2;printf $2","}/I_NAME/{printf $2",";print I_NEW-I_OLD}' RS=":|;" FS="," file
120,2020,600,400,TOM,200
120,2020,700,250,TOM,450
120,2020,800,650,TOM,150

# 7  
Old 02-11-2011
This is perfect Yinyuemi
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Sorting/Arranging file based on tags using awk

Hi, I have file which contains data based on tags. Output of the file should be in order of tags. Below are the files : Tags.txt f12 f13 f23 f45 f56 Original data is like this : Data.txt 2017/01/04|09:07:00:021|R|XYZ|38|9|1234|f12=CAT|f23=APPLE|f45=PENCIL|f13=CAR... (5 Replies)
Discussion started by: Prathmesh
5 Replies

2. Programming

How to replace the complex strings from a file using sed or awk?

Dear All, I am having a requirement to find the difference between 2 files and generate a discrepancy report out of it as an html page. I prefer using diff -y file1 file2 since it gives user friendly layout to know any discrepancy in the record and unique records among the 2 file. Here's how it... (12 Replies)
Discussion started by: Badhrish
12 Replies

3. Shell Programming and Scripting

Complex awk problem

hello, i have a complex awk problem... i have two tables, one with a value (0 to 1) and it's corresponding p-value, like this: 1. table: ______________________________ value p-value ... ... 0.254 0.003 0.245 0.005 0.233 0.006 ... ... ______________________________ and a... (6 Replies)
Discussion started by: dietmar13
6 Replies

4. Shell Programming and Scripting

Help with Complex Awk.

Hi, I have a file. In this file when ever the word "ABC" occurs at position from 25 and 34 I would like to replace the value at postion 100 to 5 for the first 1000 rows only. I have written the following Awk command. nawk 'substr($0,25,9)=="ABC" {print $0}' filename The above command... (4 Replies)
Discussion started by: pinnacle
4 Replies

5. Shell Programming and Scripting

echoing complex awk command into file fails

Using hp-ux's shell, I'm trying to echo a complex awk command into a script file for later use. But it fails on a newline character and splits the rest of the command onto the next line. echo ' printf("%s: TOTAL = %18.0lf\n", FILENAME, TOTAL) >> "TOTAL.TXT";' >>awk.script Looks... (3 Replies)
Discussion started by: Scottie1954
3 Replies

6. Shell Programming and Scripting

Complex data sorting in excel files or text files

Dear all, I have a complex data file shown below,,,,, A_ABCD_13208 0 0 4.16735 141044 902449 1293900 168919 C_ABCD_13208 0 0 4.16735 141044 902449 1293900 168919 A_ABCDEF715 52410.9 18598.2 10611 10754.7 122535 252426 36631.4 C_DBCDI_1353 0... (19 Replies)
Discussion started by: AAWT
19 Replies

7. Shell Programming and Scripting

complex Awk Question

Hi, I have a file look likes this : --->start hir Trace file: pudwh_ora_9998.trc Sort options: fchela exeela ***************************************************************count = number of times OCI procedure was executed cpu = cpu time in seconds executing elapsed = elapsed... (3 Replies)
Discussion started by: yoavbe
3 Replies

8. Shell Programming and Scripting

Splitting a complex file using awk

I have a file that contains the following format delete from table1; delete from table2; insert into table1 (col1, col2) values (value1, value2)@ insert into table1 (col1, col2) values(value3, value4)@ insert into table2(col1, col2,col3) values(value1, value2, value3)@ etc etc This is... (9 Replies)
Discussion started by: hukcjv
9 Replies

9. Shell Programming and Scripting

Complex use with awk

Hi , I have file named docs.txt The content of the file look like this: DOC disk location Size ======= ===== ============= ========= TXT A /dev/dm-1 10 TXT B /dev/dm-2 10 BIN C ... (3 Replies)
Discussion started by: yoavbe
3 Replies

10. Shell Programming and Scripting

awk error in sorting text file

Hi Having a file as below file.txt error Server Network Name Dept Date Time =========================================================================================================================== 0 ServerA LAN1 AAA IT01 04/30/2008 09:16:26 0 ... (3 Replies)
Discussion started by: karthikn7974
3 Replies
Login or Register to Ask a Question