Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

reformat the file

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting reformat the file
# 1  
Old 03-08-2005
reformat the file
Hi all,

I ran into this problem, hope you can help

I have a text file like this:
Code:
Spriden ID  First Name       Last Name                                                     Term Code  Detail Code                   Amount  Trans Date               Description                     
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 000702986   Yukiko           Adachi                                                        200520     EASY                           20.00  2005-03-07 12:00AM       SCAD Debit Card Charge        
000723223   Casey            Jothen                                                        200530     EASY                          600.00  2005-03-08 12:00AM       SCAD Debit Card Charge

and when i run the awk program to format the file, i got this result:
Code:
V2
U000634426          Kwok, Celine                           01/01/10                                                                                                                          SCAD Group Inc.                                                                                                                                                                   PA01001000100100PA01011010101101AD0100100240.0000
U000722952          Moore, John                            01/01/10                                                                                                                          SCAD Group Inc.                                                                                                                                                                   PA01001000100100PA01011010101101AD0100100300.0000

Is there a way I can get rid of the ".00" (those that I have in bold font) from the dollar amount and have the result like this instead:
Code:
V2
U000634426          Kwok, Celine                           01/01/10                                                                                                                          SCAD Group Inc.                                                                                                                                                                   PA01001000100100PA01011010101101AD010010024000
U000722952          Moore, John                            01/01/10                                                                                                                          SCAD Group Inc.                                                                                                                                                                   PA01001000100100PA01011010101101AD010010030000

Here is my awk program:
Code:
FNR==1{print "V2"} 
$6 > 0 && (( $9 $10 $(NF-1) $(NF) ) == "SCADDebitCardCharge" ) {
  _i=$3 ", " $2; _x=$6 "00"; printf("U%-19s%-39s%-130s%-178s%-41s%-6s\n", $1, _i, "01/01/10", "SCAD Group Inc.", "PA01001000100100PA01011010101101AD0100100", _x)
}

Thanks for any helps

CT
# 2  
Old 03-08-2005
First, you example output is getting differed during execution.

20.00
will be printed as,
PA01001000100100PA01011010101101AD010010020.00

so that use as,

Code:
FNR==1{print "V2"}
$6 > 0 && (( $9 $10 $(NF-1) $(NF) ) == "SCADDebitCardCharge" ) {
  _i=$3 ", " $2; split($6,a,"."); printf("U%-19s%-39s%-130s%-178s%-41s%-6s\n", $1, _i, "01/01/10", "SCAD Group Inc.", "PA01001000100100PA01011010101101AD0100100", a[1]"00")
}

hth.
# 3  
Old 03-08-2005
Thanks muthukumar Smilie

CT
# 4  
Old 03-09-2005
muthukumar,

I ran into another problem when some different data was input, my input file look like this:

Code:
Spriden ID	First Name	Last Name	Term Code	Detail Code	Amount	Trans Date	Description
000681047	Allison		Ancel		200520		EASY		-600.00	 3/9/05 0:00	wrong term
000681047	Allison		Ancel		200520		EASY		 600.00	 3/9/05 0:00	SCAD Debit Card Charge
000681047	Allison		Ancel		200530		EASY		 600.00	 3/9/05 0:00	SCAD Debit Card Charge
000699678	Ashley		Willem		200520		EASY		 650.00	 3/9/05 0:00	SCAD Debit Card Charge
000702927	Charla		Peters		200520		EASY		 260.00	 3/9/05 0:00	SCAD Debit Card Charge
000672954	Emily		Voegtlin	200520		EASY		   5.00	 3/9/05 0:00	SCAD Debit Card Charge
000657138	Jimmy		Gilbert		200530		EASY		 600.00	 3/9/05 0:00	SCAD Debit Card Charge
000692343	Paul		Martinez	200530		EASY		-200.00	 3/9/05 0:00	Decide not to deposit
000692343	Paul		Martinez	200530		EASY		 200.00	 3/9/05 0:00	Do not Deposit

I don't want the duplicate entries to print to the output file, ie. those with same ID (arg 1) and have an entry for positive charge (arg 6) and negative charge (arg 6) should be cancel out, then only the one left with positive charge with "SCAD debit Card Charge" will be print to the output file.
For instant, the first 2 entries and the last 2 entries are cancelled out from the input file. The output i want is like this:

Code:
V2
U000681047          Ancel, Allison                         01/01/10                                                                                                                          SCAD Group Inc.                                                                                                                                                                   PA01001000100100PA01011010101101AD010010060000 
U000699678          Willem, Ashley                         01/01/10                                                                                                                          SCAD Group Inc.                                                                                                                                                                   PA01001000100100PA01011010101101AD010010065000 
U000702927          Peters, Charla                         01/01/10                                                                                                                          SCAD Group Inc.                                                                                                                                                                   PA01001000100100PA01011010101101AD010010026000 
U000672954          Voegtlin, Emily                        01/01/10                                                                                                                          SCAD Group Inc.                                                                                                                                                                   PA01001000100100PA01011010101101AD0100100500   
U000657138          Gilbert, Jimmy                         01/01/10                                                                                                                          SCAD Group Inc.                                                                                                                                                                   PA01001000100100PA01011010101101AD010010060000

