Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

How to use awk for printing line from database table?

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting How to use awk for printing line from database table?
# 1  
Old 07-22-2010
How to use awk for printing line from database table?
Hi ,
I have inserted some records in a table having column "value1 varchar2(4000)" and want to spool in a file.
I have written as below
Code:
set echo off
set feed off
set hea off
set wra off
set lin 500
spo temp_table

and fired select query as below
Code:
select value1 from temp_table;
spo off

Now the output length of each column is 500 as i have given above.
The data in the temp_table varry in length and seperated by semicolon";" as below.
Code:
asdfg;sdeeeedf;;dfffff;;;fffdfd;;;dfdfdfdf;;;;dfdfdfdfdf;;;27-JUL-2010;
werwerg;ssdf;;dfffff;;;feee;;;dfdfdwewrefdf;;;;dfdfdfdfdf;;;25-JUL-2010;
asdfg;sdfsdf;;dfffff;;;fffdfd;;;dfdwerfdfdf;;;;dfdfdfdfdf;;;22-JUL-2010;
aasdasddfg;sasdasddfs;;eeeeefff;;;fffwewdfd;;;dfdfdfdf;;;;dfdfdfdfdf;;;29-JUL-2010;

and at last of each line semicolon is there.

How to copy the data as above in a file using awk,as it is going as below format with space or white character at the end with length of each line as 500
Code:
asdfg;sdeeeedf;;dfffff;;;fffdfd;;;dfdfdfdf;;;;dfdfdfdfdf;;;27-JUL-2010; 
werwerg;ssdf;;dfffff;;;feee;;;dfdfdwewrefdf;;;;dfdfdfdfdf;;;25-JUL-2010; 
asdfg;sdfsdf;;dfffff;;;fffdfd;;;dfdwerfdfdf;;;;dfdfdfdfdf;;;22-JUL-2010; 
aasdasddfg;sasdasddfs;;eeeeefff;;;fffwewdfd;;;dfdfdfdf;;;;dfdfdfdfdf;;;29-JUL-2010;

Moderator's Comments:
Mod Comment Next time use code tags please, ty.
# 2  
Old 07-22-2010
Code:
while read LINE; do printf "%-500s\n" "$LINE"; done < infile > newfile

# 3  
Old 07-22-2010
Not Working, Still having same output
Quote:
Originally Posted by zaxxon
Code:
while read LINE; do printf "%-500s\n" "$LINE"; done < infile > newfile

Thanks for reply, but this is not working and giving same output.
# 4  
Old 07-22-2010
It is working fine on my box. How do you check if you have a width of 500 per line?
Maybe open the new file with
Code:
od -c outfile

or check the size of the file which must be larger than the input file.
You can also check while opening with vi and do ":set list" for example.

---------- Post updated at 11:05 AM ---------- Previous update was at 11:02 AM ----------

Here for proof:

Code:
$> ls -l infile
-rw-r--r-- 1 isau isau 15 22. Jul 11:02 infile
$> cat infile
eins
zwei
drei
$> while read LINE; do printf "%-10s\n" "$LINE"; done < infile > outfile
$> ls -l outfile
-rw-r--r-- 1 root root 33 22. Jul 11:04 outfile
$> od -c outfile
0000000   e   i   n   s                          \n   z   w   e   i
0000020                      \n   d   r   e   i
0000040  \n
0000041

# 5  
Old 07-22-2010
Quote:
Originally Posted by zaxxon
It is working fine on my box. How do you check if you have a width of 500 per line?
Maybe open the new file with
Code:
od -c outfile

or check the size of the file which must be larger than the input file.
You can also check while opening with vi and do ":set list" for example.

---------- Post updated at 11:05 AM ---------- Previous update was at 11:02 AM ----------

Here for proof:

Code:
$> ls -l infile
-rw-r--r-- 1 isau isau 15 22. Jul 11:02 infile
$> cat infile
eins
zwei
drei
$> while read LINE; do printf "%-10s\n" "$LINE"; done < infile > outfile
$> ls -l outfile
-rw-r--r-- 1 root root 33 22. Jul 11:04 outfile
$> od -c outfile
0000000   e   i   n   s                          \n   z   w   e   i
0000020                      \n   d   r   e   i
0000040  \n
0000041

Sorry man, But I think you have not understand my prob properly.
Let me explain u.
I have already set the lin size as 500 so what ever line is coming from table they all have same lenght as 500 even if the content is less let say 230 but some white character or say space is coming after semicolon.
When i am doing spool then the contents are coming with white character or space as below
Code:
asdfg;sdeeeedf;;dfffff;;;fffdfd;;;dfdfdfdf;;;;dfdfdfdfdf;;;27-JUL-2010;<space    or white char                                                            space>
werwerg;ssdf;;dfffff;;;feee;;;dfdfdwewrefdf;;;;dfdfdfdfdf;;;25-JUL-2010;<space     or white char                                                                      space>
asdfg;sdfsdf;;dfffff;;;fffdfd;;;dfdwerfdfdf;;;;dfdfdfdfdf;;;22-JUL-2010; <space  or white char                                                           space>
aasdasddfg;sasdasddfs;;eeeeefff;;;fffwewdfd;;;dfdfdfdf;;;;dfdfdfdfdf;;;29-JUL-2010;<space  or white char                                                           space>

