awk - Multiple files - 1 file with multi-line data

05-11-2016

Registered User

30, 1

Join Date: Apr 2015

Last Activity: 1 December 2019, 2:44 PM EST

Location: India

Posts: 30

Thanks Given: 25

Thanked 1 Time in 1 Post

awk - Multiple files - 1 file with multi-line data

Greetings experts,
Have 2 input files, of which 1 file has 1 record per line; in 2nd file, multiple lines constitute 1 record; Hence declared the RS=";"
Now in the first file which ends with ";" at each line of the line; But \nis also being considered as part of the data due to which
I am facing some issues; How to avoid this.

File1: FS=@ RS=; (forced to use RS=; as for the file2 it spans across multiple lines)

Code:

Source_table_name  Source_col_name   Target_table_name    Target_col_name
Src_1@src_col11@Tgt_1@tgt_col11;
Src_2@src_col21@Tgt_2@tgt_col21;
......

File2:

Code:

Src_tbl       Tgt_tbl     join condition
Src_1@Tgt_1 @FROM ( SELECT SRC_1.* FROM SRC_1 INNER JOIN 
SRC_TEMP_1 ON  SRC_1.COL1=SRC_TEMP_1.COL1) SRC JOIN (SELECT 
TGT_1.* FROM TGT_1   INNER JOIN TGT_TEMP_1 ON 
TGT_1.COL1=TGT_TEMP_1.COL1) TGT ON   SRC.COL1=TGT.COL1;
Src_2@Tgt_2@FROM( SELECT SRC_2.*  FROM SRC_2 LEFT OUTER JOIN TGT_2 ON SRC_2.COL1=TGT_2.COL1;

awk_code:

Code:

awk -F[@] 'BEGIN { RS=";"  OFS=" "}
FNR==NR{src_tgt_array[$1 OFS $3]="something";
next;
}
{
join_array[$1 OFS $2]=$3
}
end {
for (i in src_tgt_array)
{
print "src and tgt tables " i " join condition is " join_array[i]
print "----------------"
} 
}' < file1.txt file2.txt > awk_output.txt

Output:

Code:

src and tgt tables Src_1 Tgt_1 join condition is FROM ( SELECT SRC_1.*
 FROM SRC_1 INNER JOIN SRC_TEMP_1 ON  SRC_1.COL1=SRC_TEMP_1.COL1) SRC
 JOIN (SELECT TGT_1.* FROM TGT_1   INNER JOIN TGT_TEMP_1 ON TGT_1.COL1=TGT_TEMP_1.COL1) TGT 
ON   SRC.COL1=TGT.COL1
----------------
src and tgt tables 
Src_2 Tgt_2 join condition is 
----------------

As you can see that after this line src and tgt tables the data jumped into the other line from which I assume
that there is embedded \n which is not present in join_array I guess (present without embedded newline char)

Expected Output: -- For readability I have split the data into multiple rows

Code:

src and tgt tables Src_1 Tgt_1 join condition is FROM ( SELECT SRC_1.* FROM SRC_1 INNER JOIN SRC_TEMP_1 ON  SRC_1.COL1=SRC_TEMP_1.COL1) SRC 
JOIN (SELECT TGT_1.* FROM TGT_1   INNER JOIN TGT_TEMP_1 ON TGT_1.COL1=TGT_TEMP_1.COL1) TGT 
ON   SRC.COL1=TGT.COL1
----------------

src and tgt tables Src_2 Tgt_2 join condition is FROM( SELECT SRC_2.*  FROM SRC_2 
LEFT OUTER JOIN TGT_2 ON SRC_2.COL1=TGT_2.COL1
----------------

As RS=";" the data in the array for the first record in file1 is as expected; But for the next records, there is \n embedded
into the array before next records first column is read as you can see from the above
hence not printing the data relative to this in join_array;

How to overcome this please..

Edit:
Please excuse any syntax issues as I am not able to copy/paste;

chill3chee

View Public Profile for chill3chee

Find all posts by chill3chee

05-11-2016

Registered User

12,315, 4,560

Join Date: Jul 2012

Last Activity: 22 November 2019, 4:29 PM EST

Location: San Jose, CA, USA

Posts: 12,315

Thanks Given: 952

Thanked 4,560 Times in 3,818 Posts

I'm sorry, but we can't ignore syntax issues. If you can't accurately show us what your code really looks like, we can waste our time and yours pointing out reasons why you code won't work when it has nothing to do with your problem. Our crystal balls don't work well enough to read code that you haven't shown us.

I haven't tried to work out the logic of what your awk code is doing, but there are two obvious problems with what you have shown us:
First, just like the BEGIN keyword is all uppercase, the end in your code needs to be END.

And, second, the awk utility reads from standard input when no file operands are specified on the command line and when - is specified as a file operand. So try changing the last line of your script from:

Code:

}' < file1.txt file2.txt > awk_output.txt

to:

Code:

}' file1.txt file2.txt > awk_output.txt

so awk will see two file operands instead of being given your first file on standard input and you second file as the only file operand.

If these changes don't fix your problem, please show us what happens after making these changes and we'll help you debug it further. And, please find a way to show us your actual code (either copy & paste or upload).

And, DO NOT show us expected output that is not your expected output. If we can't tell what was done to make it readable instead of being the actual output you expect, you are just adding confusion to your specification.

Don Cragun

View Public Profile for Don Cragun

Find all posts by Don Cragun

Shell Programming and Scripting

awk - Multiple files - 1 file with multi-line data

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

In PErl script: need to read the data one file and generate multiple files based on the data

Discussion started by: Sanjeev G

2. Shell Programming and Scripting

awk - Multi-line data to be stored in variable

Discussion started by: chill3chee

3. Shell Programming and Scripting

awk - 2 files comparison without for loop - multi-line issue

Discussion started by: chill3chee

4. Shell Programming and Scripting

awk : Filter a set of data to parse header line and last field of multiple same match.

Discussion started by: rveri

5. Shell Programming and Scripting

How to substract selective values in multi row, multi column file (using awk or sed?)

Discussion started by: nricardo

6. Shell Programming and Scripting

Multi-line filtering based on multi-line pattern in a file

Discussion started by: Finja

7. UNIX for Dummies Questions & Answers

awk, extract last line of multiple files

Discussion started by: Liverpaul09

8. UNIX for Dummies Questions & Answers

Using AWK: Extract data from multiple files and output to multiple new files

Discussion started by: Liverpaul09

9. UNIX for Dummies Questions & Answers

AWK, extract data from multiple files

Discussion started by: Liverpaul09

10. Shell Programming and Scripting

Using AWK to separate data from a large XML file into multiple files

Discussion started by: JRy