again, here is my awk code:
Code:
#!/bin/awk -f

FNR==1{print "V2"}
$6 > 0 && (( $9 $10 $(NF-1) $(NF) ) == "SCADDebitCardCharge" ) {
  _i=$3 ", " $2; split($6,a,"."); printf("U%-19s%-39s%-130s%-178s%-41s%-6s\n", $1, _i, "01/01/10", "SCAD Group Inc.", "PA01001000100100PA01011010101101AD0100100", a[1]"00")
}

Is it posible to do it like that? Thanks

CT
Last edited by CamTu; 03-09-2005 at 05:10 PM..
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

awk to reformat output if input file is empty, but not if file has data in it

The below awk improved bu @MadeInGermany, works great as long as the input file has data in it in the below format: input chrX 25031028 25031925 chrX:25031028-25031925 ARX 631 18 chrX 25031028 25031925 chrX:25031028-25031925 ARX 632 14... (3 Replies)
Discussion started by: cmccabe
3 Replies

2. Shell Programming and Scripting

Reformat csv file

Hi, I have a csv file with content like: 1,0,100 1,1,150 2,0,200 2,1,250 3,0,300 3,1,350 I want an output such that all numbers in 3rd col where 2nd col is "0" come in the same col in the output. The same goes for numbers where 2nd col is "1". 1 100 150 2 200 250 3 300 350 Tnx... (2 Replies)
Discussion started by: jamaje
2 Replies

3. Shell Programming and Scripting

[Solved] File reformat

I am using the code below to reformat the input (hp.txt). The output (newhp.txt) is not in the desired format and I can not seem to figure it out. I have attached both. Thank you. perl -aF/\\t/ -lne 'print join(" ",@F) for ("0 A","0 G","0 C","0 T","A 0","G 0","C 0","T 0")' hp.txt > newhp.txt ... (4 Replies)
Discussion started by: cmccabe
4 Replies

4. Shell Programming and Scripting

awk reformat file

Hello: When I tried a perl-oneliner to re-format fasta file. infile.fasta >YAL069W-1.334 Putative promoter CCACACCACACCCACACACC ACACCACACCCACACACACA ACAGCCCTAATCTAACCC >YAL068C-7235.2170 Putative ABC sequence TACGAGAATAATTT ACGTAAATGAAGTT TATATATAAA >gi|31044174|gb|AY143560.1|... (15 Replies)
Discussion started by: yifangt
15 Replies

5. Shell Programming and Scripting

Major File Reformat

Hello, I have many lengthy files that need to be reformatted. I was hoping a sed or awk script could fix this. Here is an example of the original format: P0037 # Degree: 32.999981 # COMMAND: 03 (#01A) Scale 1.296875, 52 (Wooden Crate w/ #2 Label, Bahko) v -3328.000000 12.101541 437.000000... (2 Replies)
Discussion started by: Blue Solo
2 Replies

6. Shell Programming and Scripting

Reformat file using nawk

Hi all, I have a file with records that look something like this, "Transaction ID",Date,Email,"Card Type",Amount,"NETBANX Ref","Root Ref","Transaction Type","Merchant Ref",Status,"Interface ID","Interface Name","User ID" nnnnnnnnn,"21 Nov 2011 00:10:47",someone@hotmail.co.uk,"Visa... (2 Replies)
Discussion started by: dazedandconfuse
2 Replies

7. Shell Programming and Scripting

Reformat a file

I have a csv file with 11 columns. The first columns contains the User Id. One User id can have multiple sub Id. The value of Sub Id is in column 10. 100026,captjason@hawaii.rr.com ,jason ,wolford ,1/16/1969, ,US, ,96761 ,15 ,seg_id 100026,captjason@hawaii.rr.com ,jason ,wolford ,1/16/1969,... (3 Replies)
Discussion started by: r_t_1601
3 Replies

8. Shell Programming and Scripting

Reformat the data of a file.

I have a file which have data like A.txt a 1Jan I am in a1. 1Jan I was born. 2Jan I am here. 3Jan I am in a3. b 1Jan I am in b1. c 2Jan I am in c2. d 2Jan I am in d2. 5jan I am in d5. date in the file might be vary evertime. (9 Replies)
Discussion started by: samkhu
9 Replies

9. Shell Programming and Scripting

Please help me reformat this file

I am working with a file of the form; 4256 7726 1 6525 716 1 7626 0838 1 8726 7623 2 8625 1563 2 1662 2628 3 1551 3552 3 1542 7984 ... (3 Replies)
Discussion started by: digipak
3 Replies

10. Shell Programming and Scripting

Reformat Crontab file

I need help writing a script that will reformat a crontab file. The first thing the script is doing is a crontab -l > crontab.txt. I need the crontab.txt file to read "8.00 PM every weekday (Mon-Fri) only in Oct." instead of the orig format "0 20 * 10 1-5" (1 Reply)
Discussion started by: alnita
1 Replies
Login or Register to Ask a Question