different files for different values

ss_ss · January 13, 2009, 3:28am

Hi

I am writng a script to spool some account nos information to .txt file but wants that information for each and every account no to be written in a different .txt file with account no as part of the file name of its associated file.

Thanks

angheloko · January 13, 2009, 3:40am

Hi ss_ss,

What are the other contents of the file (source file)?

You may want to include more details about your problem so that it would be easier for others to help

ss_ss · January 13, 2009, 4:01am

Hi,

I've written a query whose output is:

D A/C No Mob NO Elem ID Profile ID

D 44083669 0192652286 10866 1

D 44083455 0192300318 10867 1

Now i want to create 2 files containing 1 row each and their names should be like for 1st row File1_44083669_<YYYYMMDDHHMISS>.txt
and for 2nd row File2_44083455_<YYYYMMDDHHMISS>.txt

And in those files column header should not come.

Thanks

angheloko · January 13, 2009, 4:38am

Assuming your source file is tab delimited...

SRCFILE="srcfile.txt"
COUNT=0
sed '1d' $SRCFILE | while read X; do
        ((COUNT=COUNT+1))
        OUTFILE="file${COUNT}_"`echo "$X" | awk -F"\t" '{ print $2 }'`"_"`date +%Y%m%d%H%M%S`".txt"
        echo "$X" > $OUTFILE
done

Just update the SRCFILE variable. Also, there are other ways you can do this like using cut, sed, etc...So if you want a different solution, you can use those commands also

ss_ss · January 13, 2009, 5:25am

Thnx a lot for ur help

But after that also im getting some errors like:

SQL> SQL> SQL> SQL> SP2-0734: unknown command beginning "SRCFILE="T..." - rest of line ignored.
SQL> SP2-0042: unknown command "COUNT=0" - rest of line ignored.
SQL> SP2-0734: unknown command beginning "sed '1d' ..." - rest of line ignored.
SQL> 2 3 4 SQL> SP2-0042: unknown command "done" - rest of line ignored.

I am putting the whole code can you just look into:

#!/bin/sh

sqlplus uname/pwd@dbname <<EOF

set wrap off
set feedback off
set pagesize 0
set verify off
set trimspool on
set linesize 5000

spool /SYSTEM/custom/modules/testing/THS_PROF.txt

select 'D' "D", ciam.external_id "Kenan Account No", ciem.external_id "Mobile MSISDN", p.element_id "Element Id", case
when (p.element_id in (select b.member_id from package_component_members b, package_components c
where member_id in (10867,10866,10866,10867,10868,10866,10866,90014,90021)
and b.member_type=1
and b.component_id in (10459,10458,10658,20105,10499,10647,10810,90014,90022)
and c.component_id=b.component_id)) then '1'
else '200'
end "Ths Profile Id"
from customer_id_acct_map ciam, customer_id_equip_map ciem, product p, cmf c, dual
where ciam.account_no(+)=p.billing_account_no
and ciem.subscr_no=p.parent_subscr_no
and p.billing_account_no=c.account_no
and c.account_no=ciam.account_no
and c.bill_period='G24'
and ciem.external_id_type=1
and ciam.external_id_type=1
and ciem.external_id_type=ciam.external_id_type
and p.element_id in (select b.member_id from package_component_members b, package_components c
where member_id in (10867,10866,10866,10867,10868,10866,10866,90014,90021)
and b.member_type=1
and b.component_id in (10459,10458,10658,20105,10499,10647,10810,90014,90022)
and c.component_id=b.component_id);

spool off;

SRCFILE="THS_PROF.txt"
COUNT=0
sed '1d' $SRCFILE | while read X; do
((COUNT=COUNT+1))
OUTFILE="file${COUNT}"`echo "$X" | awk -F"\t" '{ print $2 }'`""`date +%Y%m%d%H%M%S`".txt"
echo "$X" > $OUTFILE
done

quit

EOF

angheloko · January 13, 2009, 7:12am

Hi ss,

I wasn't aware that you'll be using it with sqlplus. Try inserting the script after the 'EOF' token.

The whole script will look like this:

sqlplus uname/pwd@dbname << EOF

set wrap off
set feedback off
set pagesize 0
set verify off
set trimspool on
set linesize 5000

spool /SYSTEM/custom/modules/testing/THS_PROF.txt

