Merge two files line by line and column by column

Shell Programming and Scripting
1

Hi All,

I have two files having oracle query result. I want to merge to files line by line and also with column

File1

23577|SYNC TYPE
23578|Order Number|ConnectionState
23585|Service State|Service Name

File2

23577|AR Alarm Sync
23578|A5499|9
23585|7|test_nov7

Result should be

23577|SYNC TYPE -> AR Alarm Sync
23578|Order Number -> A5499|ConnectionState -> 9
23585|Service State -> 7|Service Name -> test_nov7
2 
join -t"|" file1 file2
3

This is adding columns of file B after columns of fileA.
I need one column of fileA and then fileB

4

Then change the order of params:-

join -t"|" file2 file1
5

I want alternate columns of fileA and fileB like
col1of A -> col1ofB |col2ofA -> col2ofB

6

try:

awk -F"|" 'NR==FNR{a[$1]=$1; b[$1,2]=$2; c[$1,3]=$3; next;}{print a[$1]"|"b[$1,2]" -> "$2 (c[$1,3]?("|"c[$1,3]" -> "$3): "")}' File1 File2
7

Try:

awk 'NR==FNR{A[$1]=$2; B[$1]=$3; next} A[$1]{$2=$2 " -> " A[$1]} B[$1]{$3=$3 " -> " B[$1]}1' FS=\| OFS=\| file2 file1
8

Both solutions are working , but no of columns are varies for each record, so can you generalize this solution for n no of column

9

try:

awk -F"|" 'NR==FNR{for(i=1;i<=NF;i++){a[$1,i]=$i;}; next;}{printf $1"|";for(i=2;i<=NF;i++){printf a[$1,i]" -> "$i (a[$1,(i+1)]?"|":"\n")}}' File1 File2
1 Like
10 
awk -F "|" 'BEGIN { OFS = "|" }
FNR == NR {
        a[$1] = NF
        for(i = 2; i <= NF; i++) a[$1,i] = $i
        next
}
{       for(i = 2; i <= NF; i++) $i = a[$1,i] " -> " $i
        print
}' File1 File2
1 Like
11

Hi Now requirement has changed. Now I dont need to merge two columns of different files but I have merged output in single file. still some word processing remaining

file A:

1|fkgk
2|sfkgn
3|kfhd
4|hfj
5|fghfd
6|dsfjs
7|fbg.
8|dfds
9|efh

fileB

2|colour->red
3|colour->green
3|height->3
5|colour->
5|height->6
5|weight->4
8|colour->blue
8|weight->

I want to show output as follows

1|fkgk|-
2|sfkgn|colour->red
3|kfhd|colour->green|height->3
4|hfj|-
5|fghfd|colour->|height->6|weight->4
6|dsfjs|-
7|fbg|-
8|dfds|colour->blue|weight->
9|efh|-

---------- Post updated at 01:21 PM ---------- Previous update was at 01:18 PM ----------

I have tried following but this is not working :frowning:

#!/bin/sh
rm debug.log
rm $3
fileA=$1
fileB=$2
fileC=$3
var=

cat $fileA|while read lineA
do
#get handle of fileA
Handle=`echo $lineA|awk -F"|" '{print $1}'`
echo "debug fileA handle:" $Handle >>debug.log
lineBhandle=`echo $
lineB=`grep -w $Handle $fileB`
echo "debug lineB :"$lineB>>debug.log
if [ -z "$lineB" ]
then
    echo "$lineA    -" >> $fileC
    echo "inside if ">>debug.log
else
    cat $fileB|while read lineC
    do
        file2handle=`echo $lineC|awk -F"|" '{print $1}'`
        echo "debug fileB handle:"$file2handle>>debug.log
        if [ $Handle -eq $file2handle ]
        then
            param=`echo $lineC|awk -F"|" '{print $2}'`
            var="$var $param"
        fi
    done
    echo "debug param:"$var>>debug.log
    echo "$lineA    $var">>$fileC
fi
done
12

Try

awk -F "|" 'NR==FNR{X[$1]=X[$1]?X[$1]"|"$2:$2;next}{if(X[$1]){print $0"|"X[$1]}else{print $0"-"}}' fileB fileA
1 Like
13

It is working, could you please explain me in layman terms how this code woks

14 
awk -F "|" 'NR==FNR{X[$1]=X[$1]?X[$1]"|"$2:$2;next}  # Read fileB - if X[$1] present then append $2 to the X[$1]. if not then assin $2 to X[$1]

{if(X[$1]){print $0"|"X[$1]}else{print $0"-"}}' fileB fileA   # Read fileA - if $1 presnet it fileA then print $0 and X[$1]. if not the print $0"-"

I hope this helps you:)

pamu

15

Thanks , I understood your logic but not code.

I mean in first sentence we are using fileB only
and in second sentence fileB and fileA both.
How code decides it

16

First we read fileB and stores it into array X[] .
Then we read second fileA and print array X[] with fileA.

1 Like
17

Let's look at the code again in a slightly different format:

1 awk -F "|" '
2 NR==FNR{
3       X[$1]=X[$1]?X[$1]"|"$2:$2
4       next
5 }     
6 {     if(X[$1]) print $0"|"X[$1]
7       else print $0"|-"
8 }' fileB fileA

The test on line 2 NR==FNR selects lines where the input line number (NR) and the input line number within the current file (FNR) are the same. This selects lines that are in the 1st file only.

The "next" command on line 4 tells awk to skip the remainder of the script for the current line and start processing the next input line. So, the commands on lines 6 and 7 are skipped when processing lines from the first file and are only used when processing ilnes from the second file.

Note that to meet the output requjirements you originally stated, the additional
"|" in red needs to be added to your script.

2 Likes
18

Why don't you modify your ORACLE select ion in the first place to accomodate your needs?

19

thanks