2

I essentially want to put the name of the query in front of each line containing the SourceField.

I have tried this concatenation scheme

awk '/<\^Query/ && p{print p;p=""}{p=p $0}END{if(p) print p}' 

But this only works until I have multiple source fields. When that happens it concatenates all the lines with SourceField:

Query: D Monthly Loan SourceField: LOAD-NO         SourceTable: MASTER SourceField: LO  

My data is:

Query: D Monthly Loan
    SourceField: LOAD-NO         SourceTable: MASTER
    SourceField: LO              SourceTable: MASTER
Query: D Monthly Loan
    SourceField: HI              SourceTable: MASTER2
QUERY: M FORBEARANCE_1
    SourceField: LOAN-NO         SourceTable: MASTER

I want the output to look like this:

Query: D Monthly Loan SourceField: LOAD-NO         SourceTable: MASTER
Query: D Monthly Loan SourceField: LO              SourceTable: MASTER
Query: D Monthly Loan SourceField: HI              SourceTable: MASTER2
Query: M FORBEARANCE_1 SourceField: LOAN-NO        SourceTable: MASTER
1
  • 1
    <\^ at the start of /<\^Query/ means "a literal < character followed by a literal ^ character". A string like that doesn't exist in your input. I think you just meant /^Query/ which means "the string Query at the start of a line (^)". – Ed Morton Jul 10 '20 at 22:17
5
$ awk '/^ +/{print q, $0; next} {q=$0}' file
Query: D Monthly Loan     SourceField: LOAD-NO         SourceTable: MASTER
Query: D Monthly Loan     SourceField: LO              SourceTable: MASTER
Query: D Monthly Loan     SourceField: HI              SourceTable: MASTER2
QUERY: M FORBEARANCE_1     SourceField: LOAN-NO         SourceTable: MASTER

or if you prefer any of these formats (there are many other possibilities too!):

$ awk 'sub(/^ +/,""){print q, $0; next} {q=$0}' file
Query: D Monthly Loan SourceField: LOAD-NO         SourceTable: MASTER
Query: D Monthly Loan SourceField: LO              SourceTable: MASTER
Query: D Monthly Loan SourceField: HI              SourceTable: MASTER2
QUERY: M FORBEARANCE_1 SourceField: LOAN-NO         SourceTable: MASTER

$ awk '/^ +/{$1=$1; print q, $0; next} {q=$0}' file
Query: D Monthly Loan SourceField: LOAD-NO SourceTable: MASTER
Query: D Monthly Loan SourceField: LO SourceTable: MASTER
Query: D Monthly Loan SourceField: HI SourceTable: MASTER2
QUERY: M FORBEARANCE_1 SourceField: LOAN-NO SourceTable: MASTER

$ awk -v OFS='\t' '/^ +/{$1=$1; print q, $0; next} {q=$0}' file
Query: D Monthly Loan   SourceField:    LOAD-NO SourceTable:    MASTER
Query: D Monthly Loan   SourceField:    LO      SourceTable:    MASTER
Query: D Monthly Loan   SourceField:    HI      SourceTable:    MASTER2
QUERY: M FORBEARANCE_1  SourceField:    LOAN-NO SourceTable:    MASTER
1
  • 1
    Thanks for all the choices! I didn't realize at first that SourceField had a leading tab. So, of course, absolutely none of anyone's solutions worked. Now they all work.But I like the simplicity of your first selection. – C0ppert0p Jul 10 '20 at 23:54
3
sed -n -e '/^Query:\|^QUERY:/{h;}' -e '/^  *SourceField:/{G;s|\(.*\)\n\(.*\)|\2\1|p;}' file
  • /^Query:\|^QUERY:/{h;} If line starts with Query:/QUERY:, put it in the hold space.
  • / *SourceField:/ If line starts with any number of spaces followed by SourceField:, then:
    • G Append a the contents of the hold space preceeding it by a newline.
    • s|\(.*\)\n\(.*\)|\2\1|p Swap the fields around the newline and print the result.

Simplified version, in case your sed supports extended regexes:

sed -nE -e '/^Query:|^QUERY:/{h;}' -e '/^ +SourceField:/{G;s|(.*)\n(.*)|\2\1|p;}' file

Output:

Query: D Monthly Loan    SourceField: LOAD-NO         SourceTable: MASTER
Query: D Monthly Loan    SourceField: LO              SourceTable: MASTER
Query: D Monthly Loan    SourceField: HI              SourceTable: MASTER2
QUERY: M FORBEARANCE_1    SourceField: LOAN-NO         SourceTable: MASTER
2

With GNU awk:

awk '/^Q/{query=$0; next}; /^ /{gsub(/^ */,"",$0); print query,$0}' file

Output:

Query: D Monthly Loan SourceField: LOAD-NO         SourceTable: MASTER
Query: D Monthly Loan SourceField: LO              SourceTable: MASTER
Query: D Monthly Loan SourceField: HI              SourceTable: MASTER2
QUERY: M FORBEARANCE_1 SourceField: LOAN-NO         SourceTable: MASTER

gsub(/^ */,"",$0) removes leading spaces.

1

My solution will not take you all the way to your desired result but it's pretty simple but you can split your source with grep go from there:

# grep Source a|awk '{print "Query: D Monthly Loan" $0}'
Query: D Monthly Loan    SourceField: LOAD-NO         SourceTable: MASTER
Query: D Monthly Loan    SourceField: LO              SourceTable: MASTER
Query: D Monthly Loan    SourceField: HI              SourceTable: MASTER2
Query: D Monthly Loan    SourceField: LOAN-NO         SourceTable: MASTER
# cat a
Query: D Monthly Loan
    SourceField: LOAD-NO         SourceTable: MASTER
    SourceField: LO              SourceTable: MASTER
Query: D Monthly Loan
    SourceField: HI              SourceTable: MASTER2
QUERY: M FORBEARANCE_1
    SourceField: LOAN-NO         SourceTable: MASTER
2
  • You never need grep when you're using awk. grep Source a|awk '{print "Query: D Monthly Loan" $0}' = awk '/Source/{print "Query: D Monthly Loan" $0}' a – Ed Morton Jul 10 '20 at 22:13
  • 1
    thanks for the hint on grep functionality within awk @EdMorton – Roman Spiak Aug 1 '20 at 18:31
1
awk '
/^Query:/ || /^QUERY:/ {queryline=$0}
/ SourceField:/ {print queryline " " $0}
' data.txt
1
  • 2
    /^Query:/ || /^QUERY:/ could be written as tolower($0) ~ /^query:/ to avoid the duplication of uc and lc query. Also print queryline " " $0 could be written as print queryline, $0 to just let OFS do it's job. – Ed Morton Jul 10 '20 at 22:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.