Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

awk statement help

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting awk statement help
# 1  
Old 11-13-2017
awk statement help
There has to be a way to do this with awk or maybe I'm just focusing on the wrong tool and making this harder than it needs to be.

I'm trying to do a file field lookup/join at a very large scale but the output changes has to change dramatically. I have an input file to do a field lookup from and essentially do a field join out output with a one to many relationship of values that will be found. For each result, I need to write out a block of text based on results found.

If results are found, data is pulled together. If there's multiple results found in the data file, then it needs to organize the somewhat like the below. If no results are found, then it just uses field values from the original file. Once that is done to determine fields, then the output has to be output way different on separate lines.

Example:
1) File1.txt (file to process)
Code:
Site~Location~date~person
1~15~2017-01-01~me
2~28~2016-05-01~owner
3~68~2015-01-28~supervisor
4~69~2012-10-15~extra

2) File2.txt (file with data to pull in...join field2 from file1 to field1 from file2)
Code:
Location~Overriding Sites
15~12
15~13
15~14
15~10
28~99
68~100

3) Output text to write out (site value from file1 is dropped if results found and overriding site values used where a location can have multiple side ids):

Code:
Begin Record
Location: 15
Site Id: 12
Site Id: 13
Site Id: 14
Site Id: 10
Date: 2017-01-01
Contact: me
End Record

Begin Record
Location: 28
Site Id: 99
Date: 2016-05-01
Contact: owner
End Record

Begin Record
Location: 68
Site Id: 100
Date: 2015-01-28
Contact: supervisor
End Record

Begin Record
Location: 69
Site Id: 4
Date: 2012-10-15
Contact: extra
End Record

I've looked at this at a few different ways. And I'm getting myself turned around. Can you help?
Moderator's Comments:
Mod Comment Please use CODE tags when displaying sample input, output, and code segments.
Last edited by Don Cragun; 11-13-2017 at 07:19 PM.. Reason: Add CODE tags.
# 2  
Old 11-13-2017
Here is an awk approach:-
Code:
awk -F'~' '
        NR == FNR {
                if ( FNR > 1 )
                        A_F1[$2] = $3 FS $4
                next
        }
        FNR > 1 {
                A_F2[$1] = ( A_F2[$1] ? A_F2[$1] FS $2 : $2 )
        }
        END {
                for ( k in A_F1 )
                {
                        n = split ( A_F1[k], T1 )
                        print "Begin Record"
                        print "Location: " k

                        if ( k in A_F2 )
                        {
                                m = split ( A_F2[k], T2 )
                                for ( i = 1; i <= m; i++ )
                                        print "Site Id: " T2[i]
                        }
                        else
                                print "Site Id: NULL"

                        print "Date: " T1[1]
                        print "Contact: " T1[2]
                        printf "End Record\n\n"
                }
        }
' file1.txt file2.txt

This User Gave Thanks to Yoda For This Post:
brettcasper
# 3  
Old 11-13-2017
Hi brettcasper,
Welcome to the UNIX & Linux Forums. When starting a thread here it always helps if you tell us what operating system and shell you're using so we know what capabilities your system has.

In addition to what Yoda suggested, you might also try the following. By reversing the order in which the files are processed, it can process records from File1.txt one record at a time instead of having to store the entire contents of both files in memory.

Code:
awk -F'~' '
FNR == 1 {
	next
}
NR == FNR {
	site[$1] = site[$1] "Site Id: " $2 "\n"
	next
}
{	printf("Begin Record\nLocation: %s\n%sDate: %s\nContact: %s\nEnd Record\n\n",
	    $2, ($2 in site) ? site[$2] : "Site Id: " $1 "\n", $3, $4)
}' File2.txt File1.txt

If you're running this on a Solaris/SunOS system, change awk to /usr/xpg4/bin/awk or nawk.
This User Gave Thanks to Don Cragun For This Post:
brettcasper
# 4  
Old 11-15-2017
You guys rock. I was doing this within a Cygwin bash shell and within an AIX OS bash shell. I was close to what Yoda was doing but can see now with his example where my code was starting to go wrong. Due to the suggestion of Don, I was focusing on testing that and it worked like a charm. Thanks for the help.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Convert Update statement into Insert statement in UNIX using awk, sed....

