awk change one value in csv based on another value in the record


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting awk change one value in csv based on another value in the record
# 1  
Old 08-01-2011
awk change one value in csv based on another value in the record

I've found 2 great discussions on this forum that are tied to my question, but for some reason, neither solution works for me.

I have a CSV file with many records, up to 150+ at some times. Many records have a value of H in $1. For those records, I need to add today's current date in $20. I'm not concerned about the date yet, I'm just trying to populate $20 with some value, any value.

I've tried ...

Code:
awk 'BEGIN {FS = ","} {if ($1 == H) {$20 = 20110801}}' MA540CSV

- and all I get is the contents of the data file MA540CSV scroll across my Unix screen.

I've also tried ...

Code:
awk 'BEGIN {OFS=","} {if ($1=="H") sub($20,"20110801"); print }' MA540CSV

Result, nothing. My command returns me to the command line, with no changes.

Any help would be greatly appreciated. If you need me to show a few records here, please let me know.

Last edited by radoulov; 08-01-2011 at 05:40 PM.. Reason: Code tags!
# 2  
Old 08-01-2011
In order to give a sensible answer we need to see a sample of your input file and an example of the expected output.
This User Gave Thanks to radoulov For This Post:
# 3  
Old 08-01-2011
Hi,

I will make a try (without testing), althought should be much better with sample input and output as Radoulov pointed out.
Code:
$ awk 'BEGIN {FS = OFS = ","} {if ($1 == "H") {$4 = 20110801}; print}' MA540CSV

Regards,
Birei
This User Gave Thanks to birei For This Post:
# 4  
Old 08-01-2011
No problem folks ... thanks for helping out ...

Sample Input

Code:
H,0004,3727,OWENS810,1079659,,,,,IC,4014527,,,,,,,,20110630,,P4,,7.40,,,,,,,,,,,,,,,EDI,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
D,0004,3727,OWENS810,1079659,,1,,,IC,4014527,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,001,17353,6504402300,2.0000,BX,3.70000,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,WIPE REM ADH UNI SOLVE,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
H,0013,3727,OWENS810,1079643,,,,,IC,13016351,,,,,,,,20110630,,P13,,269.36,,,,,,,,,,,,,,,EDI,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
D,0013,3727,OWENS810,1079643,,1,,,IC,13016351,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,001,116296,191689739101,2.0000,CA,117.68000,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,PACK EXTREMITY,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
D,0013,3727,OWENS810,1079643,,2,,,IC,13016351,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,002,5,4509001015,1.0000,BX,34.00000,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,DRAPE SURGICAL STERI U,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
H,0014,3727,OWENS810,1079489,,,,,,14057439,,,,,,,,20110630,,P14,,59.84,,,,,,,,,,,,,,,EDI,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
D,0014,3727,OWENS810,1079489,,1,,,,14057439,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,001,10595,4352MDS9380,4.0000,EA,9.86000,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,MDS9380 CUFF B/P STANDARD,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
D,0014,3727,OWENS810,1079489,,2,,,,14057439,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,002,78534,4352MDS9388,1.0000,EA,10.80000,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,CUFF ANEROID OBESE OBESE LATEX,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
D,0014,3727,OWENS810,1079489,,3,,,,14057439,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,003,65819,4352MDS9387,1.0000,EA,9.60000,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,MDS9387 CUFF B/P CHILDS,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,

Sample Output

Code:
H,0004,3727,OWENS810,1079659,,,,,IC,4014527,,,,,,,,20110630,20110801,P4,,7.40,,,,,,,,,,,,,,,EDI,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
D,0004,3727,OWENS810,1079659,,1,,,IC,4014527,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,001,17353,6504402300,2.0000,BX,3.70000,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,WIPE REM ADH UNI SOLVE,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
H,0013,3727,OWENS810,1079643,,,,,IC,13016351,,,,,,,,20110630,20110801,P13,,269.36,,,,,,,,,,,,,,,EDI,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
D,0013,3727,OWENS810,1079643,,1,,,IC,13016351,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,001,116296,191689739101,2.0000,CA,117.68000,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,PACK EXTREMITY,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
D,0013,3727,OWENS810,1079643,,2,,,IC,13016351,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,002,5,4509001015,1.0000,BX,34.00000,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,DRAPE SURGICAL STERI U,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
H,0014,3727,OWENS810,1079489,,,,,,14057439,,,,,,,,20110630,20110801,P14,,59.84,,,,,,,,,,,,,,,EDI,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
D,0014,3727,OWENS810,1079489,,1,,,,14057439,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,001,10595,4352MDS9380,4.0000,EA,9.86000,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,MDS9380 CUFF B/P STANDARD,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
D,0014,3727,OWENS810,1079489,,2,,,,14057439,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,002,78534,4352MDS9388,1.0000,EA,10.80000,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,CUFF ANEROID OBESE OBESE LATEX,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
D,0014,3727,OWENS810,1079489,,3,,,,14057439,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,003,65819,4352MDS9387,1.0000,EA,9.60000,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,MDS9387 CUFF B/P CHILDS,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,

