Just want to ask if there is a shorter hand to doing this one liner


 
Thread Tools Search this Thread
Top Forums UNIX for Beginners Questions & Answers Just want to ask if there is a shorter hand to doing this one liner
# 1  
Old 02-18-2019
Just want to ask if there is a shorter hand to doing this one liner

Hi all,

In Oracle, I am using SQL*Plus and selecting all rows in a table and spooling to a file as pipe delimited. I have to use pagesize 0 but unfortunately, using this option excludes the header and I can't get around having it to display the header fields.

So to get around this, I have to create some header file and combine the output from the non-header output and the header only output. This is working fine, I am just curious if there is a shorter version of the command that I am using to create the header line.

Here is what I am doing at the moment.

Below is the header text file doing a describe of the table:

Code:
$: cat a.txt
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 USERNAME                                  NOT NULL VARCHAR2(30 CHAR)
 ROLENAME                                  NOT NULL VARCHAR2(30 CHAR)
 SWITCHROLE                                NOT NULL VARCHAR2(1 CHAR)

And this is the command that I am using to convert the content of a.txt to a header file

Code:
$: awk 'NR > 2' a.txt | awk '{ print $1 }' | grep -v "^$" | paste -d"|" -s - | awk -F"|" '{ printf "%-30s|%-30s|%-15s\n", $1, $2, $3 }' | tee -a a.header.txt
USERNAME                      |ROLENAME                      |SWITCHROLE

Then I do a cat a.header.txt a.out.txt > a.xls. Sample a.out.txt is as below:

Code:
MICKEY                        |XX_AR_COLLECTZZ_INQUIRY       |N
DONALD                        |YY_AA_TEAMMBR                 |N

Final output is as below:

Code:
USERNAME                      |ROLENAME                      |SWITCHROLE
MICKEY                        |XX_AR_COLLECTZZ_INQUIRY       |N
DONALD                        |YY_AA_TEAMMBR                 |N

While what am doing at the moment works fine, just want to know if there is a more shorter way of doing it :-)
# 2  
Old 02-19-2019
This is possible within a single awk call:

program.awk
Code:
 (FNR == NR && NR > 2) {
        headers[NR-2]=$1
}

(FNR != NR) {
        if(FNR == 1) {
           printf "%-30s|%-30s|%-15s\n",headers[1],headers[2],headers[3]
        }
        print
}

Call it like an external program like this: awk -f program.awk header.txt data.txt or inline as ...
Code:
awk '(FNR==NR && NR > 2) { h[NR-2]=$1 } (FNR!=NR) { if(FNR==1) { printf "%-30s|%-30s|%-15s\n",h[1],h[2],h[3] } print }' header.txt data.txt


Last edited by stomp; 02-19-2019 at 05:25 AM..
# 3  
Old 02-19-2019
One could also try:
Code:
awk 'FNR==NR{if(NR>2)printf((++cnt%3)?"%-30s|":"%s\n",$1);next}1' a.txt a.out.txt >a.xls

This User Gave Thanks to Don Cragun For This Post:
Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Merge left hand strings mapping to different right hand strings

Hello, I am working on an Urdu to Hindi dictionary which has the following structure: a=b a=c n=d n=q and so on. i.e. Headword separated from gloss by a = I am giving below a live sample بتا=बता بتا=बित्ता بتا=बुत्ता بتان=बतान بتان=बितान بتانا=बिताना I need the following... (3 Replies)
Discussion started by: gimley
3 Replies

2. Shell Programming and Scripting

Combine columns from many files but keep them aligned in columns-shorter left column issue

Hello everyone, I searched the forum looking for answers to this but I could not pinpoint exactly what I need as I keep having trouble. I have many files each having two columns and hundreds of rows. first column is a string (can have many words) and the second column is a number.The files are... (5 Replies)
Discussion started by: isildur1234
5 Replies

3. Shell Programming and Scripting

Search & Replace regex Perl one liner to AWK one liner

Thanks for giving your time and effort to answer questions and helping newbies like me understand awk. I have a huge file, millions of lines, so perl takes quite a bit of time, I'd like to convert these perl one liners to awk. Basically I'd like all lines with ISA sandwiched between... (9 Replies)
Discussion started by: verge
9 Replies

4. Shell Programming and Scripting

Deleting shorter lines from data file

Hello, I have the following data file structure: 1234 text 2345 text 3456 text text text 4567 text text text 5678 text text text 6789 text text text I simply want to delete all of the lines that only have one text column (i.e. the first two lines in this case). The output would be:... (1 Reply)
Discussion started by: palex
1 Replies

5. UNIX for Dummies Questions & Answers

Shorter AWK for my code?

Hi Folks, I know my code works, but I'm still a newbie at arrays and how they function. Is there is shorter way to write my code? I'm taking averages in multiple files and concatenating output into 1 file. TIA! for file in *; do awk -F"\t" '{a1+=$1}{a2+=$2}{a3+=$3} {a4+=$4}{a5+=$5}... (1 Reply)
Discussion started by: calitiggr
1 Replies

6. Debian

change initramfs by hand?

What's the correct way to change the initramfs file that's used during boot? I know that it's a gzipped cpio archive, but when I gunzip, extract, re-archive (without changing any files), and gzip, then the result is that the system does not boot any more. And I even set the cpio archive type. ... (18 Replies)
Discussion started by: frankie06
18 Replies

7. Shell Programming and Scripting

Need a hand. Please?

i have a script in sh. with awk, e.g. want to list all the contents of a subdirectory an a tabular way. ej: outoput directory1 subdirectory1 subdirectory2 subdirectory3 file1 filen file2 filez file2 ... filen+1 ... (1 Reply)
Discussion started by: alexcol
1 Replies

8. Shell Programming and Scripting

How to get the most left hand string ??

Hi, I remember once seeing a way to get the left most string in a word. Let's say: a="First.Second.Third" (separated by dot) echo ${a#*.} shows --> Second.Third echo ${a##*.} shows --> Third How do I get the the left most string "First" Or "First.Second" ??? Tried to replace #... (2 Replies)
Discussion started by: jfortes
2 Replies

9. UNIX for Dummies Questions & Answers

Give us a hand

How do you get an awk output into columns i.e. awk (print $1,$2,$3) doesn't come out into nice columns but lots of lines of txt want something more like. I am crap at unix so give me a hand thx Rich (3 Replies)
Discussion started by: RichardB
3 Replies
Login or Register to Ask a Question