select 'D' "D", ciam.external_id "Kenan Account No", ciem.external_id "Mobile MSISDN", p.element_id "Element Id", case
when (p.element_id in (select b.member_id from package_component_members b, package_components c
where member_id in (10867,10866,10866,10867,10868,10866,10866,90014,90021)
and b.member_type=1
and b.component_id in (10459,10458,10658,20105,10499,10647,10810,90014,90022)
and c.component_id=b.component_id)) then '1'
else '200'
end "Ths Profile Id"
from customer_id_acct_map ciam, customer_id_equip_map ciem, product p, cmf c, dual
where ciam.account_no(+)=p.billing_account_no
and ciem.subscr_no=p.parent_subscr_no
and p.billing_account_no=c.account_no
and c.account_no=ciam.account_no
and c.bill_period='G24'
and ciem.external_id_type=1
and ciam.external_id_type=1
and ciem.external_id_type=ciam.external_id_type
and p.element_id in (select b.member_id from package_component_members b, package_components c
where member_id in (10867,10866,10866,10867,10868,10866,10866,90014,90021)
and b.member_type=1
and b.component_id in (10459,10458,10658,20105,10499,10647,10810,90014,90022)
and c.component_id=b.component_id);

spool off;

done

quit

EOF 

SRCFILE="THS_PROF.txt"
COUNT=0
sed '1d' $SRCFILE | while read X; do
((COUNT=COUNT+1))
OUTFILE="file${COUNT}_"`echo "$X" | awk -F"\t" '{ print $2 }'`"_"`date +%Y%m%d%H%M%S`".txt"
echo "$X" > $OUTFILE

Another solution would be to use a t-sql looping inside the sql segment of the code but you'll have to redefine your output for each loop. But I'm not much familiar with oracle (or is it not oracle?)

Try the modified one

ss_ss · January 13, 2009, 7:22am

Thanx it worked just done needs to be put at the end.

But need to ask you 1 more thing that is, as i metioned if the output of the query is 3 rows then i want 3 different files with a\c no in the name of the file for each of them containing their respective values.

angheloko · January 13, 2009, 7:31am

Hi,

Yes, I think it will work for n no. of records.

A limitation though is that the AC No. must be in the 2nd column. If you decide to move the AC No. to a different column then you'll have to update this line

OUTFILE="file${COUNT}_"`echo "$X" | awk -F"\t" '{ print $n }'`"_"`date +%Y%m%d%H%M%S`".txt"

Where n would be the column no. of the AC No.

ss_ss · January 13, 2009, 9:45pm

If my qry returns 3 rows then 3 files are being created but in the file name im not getting the a/c no and secondly for ex if a/c no is 45678 then in this a/c no file im not getting the values corresponding to this a/c no.

the output of my query is pipe delimited.

and "spool /SYSTEM/custom/modules/testing/THS_PROF.txt" because of this line i am getting this output file also apart form the files created by sed but i dont want this file.

Thanks

angheloko · January 13, 2009, 10:07pm

Hi ss,

Since your file is pipe-delimited then we'll have to change the -F option of the awk command to -F"|". Have you done that already?

Post sample output so that we can further debug

As for the unnecessary line in your output, is it the last line? What line is it? We can just exempt that line from being processed by using sed, head, or tail depending on the situation.

ss_ss · January 13, 2009, 10:23pm

Thanks again
But apart from the needed files im getting some extra files also.

For ex: If the query returns two rows, then im getting 2 o/p files as:

THS_PROF_44083669_20090114111146.txt

THS_PROF_44083455_20090114111146.txt

with their corresponding values i.e

cat THS_PROF_44083669_20090114111146.txt
D|44083669|0192652286|10866|1

cat THS_PROF_44083455_20090114111146.txt
D|44083455|0192300318|10867|1

Apart from these two files im getting 2 another files i.e.

THS_PROF.txt

THS_PROF__20090114111146.txt

cat THS_PROF.txt
SQL>
SQL> select 'D'||'|'||ciam.external_id||'|'||ciem.external_id||'|'||p.element_id||'|'||
  2  case when (p.element_id in (select b.member_id from package_component_members b, package_components c
  3  where member_id in (10867,10866,10866,10867,10868,10866,10866,90014,90021)
  4  and b.member_type=1
  5  and b.component_id in (10459,10458,10658,20105,10499,10647,10810,90014,90022)
  6  and c.component_id=b.component_id)) then '1'
  7  end "A"
  8  from customer_id_acct_map ciam, customer_id_equip_map ciem, product p, cmf c, dual
  9  where ciam.account_no=p.billing_account_no
 10  and ciem.subscr_no=p.parent_subscr_no
 11  and p.billing_account_no=c.account_no
 12  and c.account_no=ciam.account_no
 13  and c.bill_period='G08'
 14  and ciem.external_id_type=1
 15  and ciam.external_id_type=1
 16  and ciem.external_id_type=ciam.external_id_type
 17  and p.element_id in (select b.member_id from package_component_members b, package_components c
 18  where member_id in (10867,10866,10866,10867,10868,10866,10866,90014,90021)
 19  and b.member_type=1
 20  and b.component_id in (10459,10458,10658,20105,10499,10647,10810,90014,90022)
 21  and c.component_id=b.component_id);
