Unix/Linux Go Back    


Shell Programming and Scripting BSD, Linux, and UNIX shell scripting — Post awk, bash, csh, ksh, perl, php, python, sed, sh, shell scripts, and other shell scripting languages questions here.

awk statement help

Shell Programming and Scripting


Tags
awk, solved

Reply    
 
Thread Tools Search this Thread Display Modes
    #1  
Old Unix and Linux 1 Week Ago   -   Original Discussion by brettcasper
brettcasper brettcasper is offline
Registered User
 
Join Date: Jul 2011
Last Activity: 15 November 2017, 1:03 AM EST
Posts: 2
Thanks: 2
Thanked 0 Times in 0 Posts
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:
awk statement help Please use CODE tags when displaying sample input, output, and code segments.

Last edited by Don Cragun; 1 Week Ago at 07:19 PM.. Reason: Add CODE tags.
Sponsored Links
    #2  
Old Unix and Linux 1 Week Ago   -   Original Discussion by brettcasper
Yoda's Unix or Linux Image
Yoda Yoda is offline Forum Advisor  
Jedi Master
 
Join Date: Jan 2012
Last Activity: 23 November 2017, 5:45 PM EST
Location: Galactic Empire
Posts: 3,603
Thanks: 253
Thanked 1,316 Times in 1,233 Posts
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

The Following User Says Thank You to Yoda For This Useful Post:
brettcasper (1 Week Ago)
Sponsored Links
    #3  
Old Unix and Linux 1 Week Ago   -   Original Discussion by brettcasper
Don Cragun's Unix or Linux Image
Don Cragun Don Cragun is offline Forum Staff  
Administrator
 
Join Date: Jul 2012
Last Activity: 23 November 2017, 6:19 PM EST
Location: San Jose, CA, USA
Posts: 10,680
Thanks: 575
Thanked 3,738 Times in 3,190 Posts
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.
The Following User Says Thank You to Don Cragun For This Useful Post:
brettcasper (1 Week Ago)
    #4  
Old Unix and Linux 1 Week Ago   -   Original Discussion by brettcasper
brettcasper brettcasper is offline
Registered User
 
Join Date: Jul 2011
Last Activity: 15 November 2017, 1:03 AM EST
Posts: 2
Thanks: 2
Thanked 0 Times in 0 Posts
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.
Sponsored Links
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Linux More UNIX and Linux Forum Topics You Might Find Helpful
Thread Thread Starter Forum Replies Last Post
Convert Update statement into Insert statement in UNIX using awk, sed.... dev123 Shell Programming and Scripting 0 03-22-2016 04:37 AM
if statement in awk UNovIX UNIX for Dummies Questions & Answers 1 08-04-2009 06:51 PM
How is use sselect statement o/p in insert statement. nkosaraju Shell Programming and Scripting 2 08-06-2008 10:26 PM
If statement - How to write a null statement april Shell Programming and Scripting 3 04-16-2008 02:14 PM
statement Ernst Shell Programming and Scripting 10 03-09-2007 04:21 PM



All times are GMT -4. The time now is 07:59 PM.