Home » SQL & PL/SQL » SQL & PL/SQL » Extract data in excel/csv and enail automatically
Extract data in excel/csv and enail automatically [message #253708] Tue, 24 July 2007 12:11 Go to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Hi,
I would like to generate the report in excel/csv format and email automatically as daily job, how can i do it?
I tried it on server but it's not formatting propelry.
I also checked TOAD which can generate report in excel but no idea how can i email it to user?
I will appreciate your kind ideas.

Thanks,
Re: Extract data in excel/csv and enail automatically [message #253709 is a reply to message #253708] Tue, 24 July 2007 12:15 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
use BLAT utility.

[Updated on: Tue, 24 July 2007 12:16] by Moderator

Report message to a moderator

Re: Extract data in excel/csv and enail automatically [message #253710 is a reply to message #253708] Tue, 24 July 2007 12:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I tried it on server but it's not formatting propelry.

More precisely?

Quote:
I will appreciate your kind ideas.

Use search, this question has already been asked here a couple of weeks ago and each part (Excel and email) several times here, on AskTom and so on.

Regards
Michel
Re: Extract data in excel/csv and enail automatically [message #253715 is a reply to message #253709] Tue, 24 July 2007 12:53 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Thanks.
Could you please explain in details about BLAT utility?

Thanks,
Re: Extract data in excel/csv and enail automatically [message #253716 is a reply to message #253710] Tue, 24 July 2007 12:56 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Thanks.
I mean to say that i have tried on sql*plus v/s tool formatting nicely but lacking email notification.
If i can run through sql*plus on server and can write shell script to email throught Cron job.
I hope i am cleared this time and sorry about that.

Thanks,
Re: Extract data in excel/csv and enail automatically [message #253718 is a reply to message #253715] Tue, 24 July 2007 13:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://www.google.com/search?hl=en&q=blat&meta=

Regards
Michel
Re: Extract data in excel/csv and enail automatically [message #253721 is a reply to message #253708] Tue, 24 July 2007 13:05 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
How sad. A "Senior Member" who doesn't know how to use Google.

You can lead some folks to knowledge, but you can't make them think.
Re: Extract data in excel/csv and enail automatically [message #253801 is a reply to message #253721] Tue, 24 July 2007 18:57 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Create the Excel content (save to file using UTL_FILE may be easiest if you want to use Unix email like mailx - else store in a CLOB column).
http://htmldb.oracle.com/pls/otn/f?p=18326:54:3505804635922538::::P54_ID:1962
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:728625409049
http://technology.amis.nl/blog/?p=1015

Plenty of email/attachment examples...
http://www.orafaq.com/scripts/

[Updated on: Tue, 24 July 2007 19:01]

Report message to a moderator

Re: Extract data in excel/csv and enail automatically [message #254030 is a reply to message #253801] Wed, 25 July 2007 10:20 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Thanks.
I can schedule and email through CRON job but i am having problem to generate CSV or XLS file 
when I am running following sql. I have Oracle 9i.

set termout off feedback off heading off verify off linesize 150 pagesize 0

SELECT b.s_name||','||
D.login||','|| 
d.f_name||','||
d.l_name||','||
e.desc
FROM CT_E_Stk_MAP a, ct_stk b, e_info c, user d, e_code e 
WHERE a.ct_stk_id = b.ct_stk_id AND a.user = c.user
  AND a.user= d.id AND c.e_c3 = e.c_value AND e.name = 'EO06Y' 
ORDER BY stk_name

Now i am pooling as .csv file in sql*plus but problem is when data is in comma delimitted
like buffallo, NY desc column then it go to next cell.
Ex.
Clerks - Services SA01125 Petrina Johnston ON17-Sanford Oakvil 
This Oakwil goes to next cell instead of in same cell as it should be like -
Clerks - Services SA01125 Petrina Johnston ON17-Sanford, Oakvil

Thanks, 


[Updated on: Wed, 25 July 2007 11:08] by Moderator

Report message to a moderator

Re: Extract data in excel/csv and enail automatically [message #254074 is a reply to message #254030] Wed, 25 July 2007 12:40 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
enclose the data within double quotes.

something like

SELECT '"' || b.s_name||'",'|| ....


HTH

P.S : Only thing you have to look out for is if your data is having double quotes make sure you escape it with another double quotes.
Re: Extract data in excel/csv and enail automatically [message #254103 is a reply to message #254074] Wed, 25 July 2007 14:15 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Thanks Rajaram.
I tried but its pooling csv file records in one line.
Could you please copy here full select so i can know where i am making mistake?

Thanks,
Re: Extract data in excel/csv and enail automatically [message #254750 is a reply to message #254103] Fri, 27 July 2007 16:28 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Hi,
I have written a programme and it does the generate the file in .csv/.xls format and email it back to me, 
but i am having problem with the output as it also giving me spool command.
Ex.
SQL> @report.sql 
...
all the reocrds with heading
....
SQL> spool off

I want to avoid this spool from header and footer.

I am using currently following sql parameters:

set linesize 4000 pagesize 0 trimspool on feedback off verify off markup html on spool on
spool ${cntl_log_file}_${now}_sql_1.xls
@${ORACLE_BASE}/local/bin/report.sql

Thanks, 

[Updated on: Sat, 28 July 2007 00:53] by Moderator

Report message to a moderator

Re: Extract data in excel/csv and enail automatically [message #254764 is a reply to message #254750] Sat, 28 July 2007 00:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Put the "spool off" statement in the SQL script.

Regards
Michel
Re: Extract data in excel/csv and enail automatically [message #255144 is a reply to message #254764] Mon, 30 July 2007 15:35 Go to previous message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Thanks. it works.
Previous Topic: To drop all constraints of a table
Next Topic: ORA:00604
Goto Forum:
  


Current Time: Mon Dec 05 03:10:50 CST 2016

Total time taken to generate the page: 0.13188 seconds