[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Displaying combined fields from different input files to screen using awk

Posted on 2006-03-27
17
Medium Priority
?
246 Views
Last Modified: 2011-09-20
Hi all!

I'm having trouble combining field 2 values from various files into one output (print to screen) on separate columns . They have been read into awk using $* within a bash script. They were applied to the bash script as command-line input files where the number of files typed in the command-line was unlimited).  I'm trying to get the output to look as follows:

07956434547      195     180      104        
07995434267        35       99      232      
07896567321        97     154      276    
07543563298      166     100        65    
07958765421      340     180      353    
07896128965      268     401     106    
07957327570       257     128     192    
             
I'm okay with displaying the fields of column 1.The columns that start with the fields 195, 180, and 104 are the 2nd fields from different files I have read in.  However, I only managed to get the following output for field 2 when each file is combined:

195
35
97
166
340
268
257
180
99
154
100
180
401
128
104
232
276
65
353
106
192

An example of an input file is as follows:

07956434547       104        
07995434267         32      
07896567321      276    
07543563298        65    
07958765421      353    
07896128965       106    
07957327570       192    

I tried using separators such as FS and RS within the BEGIN and END block of my awk script but to no affect.This is the code came up with:

BEGIN { FS = "\n"; RS =""; NF ==2 } #Used as precondition for following code:

for (i=1; i<=NF; ++i){
split($i, bill, " ")

phone = bill[1] #Phone number array of current month
tbill = bill[2] #Phone bill array of current month
print tbill #Prints field 2 from each file
print phone #prints field 1 from each file
}


I can limit the fields being displayed by attaching the following code around the above code:

++prev_NR #Used prev!=FILENAME to set this variable to 0
if(NR>prev_NR){ #Can adjust this condition for desired output

}
 

In conclusion, I need assistance on trying to get field 2 from each separate file, to display as consecutive columns alongside the 1st field (as shown above).

Your help would be very much appreciated

Thanks
0
Comment
Question by:london_town
  • 6
  • 6
  • 5
17 Comments
 
LVL 27

Accepted Solution

by:
Nopius earned 1500 total points
ID: 16307610
Is 'awk' a requirement?

An example of an input file is as follows:

07956434547      104        
07995434267        32      
07896567321      276    
07543563298        65    
07958765421      353    
07896128965      106    
07957327570      192    

I see the better way
combine.sh
--[cut]--
#!/bin/sh
j=/tmp/combined.txt
touch $j
for i in $*
do
  sort<$i >$i.tmp
  join -a2 $j $i.tmp >$j.new
  mv $j.new $j
  rm $i.tmp
done
cat /tmp/combined.txt
--[cut]--
0
 

Author Comment

by:london_town
ID: 16308300
awk is the language being used throughout so would prefer this scripting language.  As I am passing arguments into awk from bash I tried tackling the problem by extending this method but was unsuccessful
0
 
LVL 27

Expert Comment

by:Nopius
ID: 16308356
shell is also available on all systems and every used in script command.

just run
sh combine.sh file1 file2 file3 ...

arguments are file names which you want to combine
exactly how files are passed to awk

result will be in /tmp/combined.txt don't forget to remove it after command is executed, because next calls will 'append' data to this file.

why I've suggested this solution, because 'join' command doing the same thing you are trying to invent again.
man join

Yes, solution in awk also exists, but it is not very nice and it's not a unix way of doing such things.
Try this script before
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

Author Comment

by:london_town
ID: 16313898
I will gather my thoughts tomorrow.  Thanks for your help Nopius
0
 
LVL 51

Expert Comment

by:ahoffmann
ID: 16314685
do you mean something like:
  paste file1 file2 file3

if not, please post content of file1 file2 ...
0
 
LVL 27

Expert Comment

by:Nopius
ID: 16316058
london_town I've misunderstand you. I thought you have number of files like:

07956434547      104

and wand to get one file like:
07956434547  104    305    607

If you like to 'join' by line number, not by value of first field, then ahoffman right, use paste.
0
 

Author Comment

by:london_town
ID: 16321505
I'm just about to continue working on the program.  As I stated in my original question, the output should display to screen the following only:

07956434547     195     180      104        
07995434267       35       99      232      
07896567321       97     154      276    
07543563298     166     100        65    
07958765421     340     180      353    
07896128965     268     401     106    
07957327570      257     128     192    

I'm going with the join method which will be used within bash.  The output will be printed within awk
0
 

Author Comment

by:london_town
ID: 16321687
Sorry, yea my files have the layout of....

07956434547      104

per line. 7 lines in total
0
 
LVL 51

Expert Comment

by:ahoffmann
ID: 16322666
do you mean that file1 contains:

07956434547      104

and file2 contains:

07956434547      180

and file3 contains:

07956434547      195
0
 

Author Comment

by:london_town
ID: 16329655
Amount of files inputted can be unlimited but i'm working with at the moment:

FILE 1:

07956434547      195
07995434267      35
07896567321      97
07543563298      166
07958765421      340
07896128965      268
07957327570      257

FILE 2:

07956434547      180
07995434267      99
07896567321      154
07543563298      100
07958765421      180
07896128965      401
07957327570      128

FILE 3:

07956434547      104
07995434267      232
07896567321      276
07543563298      65
07958765421      353
07896128965      106
07957327570      192
0
 
LVL 51

Expert Comment

by:ahoffmann
ID: 16329798
paste file1 file2 file3 | awk '{$3="";$5="";print}'

NOTE that the files need to be sorted first
0
 
LVL 27

Expert Comment

by:Nopius
ID: 16329996
I've found another method (reliable when nuber of lines in each file is not equal):

combine.awk:
--[cut]--
NR==1 {
 idval=idval FS $2
 next
}
$1==idx {
 idval=idval FS $2
 next
}
{
  print idx FS idval
  idx=$1
  idval=$2
}
--[cut]--

Now how it works:

cat file1.txt file2.txt file3.txt | sort | awk -f combine.awk
0
 
LVL 27

Expert Comment

by:Nopius
ID: 16330040
Oops, bug with non-printing of last line and empty lines...
This should work better:

NR==1 {
 idval=idval FS $2
 next
}
$1==idx {
 idval=idval FS $2
 next
}
{
 if (idx~/[0-9]/) print idx FS idval
 idx=$1
 idval=$2
}
END{
  print idx FS idval
}
0
 
LVL 51

Expert Comment

by:ahoffmann
ID: 16330571
sort file1 file2 file3 | awk '{a[$1]=sprintf("%4s %4s",a[$1],$2)}END{for(n in a){print n" "a[n]}}'
# no useless use of cat, one pipe, no extra file ;-)
0
 

Author Comment

by:london_town
ID: 16337857
Thanks everyone for your help on this problem.
As you can see, I have rewarded Nopius the points. This code was used as the foundation for my elaborated solution
0
 
LVL 27

Expert Comment

by:Nopius
ID: 16337896
london_town, thank you. Really when you have such problems as above, it's time to move to SQL and database ;)

ahoffmann: ok, many advantages of your code, but more memory consumption in arrays in awk ;)
0
 
LVL 51

Expert Comment

by:ahoffmann
ID: 16339907
no worries, the EE suggestion is to grade the most helpful answer, which might not be the best one ;-)
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The purpose of this article is to fix the unknown display problem in Linux Mint operating system. After installing the OS if you see Display monitor is not recognized then we can install "MESA" utilities to fix this problem or we can install additio…
The purpose of this article is to demonstrate how we can upgrade Python from version 2.7.6 to Python 2.7.10 on the Linux Mint operating system. I am using an Oracle Virtual Box where I have installed Linux Mint operating system version 17.2. Once yo…
Integration Management Part 2
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question