Hi folks, I have a scenario to convert the update statements into insert statements using shell script (awk, sed...) or in database using regex. I have a bunch of update statements with all columns in a file which I need to convert into insert statements. UPDATE TABLE_A SET COL1=1 WHERE... (0 Replies)
Discussion started by: dev123
0 Replies

2. Shell Programming and Scripting

awk if then else statement

I am using awk as part of and if then else statement. I am trying to have the user enter a gene name and then a variant or variants and have a specific transcript assigned to the variants depending on the gene. Below is my code but the if then else statement is wrong. Basically, the gene name... (2 Replies)
Discussion started by: cmccabe
2 Replies

3. Shell Programming and Scripting

If statement in awk

I run my script "switch.sh" repeatedly (within 30 seconds). Each time script is triggered, script itself should kill all previous process. Here is my code: for pid in $(ps -fe | grep 'switch.sh' | grep -v grep | awk '{if ($2<$$) print $2}'); do sudo kill -9 $pid done sleep 30 ... (6 Replies)
Discussion started by: armatron
6 Replies

4. Shell Programming and Scripting

Help with awk statement

Hi I have a file with test test2 1000000657373 test1 test3 1000003849450 test2 test4 test5 100000837474 I cat the file and pipe it to an awk statement like so cat /tmp/file |awk '{if ($3 ~ "^*$" && $3 > 1024000000) print "/vol/"$1"/"$2;else if ($4 ~ "^*$" && $4 > 1024000000) print... (15 Replies)
Discussion started by: bombcan
15 Replies

5. Shell Programming and Scripting

awk if statement

hi, I have a l-column file of more than 10,000 lines with interspersed negative values. What I want to do is add a fixed number (360) everytime a negative value is encountered while leaving the positive ones as is. I need something that will read every line of the file and do the calculation... (3 Replies)
Discussion started by: ida1215
3 Replies

6. Shell Programming and Scripting

Help Regarding AWk and IF THEN ELSE Statement

Hi, I have a data file which contains record count. So doing wc -l rightfit_balancing_count.dat | awk '{print $1}'] gives me the record count stored in the file. Now, i want to send a mail from UNIX, if the record count is equal to 0,otherwise it should do nothing. Any help... (2 Replies)
Discussion started by: Shell_Learner
2 Replies

7. Shell Programming and Scripting

Awk 'if' statement help

Hello all, I'm very new to this (<5hrs!) please bear with me. My file looks like this 386259.448541 417069.155 154935.157 186.206 162 1 1 8 386259.448551 417068.53 154935.04 186.144 156 1 1 8 386259.448561 417067.911 154934.926 186.175 164 1 1 8 386259.450337 417086.643 154946.483 894.671... (4 Replies)
Discussion started by: rebeccab37
4 Replies

8. Shell Programming and Scripting

awk, if statement

Having a little trouble with awk and an if statement. I have a test setup which I am trying to only print the records which start with the month 03. Everything I tried, prints everything, even the 02 month 03/23/2010 12:47:51 ga2828 SUBMITTED FROM URL: test123.cgi show port count ... (2 Replies)
Discussion started by: numele
2 Replies

9. Shell Programming and Scripting

awk inside another awk statement

hi all, i have two files 1) a.txt one two three 2) abc "one" = 10 pqr "three" = 20 345 "two" = 0 this is what i want in third file (3 Replies)
Discussion started by: shishirkotkar
3 Replies

10. UNIX for Dummies Questions & Answers

if statement in awk

Hi Friends How do I do two things from one if statement inside awk? I want to run a script and create a new file from the same condition. awk '{ if ($2 == ""){print " "|"cd /local/test; ./script.ksh"}{cat > ran_true.txt}}' $IN_FILE Bolded are the two things I want to be done. Thanks (1 Reply)
Discussion started by: UNovIX
1 Replies
Login or Register to Ask a Question