Printable View
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?
[QUOTE=krishnaindia2007;21852]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?[/QUOTE]
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[COLOR="Red"] Start import at row[/COLOR] and click next.
5) Use the break line as required and click finish.
I hope this will help you.
Have a pleasant time.
[QUOTE=krishnaindia2007;21852]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?[/QUOTE]
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[COLOR="Red"] Start import at row[/COLOR] 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[COLOR="Red"] Start import at row[/COLOR] 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;
[QUOTE=krishnaindia2007;21852]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?[/QUOTE]
Dear Krishnaindia2007,
Refer the attachment and try again