Languages and Scripting
Showing results for 
Search instead for 
Do you mean 

combine 2 records into 1 record on same key field with ksh or awk?

SOLVED
Go to Solution
Trusted Contributor Trusted Contributor

combine 2 records into 1 record on same key field with ksh or awk?

the following file is from EMC Storage Scope. It's a "csv" file (comma-seperated source file):

Zone Set Name,Zone Name,Fabric,Member Zoning Type,Zone Member,Member System Name,Member System Type

ETC_FabricA_00_12,ELVIS_SL2,ETC-SW-02,End Port WWN,5006048accc85581,000187900246,Storage Array
ETC_FabricA_00_12,ELVIS_SL2,ETC-SW-02,End Port WWN,50060b00001358b4,,

ETC_FabricA_00_12,ETC-A01_SL4_SW00_P48_0246_2CA,ETC-SW-02,End Port WWN,5006048accc85581,000187900246,Storage Array
ETC_FabricA_00_12,ETC-A01_SL4_SW00_P48_0246_2CA,ETC-SW-02,End Port WWN,50060b0000133e94,etc-a01,Host

ETC_FabricA_00_12,ETC-A02_SL4_SW00_P52_0246_2CA,ETC-SW-02,End Port WWN,5006048accc85581,000187900246,Storage Array
ETC_FabricA_00_12,ETC-A02_SL4_SW00_P52_0246_2CA,ETC-SW-02,End Port WWN,50060b0000133e98,etc-a02,Host

In general (but not always) two lines make up a "zone". (sometimes 3 or 4, and sometimes 1..)

How can I manipulate this file so that I only have one line (record) for each zone - in ksh or awk (NOT perl)? Can I do a "join" on multiple fields? Can I use awk?
6 REPLIES
Highlighted
Honored Contributor Honored Contributor

Re: combine 2 records into 1 record on same key field with ksh or awk?

Try this

awk '$1 ~ /ETC_Fabric.*/ {getline SOME; printf("%s %s\n",$0,SOME)}' InputFile

May not be the most elegant way to do it, but hey it works :-)
Learn What to do ,How to do and more importantly When to do ?
Exalted Contributor Exalted Contributor

Re: combine 2 records into 1 record on same key field with ksh or awk?


fc=1
while read -r filedata
do
dataline=$filedata
if [ $fc -gt 1 ] then
data="${dataline}${filedata}"
fc=0
fi

(( fc = fc + 1 ))

done < input_file

The only thing I don't have solved is handling 3 or 4 line input.

This script will combine multiple records onto a single record.


Basic awk use.

datahold=$(awk print {'print $1 $2 $3'})

use as many $ varaibles as you need to get all the fields. You can also do it like this:
datahold1=$(awk print {'print $1'})
datahold2=$(awk print {'print $2'})
datahold3=$(awk print {'print $3'})

awk is great for stripping fields out of data.

comma delimited awk.

awk -F:'

SEP
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
Honored Contributor Honored Contributor

Re: combine 2 records into 1 record on same key field with ksh or awk?

Oops, sorry my awk can only handle 2 records per zone.
Learn What to do ,How to do and more importantly When to do ?
Trusted Contributor Trusted Contributor

Re: combine 2 records into 1 record on same key field with ksh or awk?

It looks like each "zone" is separated by a blank line.
If that is always the case, you could try something like this: (I assume you also want a comma between joined lines)

> tmpfile
new_line=""
file=
((numlines=`cat $file | wc -l`))
while (( $numlines > 0 )) ; do
tmpline=`tail -$numlines $file | head -1`
if [[ -n $tmpline ]] ; then
if [[ -n $new_line ]] ; then
new_line=""
echo ",\c" >> tmpfile
fi
echo "${tmpline}\c" >> tmpfile
else
new_line="1"
# Repeat the next line if you want a blank line between lines
echo >> tmpfile
fi
let numlines-=1
done


NOTE: 'tail' has a 20K buffer size limit.
What does this button do?
Honored Contributor Honored Contributor

Re: combine 2 records into 1 record on same key field with ksh or awk?

Hi,
try this:

#!/usr/bin/sh

PREVIOUS=""
IFS=,
while read Zone_Set_Name Zone_Name_Fabric Member_Zoning_Type Zone_Member Member_
System_Name Member_System_Type
do
if [ "$PREVIOUS" != "$Zone_Name_Fabric" ]
then
echo "$Zone_Set_Name $Zone_Name_Fabric $Member_Zoning_Type $Zone_Member
$Member_System_Name $Member_System_Type"
fi
PREVIOUS="$Zone_Name_Fabric"
done <$1

using your input file as $1....

regards,
John K.
it would be nice if you always got a second chance
Honored Contributor Honored Contributor

Re: combine 2 records into 1 record on same key field with ksh or awk?

for zone in `awk -F "," '!/^$/ { print $2 }' | uniq`; do grep $zone test2.log > $zone.log; done

will collect records based on zonename into zonename.log file. Then combine / paste the individual zones.

hth.
Easy to suggest when don't know about the problem!