Excel export

[JaxtheDog]

I would like to export Oracle data from a VB6 program recordset to an Excel file - similary to the way TOAD allows you to save a grid as an .XLS file. Does anyone know an easy way to do this? I'd appreciate any help you can offer.

Thanks,
Jax

 

[sem]

I suppose that you should ask this question in VB or Excel forums, because it's not Oracle-related.

Regards, Dima

http://seminihin.narod.ru

 

[SantaMufasa]

Jax,

Here is my GenASCII.sql script that writes an Excel-loadable, comma-and-double-quote separated flat file from any table in the schema to which you are connected:

REM **************************************************************
REM David L. Hunt (file author) distributes this and other 
REM files/scripts for educational purposes only, to illustrate the 
REM use or application of various computing techniques. Neither the 
REM author nor Dasages, LLC, makes any warranty regarding this 
REM script's fitness for any industrial application or purpose nor is 
REM there any claim that this or any similarly-distributed scripts 
REM are error free or should be used for any purpose other than
REM illustration.
REM **************************************************************
set echo off
accept x prompt "Enter the table to ASCII: "
accept y prompt "Enter the flat file to write: "
prompt
prompt Following output is generated script that writes text output from table "&x"
prompt 
set verify off
set feedback off
set heading off
set pagesize 0
set linesize 32767
set trimout on
set trimspool on
spool temp.sql
select 'set echo off' from dual;
select 'set feedback off' from dual;
select 'set heading off' from dual;
select 'set pagesize 0' from dual;
select 'spool &y' from dual;
select 'select ' from dual;
select decode (rownum,1,'''"''||','||'',"''||') || column_name || '|| ''"''' 
  from user_tab_columns
 where table_name = upper('&x');
select 'from &x' from dual;
select ';' from dual;
select 'spool off' from dual;
select 'set feedback on' from dual;
select 'set heading on' from dual;
select 'set pagesize 20' from dual;
spool off
prompt
prompt Following is text output written to file "&y"
prompt
@temp
set echo off
prompt
prompt wrote file "&y"
prompt

If you want to test the code, be sure to copy and paste the code to a script (such as GenASCII.sql), then run the script from the SQL*Plus prompt: "@GenASCII".

You will need to figure out the logistics of running such a script from VB6.

Let us know if this is helpful.

Mufasa
(aka Dave of Sandy, Utah, USA @ 10:40 (03Dec03) GMT, 03:40 (03Dec03) Mountain Time)

 

[JaxtheDog]

Thank you for the replies. I will try the CSV routine, and I will also ask the VB6 forum for advice. I really appreciate your help.

Jax

 

[JaxtheDog]

Mufasa

I tried your ascii generator, thank you. I did get it to work, however, I think there was a line missing (or I didn't understand it) just before or after the decode select. When I ran it from sql3.3 I got a partial table. I have modified it and have it running in VB.

Thanks!

Jax