modify a few field of the record information

happyv · #1 (**permalink**) 03-22-2007

Hello,
I have the following record in a text file, i would like modify some field:
1 - remove all space between ",", but the company name of word will not delete. Anyway, I can use the following statement to do it.
's/^ *//;s/ *, */,/g;s/ *$//' file

2. field #12, I need to modify to time format, for example, the following record is "0" to "0:00" or "12" to "12:00"

3. field #24, the value "AUS" - I have another text table (see below), which need to map to full name "Australia"

4. field #25 is the payment currency, if the field is empty, it will look up the text table to put the correct currency

00001,LOTUS,Peter cooking Pty Ltd ,02349,N ,162:39 , 102, 78.426000, 0.000000, 78.426000, 0.000000,0 , 0, 0.000000, 0.000000, 0.000000, 0.000000,162:39 , 102, 78.426000, 0.000000, 78.426000, 0.000000,AUS ,AUD

text table:
AUS Australia AUD
IND India USD
CAN Canada USD

ghostdog74 · #2 (**permalink**) 03-22-2007

here's a rather crude way ( got to fine tune on your own )

Code:

awk -F"," ' BEGIN { OFS=","
	    lookup["AUS"]="Australia";
	    lookup["Australia"] = "AUD"
	    lookup["IND"] = "India"
	    lookup["India"] = "USD"
	    lookup["CAN"] = "Canada" 
	    lookup["Canada"] = "USD"
	    }
	    {	    gsub(/ ,/ , ",", $0 ) #get rid of blanks 
		    gsub(/, / , ",", $0 )
                    gsub(/ , /, "," , $0)
	    }	  
	    $12 ~ /[0-9]/  { $12 = $12":00" }	    
	    { $24 = lookup[$24]	}
	    $25 == "" { $25 = lookup[$24] }
	    { print $0 }
' file

happyv · #3 (**permalink**) 03-23-2007

Quote:

Originally Posted by ghostdog74

here's a rather crude way ( got to fine tune on your own )

Code:

awk -F"," ' BEGIN { OFS=","
	    lookup["AUS"]="Australia";
	    lookup["Australia"] = "AUD"
	    lookup["IND"] = "India"
	    lookup["India"] = "USD"
	    lookup["CAN"] = "Canada" 
	    lookup["Canada"] = "USD"
	    }
	    {	    gsub(/ ,/ , ",", $0 ) #get rid of blanks 
		    gsub(/, / , ",", $0 )
                    gsub(/ , /, "," , $0)
	    }	  
	    $12 ~ /[0-9]/  { $12 = $12":00" }	    
	    { $24 = lookup[$24]	}
	    $25 == "" { $25 = lookup[$24] }
	    { print $0 }
' file

After run your statement, the field #12 showing ":00" which is not my my result. The result should be "0:00". any advise?

ghostdog74 · #4 (**permalink**) 03-23-2007

my sample input is taken from your example

Code:

# more file
00001,LOTUS,Peter cooking Pty Ltd ,02349,N ,162:39 , 102, 78.426000, 0.000000, 78.426000, 0.000000,0 , 0, 0.00
0000, 0.000000, 0.000000, 0.000000,162:39 , 102, 78.426000, 0.000000, 78.426000, 0.000000,AUS ,AUD

output:

Code:

./test.sh
00001,LOTUS,Peter cooking Pty Ltd,02349,N,162:39,102,78.426000,0.000000,78.426000,0.000000,0:00,0,0.000000,0.000000,0.000000,0.000000,162:39,102,78.426000,0.000000,78.426000,0.000000,Australia,AUD

field 12 is changed (red). I am not sure why yours don't.

happyv · #5 (**permalink**) 03-23-2007

Quote:

Originally Posted by ghostdog74

my sample input is taken from your example

Code:

# more file
00001,LOTUS,Peter cooking Pty Ltd ,02349,N ,162:39 , 102, 78.426000, 0.000000, 78.426000, 0.000000,0 , 0, 0.00
0000, 0.000000, 0.000000, 0.000000,162:39 , 102, 78.426000, 0.000000, 78.426000, 0.000000,AUS ,AUD

output:

Code:

./test.sh
00001,LOTUS,Peter cooking Pty Ltd,02349,N,162:39,102,78.426000,0.000000,78.426000,0.000000,0:00,0,0.000000,0.000000,0.000000,0.000000,162:39,102,78.426000,0.000000,78.426000,0.000000,Australia,AUD

field 12 is changed (red). I am not sure why yours don't.

ok, let me to run it again...
also, you lookup statement..a bit difficult for me..because of my table (called currency_table.txt) which contain over 200 value, if I add the looup into the script..it may take a lot of time....any other possible solution for this?

happyv · #6 (**permalink**) 03-23-2007

$12 ~ /[0-9]/ { $12 = $12":00" }

the above statement is not work..if I split it to run and try the result

ghostdog74 · #7 (**permalink**) 03-23-2007

Quote:

Originally Posted by happyv

ok, let me to run it again...
also, you lookup statement..a bit difficult for me..because of my table (called currency_table.txt) which contain over 200 value, if I add the looup into the script..it may take a lot of time....any other possible solution for this?

oh ok...then one way is to read in the currency table from awk and store those values into array. an eg

Code:

awk  'BEGIN { FS=" " ;while ( getline < "currency_table_file" ) lookup[$1] = $2 
                               #other statements...
                    }
           ......#other statement.

then array lookup will contain the lookup table.pls try it out for yourself.

More UNIX and Linux Forum Topics You Might Find Helpful
Thread	Thread Starter	Forum	Replies	Last Post
how to read record by record from a file in unix	raoscb	UNIX for Dummies Questions & Answers	1	05-16-2008 07:30 AM
splitting a record and adding a record to a file	rsolap	Shell Programming and Scripting	1	08-13-2007 02:58 PM
how to replace field for each record	happyv	Shell Programming and Scripting	12	06-26-2007 08:56 AM
mapping record information	happyv	Shell Programming and Scripting	2	03-26-2007 02:36 AM
seaching field and getting complete record	mahabunta	UNIX for Dummies Questions & Answers	4	08-28-2006 05:52 PM

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode	Rate This Thread:


	Powered by