I am using the following script to send output of a query to excel file.

Set markup html on
SET LINESIZE 2000
Spool test1.xls
Select * from CMSLOANISSUETRANSACTION;
Spool off


The entire row is converted into a single cell. How to send each column value into a seperate cell while sending output to excel file?

Dear krishnaindia2007,

Good Morning

Try these steps:

1) Spool test.txt
Select * from CMSLOANISSUETRANSACTION;
Spool off;
2) Open MS-Excel
3) Open the test.txt file in Excel.
4) A wizard will be opened with two options
Specify from where to start, i mean, specify row number in the option Start import at row and click next.
5) Use the break line as required and click finish.

I hope this will help you.

Have a pleasant time.

Dear krishnaindia2007,

Good Morning

Try these steps:

1) Spool test.txt
Select * from CMSLOANISSUETRANSACTION;
Spool off;
2) Open MS-Excel
3) Open the test.txt file in Excel.
4) A wizard will be opened with two options
Specify from where to start, i mean, specify row number in the option Start import at row and click next.
5) Use the break line as required and click finish.

I hope this will help you.

Have a pleasant time.

Dear krishnaindia2007,

Good Morning

Try these steps:

1) Spool test.txt
Select * from CMSLOANISSUETRANSACTION;
Spool off;
2) Open MS-Excel
3) Open the test.txt file in Excel.
4) A wizard will be opened with two options
Specify from where to start, i mean, specify row number in the option Start import at row and click next.
5) Use the break line as required and click finish.

I hope this will help you.

Have a pleasant time.

Still it is importing entire row into a signle cell not each column into a seperate cell.

We can use plsql script to load oracle data to excel with out using any tools
This is a sample code use this...
PROCEDURE EXCEL_FILE IS
cursor dtl is select * from accounts_icd order by auth_dt,ich_ven_cd;
row_num number;
col_num number;
cell_val number;
app_acc OLE2.OBJ_TYPE;
dbs OLE2.OBJ_TYPE;
dc OLE2.OBJ_TYPE;
args_acc OLE2.OBJ_TYPE;
app OLE2.OBJ_TYPE;
args OLE2.LIST_TYPE;
args2 OLE2.LIST_TYPE;
ws OLE2.OBJ_TYPE;
wb OLE2.OBJ_TYPE;
wss OLE2.OBJ_TYPE;
wbs OLE2.OBJ_TYPE;
cell OLE2.OBJ_TYPE;
ctr1 NUMBER := 0;
ctr2 NUMBER := 0;
ctr3 PLS_INTEGER := 0; --NUMBER := 0;
m_row number:=1;
m_len number:=0;
m_flag number:=0;
Begin

---------------- Initialise Excel

app := OLE2.CREATE_OBJ('Excel.Application');
OLE2.SET_PROPERTY(app, 'Visible','True');
wbs := OLE2.GET_OBJ_PROPERTY(app,'Workbooks');
wb := OLE2.INVOKE_OBJ(wbs,'Add');
wss := OLE2.GET_OBJ_PROPERTY(app,'Worksheets');
ws := OLE2.INVOKE_OBJ(wss,'Add');

---
For I in 1..2 loop
args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, m_row); --- Row
OLE2.ADD_ARG(args, I); --- Column
cell := OLE2.GET_OBJ_PROPERTY(ws,'Cells', args);
OLE2.DESTROY_ARGLIST(args);
if I=1 then
OLE2.SET_PROPERTY(cell, 'Value','Auth. Date');
elsif I=2 then
OLE2.SET_PROPERTY(cell, 'Value','ICD Doc.No');
end if;
OLE2.RELEASE_OBJ(cell);
end loop;
--- Heading

-- Data

For J in Dtl loop

For I in 1..2 loop

-- Repeat Row
args := OLE2.CREATE_ARGLIST;

OLE2.ADD_ARG(args, m_row); --- Row
OLE2.ADD_ARG(args, I); --- Column

cell := OLE2.GET_OBJ_PROPERTY(ws,'Cells', args);
OLE2.DESTROY_ARGLIST(args);

if I=1 then
OLE2.SET_PROPERTY(cell, 'Value',J.AUTH_DT);
elsif I=2 then
OLE2.SET_PROPERTY(cell, 'Value',J.ICD_DOC_NO);
end if
end loop
end loop
---- End
OLE2.RELEASE_OBJ(wbs);
OLE2.RELEASE_OBJ(ws);
OLE2.RELEASE_OBJ(wbs);
OLE2.RELEASE_OBJ(wb);
OLE2.RELEASE_OBJ(app);
END;

Dear Krishnaindia2007,

Refer the attachment and try again

Attached Files

1.doc (55.0 KB, 10 views)