---------- Post updated at 05:05 PM ---------- Previous update was at 05:02 PM ----------

Birei! Hooray! Birei! Hooray! My syntax was not quite as clean as yours. Yours worked wonderfully. Now I'll work on inserting the current date into the second value. Thanks again for all your help!

Last edited by radoulov; 08-02-2011 at 03:58 AM.. Reason: Code tags.
# 5  
Old 08-02-2011
Code:
awk -F, '/^H/ { $20 = dt }1' dt="$( date '+%Y%m%d' )" OFS=, infile

This User Gave Thanks to radoulov For This Post:
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 change contents of field based on condition in same file

In the awk below I am trying to copy the entire contents of $6 there may be multiple values seperated by a ;, to $8, if $8 is . (lines 1 and 3 are examples). If that condition $8 is not . (line2 is an example) then that line is skipped and printed as is. The awk does execute but prints the output... (3 Replies)
Discussion started by: cmccabe
3 Replies

2. Shell Programming and Scripting

awk to select lines with maximum value of each record based on column value

Hello, I want to get the maximum value of each record separated by empty line based on the 3rd column of each row within each record? Input: A1 chr5D 634 7 82 707 A2 chr5D 637 6 82 713 A3 chr5D 637 5 82 713 A4 chr5D 626 1 82 704... (4 Replies)
Discussion started by: yifangt
4 Replies

3. Shell Programming and Scripting

Keeping record of file 2 based on a reference file 1 in awk

I have 2 input files (tab separated): file1: make_A 1990 foo bar make_B 2010 this that make_C 2004 these those file2: make_X 1970 1995 ref_1:43 ref_2:65 make_A 1970 1995 ref_1:4 ref_2:21 ref_3:18 make_A 1980 2002 ref_1:7 ref_2:7 ref_3:0 ... (2 Replies)
Discussion started by: beca123456
2 Replies

4. Shell Programming and Scripting

Need to change date format in a csv file using awk

Example: Input csv file 00245DLS,Sitel Ocala,12/31/2014,18:45,1.00,7.00,0.00,0.00 00245DLS,Sitel Ocala,12/31/2014,19:00,-1.00,-1.00,-1.00,-1.00 00245HB,Charlotte,01/01/2015,00:00,-1.00,-1.00,-1.00,0.00 Output csv file 00245DLS,Sitel Ocala,2014/12/31,18:45,1.00,7.00,0.00,0.00 00245DLS,Sitel... (8 Replies)
Discussion started by: adit
8 Replies

5. Shell Programming and Scripting

Datestamp format 2nd change in csv file (awk or sed)

I have a csv file formatted like this: 2014-08-21 18:06:26,A,B,12345,123,C,1232,26/08/14 18:07and I'm trying to change it to MM/DD/YYYY HH:MM for both occurances. I have got this: awk -F, 'NR <=1 {print;next}{"date +%d/%m/%Y\" \"%H:%m -d\""$1 "\""| getline dte;$1=dte}1' OFS="," test.csvThis... (6 Replies)
Discussion started by: say170
6 Replies

6. Shell Programming and Scripting

How to compare current record,with next and previous record in awk without using array?

Hi! all can any one tell me how to compare current record of column with next and previous record in awk without using array my case is like this input.txt 0 32 1 26 2 27 3 34 4 26 5 25 6 24 9 23 0 32 1 28 2 15 3 26 4 24 (7 Replies)
Discussion started by: Dona Clara
7 Replies

7. Shell Programming and Scripting

awk work with time change in csv file

Hi, i have csv input file looks like below 3rd field is date and time field i want to change it with user supplied date and time says year=2011 month=09 day=05 hour=11 count=2 when count is say 10 then first ten records should pick and it should increment the... (2 Replies)
Discussion started by: raghavendra.nsn
2 Replies

8. Shell Programming and Scripting

Record count of a csv file

Hello Gurus, We have a requirement to count the valid number of records in a comma delimited file with double quotes. The catch here is..few records have a new line carriage within the double quotes,,say for ex:we have a file called accounts the record count is 4827..but the actual valid count... (5 Replies)
Discussion started by: ajaykk
5 Replies

9. Shell Programming and Scripting

search file, change existing value based on input (awk help)

I have a file (status.file) of the form: valueA 3450 valueB -20 valueC -340 valueD 48 I am tailing a data.file, and need to search and modify a value in status.file...the tail is: tail -f data.file | awk '{ print $3, ($NF - $(NF-1)) }' which will produce lines that look like this: ... (3 Replies)
Discussion started by: nortonloaf
3 Replies

10. UNIX for Dummies Questions & Answers

How to delete a record from a csv file

Hi Guys I have downloaded a table from oracle database in .csv format. it has many fields as Title, First Name, Last Name etc. I have to download distinct titles from database and now i have to check all those titles from data of First Name one by one. and then i have to delete matched record.... (1 Reply)
Discussion started by: Rajeev Agrawal
1 Replies
Login or Register to Ask a Question