Click here to Skip to main content
14,270,872 members
Rate this:
Please Sign up or sign in to vote.
See more:
required data on xls format. Please help me.


set feedback off
set pagesize 0
set head off
set echo off
set linesize 500
set line 200
set verify off

SPOOL C:\DATA\ABN29977_BKG.xls

Select
'CNSG_NO' ||','||
'BKG_DATE' ||','||
'CNSGEE_NAM' ||','||
'REF_NO' ||','||
'DSTN' ||','
FROM DUAL
UNION ALL
select
substr(b.cnsg_no,1,12) ||','||
TO_CHAR(b.bkg_dat,'DD-MON-YYYY') ||','||
replace(substr(t.cnsgee_nam,1,30),',','') ||','||
replace(substr(t.cnsgee_addr1,1,20),',','') ||','||
t.dstn ||','
from oms_cnsg_bill b, oms_cnsg_track t
where b.cnsg_no=t.cnsg_no
and b.cus_no='ABN29977'
and b.bkg_dat between trunc (SYSDATE)-2 and trunc (SYSDATE)-1

/
SPOOL OFF

EXIT;
Posted
Comments
chaau 7-Aug-14 19:04pm
   
So, what's your question? It is not a xls file format. It is a csv. You will be better off if you spool it as a csv file and open with Excel. Wy do you need an extra comma at the end of the lines?
Member 10999119 7-Aug-14 19:58pm
   
Sir, we required data output on xls format instead of csv. Please advise
Richard MacCutchan 8-Aug-14 5:00am
   
Change your file extension to csv and then you can load it direct into Excel. There is no way that SQL can generate a xls format file.
   
Help with what? Do you really hope to write Excel file with SQL?! :-)
—SA
Member 10999119 7-Aug-14 20:25pm
   
Yes ! When we run sql query, generate xls format file not a csv format.
   
It does not seem realistic. :-)
—SA

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

You may create an xls workbook and connect it to your Oracle database using automation.

See "Use automation to create a QueryTable on a worksheet" at
http://support.microsoft.com/kb/247412/en-us[^]

You may remove the connection from the QueryTable object after updating.

Also you may use SQL Server Intergation Services if you have it installed.

See SQL Server Export to Excel using SQL Server Integration Services[^]
   
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100