but i want in below format
Code:
asdfg;sdeeeedf;;dfffff;;;fffdfd;;;dfdfdfdf;;;;dfdfdfdfdf;;;27-JUL-2010;
werwerg;ssdf;;dfffff;;;feee;;;dfdfdwewrefdf;;;;dfdfdfdfdf;;;25-JUL-2010;
asdfg;sdfsdf;;dfffff;;;fffdfd;;;dfdwerfdfdf;;;;dfdfdfdfdf;;;22-JUL-2010;
aasdasddfg;sasdasddfs;;eeeeefff;;;fffwewdfd;;;dfdfdfdf;;;;dfdfdfdfdf;;;29-JUL-2010;

I think now you can understand my problem.
Thanks anyway.
Last edited by CaapAjayShukla; 07-22-2010 at 07:04 AM..
# 6  
Old 07-22-2010
I don't seem to understand the requirement too,
if you want to trim the white space characters at the end of each line, just enable trimspool:

Code:
set trimspool on

# 7  
Old 07-22-2010
Thanks man I think it will work. Really Thanks
Quote:
Originally Posted by radoulov
I don't seem to understand the requirement too,
if you want to trim the white space characters at the end of each line, just enable trimspool:

Code:
set trimspool on

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Printing string from last field of the nth line of file to start (or end) of each line (awk I think)

My file (the output of an experiment) starts off looking like this, _____________________________________________________________ Subjects incorporated to date: 001 Data file started on machine PKSHS260-05CP ********************************************************************** Subject 1,... (9 Replies)
Discussion started by: samonl
9 Replies

2. Shell Programming and Scripting

awk printing output to new line

Hi I have a file profile.txt with the below input: {"atgUserId":"736f14c4-eda2-4531-9d40-9de4d6d1fb0f","firstName":"donna","lastName":"biehler","email":"schoolathome42@live.com","receiveEmail":"y es"}, {"atgUserId":"c3716baf-9bf8-42da-8a44-a13fff68d20f","firstName":"Gilberto... (6 Replies)
Discussion started by: ankur328
6 Replies

3. Shell Programming and Scripting

How to read a text file line by line and insert into a database table?

I have a test file that I want to read and insert only certain lines into the the table based on a filter. 1. Rread the log file 12 Hours back Getdate() -12 Hours 2. Extract the following information on for lines that say "DUMP is complete" A. Date B. Database Name C.... (2 Replies)
Discussion started by: JolietJake
2 Replies

4. Shell Programming and Scripting

Awk: Comparing arguments with in line values of file and printing the result

I need to develop a script where I will take two date arguments as parameter date1 and date2 which will in format YYYYMM. Below is the input file say sample.txt. sample.txt will have certain blocks starting with P1. Each block will have a value 118,1:TIMESTAMP. I need to compare the... (7 Replies)
Discussion started by: garvit184
7 Replies

5. Shell Programming and Scripting

sed , awk script for printing matched line before regular expression

hi All , I am having a large file with lots of modules as shown below ############################################### module KKK kksd kskks jsn;lsm jsnlsn; Ring jjsjsj kskmsm jjs endmodule module llll 1kksd11 k232skks j33sn;l55sm (6 Replies)
Discussion started by: kshitij
6 Replies

6. UNIX for Dummies Questions & Answers

Using awk to find max and printing entire line

Hi folks, I am very new to awk. I have what is probably a very simple question. I'm trying to get the max value of column 1, but also print column 2. My data looks like this: 0.044|2000-02-03 14:00:00 5.23|2000-02-03 05:45:00 5.26|2000-02-03 11:15:00 0|2000-02-01 18:30:00 So in this case... (2 Replies)
Discussion started by: amandarobe
2 Replies

7. Shell Programming and Scripting

Matching and printing line with awk

Hi there, I'm trying to use awk to print out the entire line that contains a match to a certain regex and then append some text,plus the match to the end of the line. So far I have: awk -F: '{print "RG:Z:" $2}' file Which prints out the match I want plus the additional text, but I'm stuck... (3 Replies)
Discussion started by: jim_lad
3 Replies

8. Shell Programming and Scripting

Maybe by AWK: printing help diagonal matrix characters into line

Hi Experts, I want to print this charts diagonal data into straight lines. This is a matrix 24X24 Horizontal and vertical. - I want to print all the diagonal cutting characters into straight line: Data: E F S S A H A L L A T M C N O T S O B O D U Q H I W I B N L O C N I L N L A N S I N... (9 Replies)
Discussion started by: rveri
9 Replies

9. Shell Programming and Scripting

Printing a specific line using AWK

Hi, I have a script that fetches only specific information from fcinfo command. Below is a portion of the script. #!/usr/bin/ksh set -x HBA_COUNT=`sudo fcinfo hba-port | grep -i state | awk 'END{print NR}'` echo "$HBA_COUNT HBAs exist" echo '........' INDEX=1 while $INDEX -le... (2 Replies)
Discussion started by: jake_won
2 Replies

10. Shell Programming and Scripting

printing a line number using awk

Hi Chaps, I'm trying to print the line number of a comma delimited file where the second field in the line is blank using AWK. Here is the code I have so far where am I going wrong. It is the last column in the file. nawk -v x==0 'BEGIN {FS=",";OFS=","} x++ if ($2 == " ") print $x' bob.tst ... (3 Replies)
Discussion started by: rjsha1
3 Replies
Login or Register to Ask a Question