I have a text file looks like:
lang_tab id number
lang_tab name varchar2(10)
lang_tab suffix varchar2(10)
does anyone know/have a script to create output like:
an array with "," delimiter. e.g. id,name,sufix
thanks in advance
Printable View
I have a text file looks like:
lang_tab id number
lang_tab name varchar2(10)
lang_tab suffix varchar2(10)
does anyone know/have a script to create output like:
an array with "," delimiter. e.g. id,name,sufix
thanks in advance
it looks like the file is space delimited, you can use:
cat file_name | awk '{print $1,","$2,","$3}'
#!/bin/sh
$ORACLE_HOME/bin/sqlplus -S usrename/password@servicename << EOF
set heading off
set pagesize 1000
set arraysize 100
spool filename
select id||","||name||","||suffix
from table_name;
spool off
EOF
exit
-----------------------
Sam
sorry, I did not make it clear. I want only display the 2nd field of each row and concated into a line with "," as delimiter.
the text file looks like 3xn
lang_tab id number
lang_tab name varchar2(10)
lang_tab suffix varchar2(10)
: : :
lang_tab user varchar2(16)
the output I want to be an array
id,name,suffix
thanks
Is your text file is fixed field format or variable filed format.
Sam
post a sample data and how you want it to become.
You might be able to do it in 'vi' itself.
I spool
select TABLE_NAME,column_name,DATA_TYPE from user_tab_columns;
to a text file:
This is the sample data which spooled from sqlplus
cust_tab custid number
cust_tab custname varchar2(20)
cust_tab ...
: : :
cust_tab user varchar2(16)
lang_tab langid number
lang_tab name varchar2(10)
lang_tab suffix varchar2(10)
: : :
lang_tab user varchar2(16)
now, I need to create a string which contains all column name for each table.
output should be:
custid,custname,descption,..user
langid,langname,suffic,..user
Confusion !!
The SQL query that you have mentioned should create one line records (with proper linesize). The you can use the awk option you that has been posted earlier.
Why are you saying that you are getting one column per line ?
I guess I still not make it clear enough. sorry.
Say I have 2 table cust_tab and lang_tab
cust_tab contains 5 columns: custid, custname, custaddr, custphone, updateuser.
lang_tab contain 4 columns: langid, langname, langdesc, updateuser.
I want create a list column name for each table. (one line per table, use "," to seperate columns)
so the output will be
custid, custname, custaddr, custphone, updateuser
langid, langname, langdesc, updateuser
----
I assume I should spool them into a text file
the text file will be:
cust_tab custid number
cust_tab custname varchar2(20)
cust_tab custaddr varchar2(40)
cust_tab custphone varchar2(16)
cust_tab updateuser varchar2(16)
lang_tab langid number
lang_tab langnam varchar2(30)
lang_tab langdesc varchar2(30)
lang_tab updateuser varchar2(16)
then AWK to lines. how?
thanks
If you need the SQL query to be in the text file format, go ahead and use the shell script what I had posted in there with your query parameters in place.
I still don't get what you are looking for??? :confused:
Explain the purpose/need step by step.
Sam
okay, so you are talking @ actual columns and not the records in the table. I thought your example of selecting from USER_TAB_COLUMNS was just an example.
So, if you spool to a file :
awk '{ printf("%s,",$1) }END{ printf("\n")}' your_spool_file
should do the job.
thanks SVK, it works.
but I still need your help.
The result:
cust_id,cust_name,cust_descp,updateuser,
How I eliminate the last ",".
thanks, again
Once you execute the awk, you can used sed to eliminate the last ','.
Or, you can use a simple logic in awk to check for the record number (NR) and don't print the ',' for the first record and print a ',' before the actual field for all other records. Simple.
svk,
I am not good at either one. I try to use MAN to figure out how but no answer. Could you please give me the syntax.
thanks.
Hi, I am not quite sure what you want, but under the assumption that you are only interested in the second field for every 5 line, here's a script that might do just that. It could be shorter, but here it is:
script:
#!/bin/sh
#!/bin/sh
awk '{
if($2 != "") {printf "%s," ,$2; }
if(getline) {printf "%s," ,$2;}
if(getline) {printf "%s," ,$2;}
if(getline) {printf "%s," ,$2;}
if(getline) {printf "%s\n" ,$2;}
}' < punt
Lets get this completed :
Create a .sql file as follows :
spool /tmp/x
set pages 0
set lines 200
set feedback off
set head off
select table_name,column_name,data_type,column_id
from user_tab_columns
order by 1,4;
spool off
Executing this file script will create a spool file /tmp/x.lst that is in the format that you have been mentioning since begining.
Now, create this awk script : (lets call it /tmp/x.awk)
BEGIN {
prev_table="."
}
{
curr_table=$1
if ( curr_table!=prev_table )
{
prev_table=curr_table
printf("\nTable %s : %s",$1,$2)
}
else
{
printf(",%s",$2)
}
}
Execute this awk script as follows :
awk -f /tmp/x.awk /tmp/x.lst
The output will be as follows :
Table ABC : COL1,COL2,COL3
Table DEF$_PROPAGATOR : USERID,USERNAME,CREATED
You can redirect output of the above awk command to a file.
If you do not want the "Table .... : " part in the output, edit the awk script as required.
Does it solve the issue ?
[Edited by svk on 08-28-2001 at 06:38 PM]
Hi,
So you want table_name,column_name,column_name,...
use a cursor
select table_name,column_name
from dba_tab_columns (OR user_tab_columns)
and loop for each table.
use dbms.output to create the right output.
Hope this helps
Hello SVK
could you plaese explain your code i am feeling it a bit diffcult to understand it.
regards
hrishy
svk,
thanks, it works perfectly.
In fact, last night I use your previous suggestion
at end I use sed to remove the pending ",".
awk '{ printf("%s,",$1) }END{ printf("\n")}' your_spool_file | sed "s/\,$//g"
both works !!! this is not the first time you help me out, thanks.
jm