D|44083669|0192652286|10866|1
D|44083455|0192300318|10867|1
SQL>
SQL> spool off;

cat THS_PROF__20090114111146.txt
SQL> spool off;

Now plz help me how can i get rid of these 2 unwanted files.

Thanks

angheloko · January 13, 2009, 10:46pm

Hi,

Can you post a sample source file (the file where the pipe-delimited results are being written). So we can know what it looks like.

ss_ss · January 13, 2009, 10:51pm

cat THS_PROF_44083669_20090114111146.txt

D|44083669|0192652286|10866|1

cat THS_PROF_44083455_20090114111146.txt

D|44083455|0192300318|10867|1

angheloko · January 14, 2009, 12:05am

Hi ss,

Sorry, I meant the THS_PROF.txt (the output of the sql) so that we can see where the unnecessary line is.

ss_ss · January 14, 2009, 12:10am

the output of the sql is:

D|44083669|0192652286|10866|1
D|44083455|0192300318|10867|1

and the utput of the THS_PROF.txt is:

SQL>
SQL> select 'D'||'|'||ciam.external_id||'|'||ciem.external_id||'|'||p.element_id||'|'||
2 case when (p.element_id in (select b.member_id from package_component_members b, package_components c
3 where member_id in (10867,10866,10866,10867,10868,10866,10866,90014,90021)
4 and b.member_type=1
5 and b.component_id in (10459,10458,10658,20105,10499,10647,10810,90014,90022)
6 and c.component_id=b.component_id)) then '1'
7 end "A"
8 from customer_id_acct_map ciam, customer_id_equip_map ciem, product p, cmf c, dual
9 where ciam.account_no=p.billing_account_no
10 and ciem.subscr_no=p.parent_subscr_no
11 and p.billing_account_no=c.account_no
12 and c.account_no=ciam.account_no
13 and c.bill_period='G08'
14 and ciem.external_id_type=1
15 and ciam.external_id_type=1
16 and ciem.external_id_type=ciam.external_id_type
17 and p.element_id in (select b.member_id from package_component_members b, package_components c
18 where member_id in (10867,10866,10866,10867,10868,10866,10866,90014,90021)
19 and b.member_type=1
20 and b.component_id in (10459,10458,10658,20105,10499,10647,10810,90014,90022)
21 and c.component_id=b.component_id);
D|44083669|0192652286|10866|1
D|44083455|0192300318|10867|1
SQL>
SQL> spool off;

But i dont want THS_PROF.txt to be created jus 2 files corresponds to 2 rows of the query.

angheloko · January 14, 2009, 1:34am

Try adding

sed 's\spool /SYSTEM/custom/modules/testing/THS_PROF.txt\//g'

before

SRCFILE="THS_PROF.txt"
COUNT=0
sed '1d' $SRCFILE | while read X; do
((COUNT=COUNT+1))
OUTFILE="file${COUNT}_"`echo "$X" | awk -F"\t" '{ print $2 }'`"_"`date +%Y%m%d%H%M%S`".txt"
echo "$X" > $OUTFILE

This will attempt to remove the line...I'm not sure though if this will be successful. I cannot test it right now.

ss_ss · January 14, 2009, 1:53am

Error

sed: Function s\spool /SYSTEM/custom/modules/testing/THS_PROF.txt\//g cannot be parsed.

angheloko · January 14, 2009, 2:39am

sed 's\spool /SYSTEM/custom/modules/testing/THS_PROF.txt\\g'

My bad...Try again

ss_ss · January 14, 2009, 2:51am

Could you plz tell me wht this line of code can actually do???????????????????

Same thing sed: Function s\spool /SYSTEM/custom/modules/testing/THS_PROF.txt\\g cannot be parsed.

ss_ss · January 14, 2009, 3:16am

How can i modify this sed stmt to create just 1 file for all the account nos with the value passed as argument should be in the file name in place of account no like THS_PROF_G03_20090114155816.txt where G03 is the value passed as argument:

SRCFILE="THS_PROF.txt"
sed '1d' $SRCFILE | while read X; do
OUTFILE="THS_PROF_"`echo "$X" | awk -F"\t" '{ print $2 }'`"_"`date +%Y%m%d%H%M%S`".txt"
echo "$X" > $OUTFILE