Home » SQL & PL/SQL » Client Tools » Export to Excel by each group (SQL/Oracle)
Export to Excel by each group [message #598760] Thu, 17 October 2013 09:02 Go to next message
epr55
Messages: 12
Registered: May 2011
Location: PR
Junior Member
Hi

I have to export the results of a query to an Excel spreadsheet. Easy enough; however it is that I have to do it for each group. What I want to do is to be able to save it in a file that has the group number.

This is the query
Lets say that I have the groups in the valiable RegionID: 11213, 21345 and 6537


@export on;
@export set filename="C:\11213\IP_claims_11213_.xls"
select * from mytable
where RegionID=11213;


How I can make it that I do not have to manually change the folder name in the direction and the where statement for each RegionID?

icon3.gif  Re: Export to Excel by each group [message #598764 is a reply to message #598760] Thu, 17 October 2013 09:15 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It is easy to dynamically generate a script, like this:
SQL> set head off
SQL> set feed off
SQL> select 'spool dump_'||deptno||'
  2  select * from emp where deptno='||deptno||' order by ename;'||'
  3  spool off'
  4  from dept
  5  order by deptno
  6  /
spool dump_10
select * from emp where deptno=10 order by ename;
spool off
spool dump_20
select * from emp where deptno=20 order by ename;
spool off
spool dump_30
select * from emp where deptno=30 order by ename;
spool off
spool dump_40
select * from emp where deptno=40 order by ename;
spool off

You spool this query in a file and you just have to execute the generated file.
Re: Export to Excel by each group [message #598765 is a reply to message #598764] Thu, 17 October 2013 09:24 Go to previous messageGo to next message
epr55
Messages: 12
Registered: May 2011
Location: PR
Junior Member
Not really what I am trying to achieve...
Thanks for the very fast attention, very appreciated.
Re: Export to Excel by each group [message #598766 is a reply to message #598760] Thu, 17 October 2013 09:40 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2190
Registered: May 2013
Location: World Wide on the Web
Senior Member
epr55 wrote on Thu, 17 October 2013 19:32
@export set filename="C:\11213\IP_claims_11213_.xls"

How I can make it that I do not have to manually change the folder name in the direction and the where statement for each RegionID?


You will have to either pass the RegionIds as parameters or assign it dynamically.

You can write an anonymous block, in a FOR loop, generate the SPOOL script.
FOR i IN (SELECT DISTINCT regionid FROM mytable) LOOP
 << prepare you SPOOL script here, the regionid folder would be i.regionid >>
 << like, select 'filename=C:\'||i.regionid||'\IP_claims_'||i.regionid||'_.xls' ....so on>>
END LOOP;


Re: Export to Excel by each group [message #598771 is a reply to message #598766] Thu, 17 October 2013 10:10 Go to previous messageGo to next message
epr55
Messages: 12
Registered: May 2011
Location: PR
Junior Member
I am at a very beginner level on this. Do not know about cursors and looping Sad


If you can write down the code for a dummy like me.
Re: Export to Excel by each group [message #598772 is a reply to message #598771] Thu, 17 October 2013 10:29 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2190
Registered: May 2013
Location: World Wide on the Web
Senior Member
epr55 wrote on Thu, 17 October 2013 20:40
I am at a very beginner level on this. Do not know about cursors and looping Sad


If you can write down the code for a dummy like me.


SQL> set serveroutput on;
SQL> 
SQL> DROP table mytable;
 
Table dropped
SQL> CREATE TABLE mytable(regionid NUMBER);
 
Table created
SQL> INSERT INTO mytable VALUES(11213);
 
1 row inserted
SQL> INSERT INTO mytable VALUES(21345);
 
1 row inserted
SQL> INSERT INTO mytable VALUES(6537);
 
1 row inserted
SQL> COMMIT;
 
Commit complete
SQL> DECLARE
  2     VAR_SQL VARCHAR2(4000);
  3  BEGIN
  4     FOR I IN (SELECT REGIONID FROM MYTABLE) LOOP
  5        VAR_SQL := 'export on;' || CHR(10) || 'export set filename="C:\' ||
  6                   I.REGIONID || '\IP_claims_' || I.REGIONID || '_.xls"' ||
  7                   CHR(10) || 'select * from mytable where regionid=' ||
  8                   I.REGIONID || ';';
  9        DBMS_OUTPUT.PUT_LINE(VAR_SQL||CHR(10));
 10     END LOOP;
 11     DBMS_OUTPUT.PUT_LINE(VAR_SQL);
 12  END;
 13  /
 
export on;
export set filename="C:\11213\IP_claims_11213_.xls"
select * from mytable where regionid=11213;

export on;
export set filename="C:\21345\IP_claims_21345_.xls"
select * from mytable where regionid=21345;

export on;
export set filename="C:\6537\IP_claims_6537_.xls"
select * from mytable where regionid=6537;

export on;
export set filename="C:\6537\IP_claims_6537_.xls"
select * from mytable where regionid=6537;
 
PL/SQL procedure successfully completed


Regards,
Lalit
icon2.gif  Re: Export to Excel by each group [message #598774 is a reply to message #598772] Thu, 17 October 2013 10:41 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Or with my way:
SQL> select 'export on;'||'
  2  export set filename="C:\'||regionid||'\IP_claims_'||regionid||'_.xls"
  3  select * from mytable where regionid='||regionid||';'||'
  4  '
  5  from mytable
  6  /
export on;
export set filename="C:\11213\IP_claims_11213_.xls"
select * from mytable where regionid=11213;
export on;
export set filename="C:\21345\IP_claims_21345_.xls"
select * from mytable where regionid=21345;
export on;
export set filename="C:\6537\IP_claims_6537_.xls"
select * from mytable where regionid=6537;

icon14.gif  Re: Export to Excel by each group [message #598788 is a reply to message #598774] Thu, 17 October 2013 14:19 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2190
Registered: May 2013
Location: World Wide on the Web
Senior Member
Michel Cadot wrote on Thu, 17 October 2013 21:11

Or with my way:


Of course, cannot deny to the proved and obvious fact that SQL is always better than PL/SQL.
Re: Export to Excel by each group [message #598803 is a reply to message #598788] Thu, 17 October 2013 15:55 Go to previous messageGo to next message
epr55
Messages: 12
Registered: May 2011
Location: PR
Junior Member
I was informed that these commands are performed directly from ORACLE , which I do not have access. Is there another option?
Re: Export to Excel by each group [message #598805 is a reply to message #598803] Thu, 17 October 2013 15:59 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2190
Registered: May 2013
Location: World Wide on the Web
Senior Member
epr55 wrote on Fri, 18 October 2013 02:25
I was informed that these commands are performed directly from ORACLE , which I do not have access. Is there another option?


Common man, you mentioned in the subject that you use Oracle DB, BTW you did not mention the version and the OS too.

Anyway, how can you access Oracle DB without using Oracle commands? What exactly are you doind?
Re: Export to Excel by each group [message #598819 is a reply to message #598805] Thu, 17 October 2013 16:48 Go to previous messageGo to next message
epr55
Messages: 12
Registered: May 2011
Location: PR
Junior Member
I am a girl Smile sorry for my lack of knowlegde on this that make it difficult for you guys. I have tool called DB Visualizer, which connects to to an ORACLE database and there write SQL statements.
Re: Export to Excel by each group [message #598821 is a reply to message #598819] Thu, 17 October 2013 16:53 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2190
Registered: May 2013
Location: World Wide on the Web
Senior Member
epr55 wrote on Fri, 18 October 2013 03:18
I am a girl Smile


Apologies, I didn't know that you are a girl, when I addressed you in my previous post.
Re: Export to Excel by each group [message #598826 is a reply to message #598821] Thu, 17 October 2013 17:22 Go to previous messageGo to next message
epr55
Messages: 12
Registered: May 2011
Location: PR
Junior Member
Thanks, no problem Smile I felt sad I may have to go through each one to create the files, and the worst part is that is monthly!!
Re: Export to Excel by each group [message #598827 is a reply to message #598826] Thu, 17 October 2013 17:27 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2190
Registered: May 2013
Location: World Wide on the Web
Senior Member
epr55 wrote on Fri, 18 October 2013 03:52
I felt sad I may have to go through each one to create the files, and the worst part is that is monthly!!


1. You mean the data is stored for all the months together in the same table?
2. Do you have RANGE PARTITION on monthly basis for the table to store the monthly data?

Regards,
Lalit
Re: Export to Excel by each group [message #598830 is a reply to message #598827] Thu, 17 October 2013 17:48 Go to previous messageGo to next message
epr55
Messages: 12
Registered: May 2011
Location: PR
Junior Member
I update the table based on monthly analysis and have to provide to each group (contract) the detail results for one particular month. Since there are 27 groups , there are different files I have to provide to them, so it makes is very time consuming to create the files.

For this, specifically I already created folders for each of the group IDs.
The task is to save in the folder the results for only the records for the group and save the file with a specic name:
C:\GroupID\IP_claims_GroupID_.xls"

the simple query is select * from mytable where GroupID=11321;
...select * from mytable where GroupID=62243;
and so on


Re: Export to Excel by each group [message #598831 is a reply to message #598830] Thu, 17 October 2013 17:58 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2190
Registered: May 2013
Location: World Wide on the Web
Senior Member
Look, it is not that placing the files in respective folder that consumes the time. It is the data retrieval that consumes the time.

1. Do you have the necessary indexes created?
2. Please post the number of records counts that you want to retrieve, and that the table has.
3. Is the table partitioned on monthly basis, I already pointed it in my previous post.

So finally, it is not a SQL question, rather, it's a performance tuning question, am I right?
Re: Export to Excel by each group [message #598862 is a reply to message #598831] Fri, 18 October 2013 08:13 Go to previous message
epr55
Messages: 12
Registered: May 2011
Location: PR
Junior Member
What I mean of taking time for me is to have to change the code for each group that I have to run the query and change the name of the location and name of the file, since there are several reports that follows the same process and I have 28 groups.


1. Yes, the table has indexes and running the query for each group is very fast.
2.Number of records as I explained is not an issue, it is the times I have to change the query just to change the filter and name of the file.
3.The table has a field to identify the month. To run it for a particular month, I specify it in a 'where' statement.

Well it is an SQL question, I want to be able to not have to change the query to run it for each group. I want that automatically, it goes group by group and export it to the folder and name it as described above. The improvement in performance is for me, because if I do not have to do it one by one, I can finish faster. Wink
Previous Topic: SQL problem
Next Topic: dbms_output issue
Goto Forum:
  


Current Time: Fri Aug 29 22:07:17 CDT 2014

Total time taken to generate the page: 0.15130 seconds