Wednesday, April 12, 2006

Custom Export to CSV

It's been a while since I've updated my blog. I've been quite busy lately, and just have not had the time that I used to. We're expecting our 1st child in just a few short weeks now, so most of my free time has been spent learning Lamaze breathing, making the weekly run to Babies R Us, and relocating my office from the larger room upstairs to the smaller one downstairs - which I do happen to like MUCH more than I had anticipated. I have everything I need within a short walk - a bathroom, beer fridge, and 52" HD TV. I only need to go upstairs to eat and sleep now, but alas, this will all change soon...

Recently, I was asked if you could change the way Export to CSV in ApEx works. The short answer is, of course, no. But it's not too difficult to "roll your own" CSV export procedure.

Why would you want to do this? Well, the customer's requirement was to manipulate some data when the Export link was clicked, and then export it to CSV in a format which can be imported into PeopleSoft. With the built-in export functionality, this is simply not possible.

It's almost too easy. Simply create a Report Region with your favorite query. Add a button or link or anything which will get you to a blank page. On that page, add a PL/SQL process which will fire "On Load - Before Header" In the source of that process, use this code:


begin
-- Set the MIME type
owa_util.mime_header( 'application/octet', FALSE );
-- Set the name of the file
htp.p('Content-Disposition: attachment; filename="emp.csv"');
-- Close the HTTP Header
owa_util.http_header_close;
-- Loop through all rows in EMP
for x in (select e.ename, e.empno, d.dname
from emp e, dept d where e.deptno = d.deptno
and e.deptno like :P1_DEPTNO)
loop
-- Print out a portion of a row,
-- separated by commas and ended by a CR
htp.prn(x.ename ||','|| x.empno ||','||
x.dname || chr(13));
end loop;
-- Send an error code so that the
-- rest of the HTML does not render
htmldb_application.g_unrecoverable_error := true;
end;


You can download the export file here.

62 comments:

Mike Rife said...

Hey Scott. Congratulations on the future addition to your family. The addition changes your prespective on everything.

I tried your export to CSV. Unfortunately the EMP.CSV file that was created has no data in it.

Scott said...

Hey Scott. Congratulations on the future addition to your family. The addition changes your prespective on everything.

Thansk, Mike. I am so blissfully unaware of just how much change is coming. :)

I tried your export to CSV. Unfortunately the EMP.CSV file that was created has no data in it.

I fixed a small bug. It should be working now, and you can re-download the export file as well.

Thanks,

- Scott -

Aman Sharma said...

Hi Sir
My heartiest congrats to you and entire family.
PS:Sir,I noticed that your website is using the syntax of APEX in the URL.Is your site is now using APEx as its base?Just curious so asked.
best regards and once again congrats a bunch.
aman

Scott said...

My heartiest congrats to you and entire family.

Thanks for your kind wishes!

PS:Sir,I noticed that your website is using the syntax of APEX in the URL.Is your site is now using APEx as its base?Just curious so asked.

Yes, you are correct. Sort of. I have 2 sites - sumnertech.com and sumnertechnologies.com. Currently, my static site is on sumnertech.com, and sumnertechnologies.com is a hosted site from Revion.com, which is running ApEx. My plan is to move everyting to ApEx, and merge the two sites, but it's been hard getting the time to do that.

Thanks,

- Scott -

Aman Sharma said...

Hi sir
thanx alot.You know I always wanted to ask you that why your site is not an APEX but a static one?Glad that very soon,we all shall see the full blown APEX site running.
with best regards
aman

Anonymous said...

Hi Scott, i used your method to export a standard report to excel. It worked well. However i am facing a small problem. One of the report column is varchar2 type but sometime contains numreic value like '00123'. In the output excel such numeric values are truncated to '123'. Is there a fix for this??

Thanks and Regards
V

Scott said...

Oddly enough, I just ran into this problem myself. The trick is to enclose each "cell" in double-quotes.

Thus, change this line:

htp.prn(x.ename ||','|| x.empno ||','|| x.dname || chr(13));

To this:

htp.prn('"' || x.ename ||'","'|| x.empno ||'","'|| x.dname || '"' || chr(13));

Thanks,

- Scott -

Anonymous said...

I have the same problem as leading zeros being truncated say '00123' beacomes '123' when exported into excel. Enclosing double quotes didn't solve this. Any other ideas?

Anonymous said...

hi scott,

i have a question to the "custom export to csv"

how can a change your procedure in the way that I can said where the default place to save the file is

like C:\EXPORT_CSV\file_name.csv

is this possible

thanks for help

Scott said...

how can a change your procedure in the way that I can said where the default place to save the file is

Off the top of my head, I don't think you can, as it's a setting of the specfic browser, not the server.

Thanks,

- Scott -

Anonymous said...

Hi Scott,

How can we do if we want to export multiple csv files by pressing a button or a link. My data is in views (arround 50 views). I want to create multiple csv files the name I can use same as view name. I appreciate your help.

Thanks,
James.

Scott said...

How can we do if we want to export multiple csv files by pressing a button or a link. My data is in views (arround 50 views). I want to create multiple csv files the name I can use same as view name. I appreciate your help.

James - I'm not sure if this is possible, as once an HTTP stream is started & stopped, I'm not sure that you can easily - if at all - restart it.

Sorry that I don't have a better answer.

- Scott -

ImraneA said...

Hi Scott

Used your code - worked ok. Is it possible to manipulate excel within html e.g. adjust column size, align it etc.

ImraneA

Scott said...

Is it possible to manipulate excel within html e.g. adjust column size, align it etc

If you want to change the formatting, you'll probably have better luck with the OWA_SYLK Utility

Thanks,

- Scott -

Anonymous said...

Scott:

your posts on this blog and advice on APEX forum are invaluable. Thanks for your time, it helps us novices greatly! I have a question that is not 100% related to a "Custom Export to CSV."

We have an APEX application with mail merge and document management system used by 200+ employees (and rapidly expanding). Mail merge has been setup by exporting CSV file to a PC, and opening mail merge document stored as a blob. Works perfect.

That is, until PCs with Office 2007 started rolling in. Word 2007 (and 2003) are not using DDL technology, and CSV and TXT files cen not be used without going through entire mail merge setup every time a template is open.

So, my question is two-fold. Have any of your existing customers experienced this? What was their solution?

Second - can APEX export any other file format except for CSV (XML comes to mind). If not, will future releases provide export format options?

I may cross-post this message in APEX forum, if a solution can be found. Thanks in advance for any insight.

Vojin
vjanjic457081@comcast.net

Scott said...

your posts on this blog and advice on APEX forum are invaluable. Thanks for your time, it helps us novices greatly!

Thanks! Glad that you find it useful.

I may cross-post this message in APEX forum, if a solution can be found. Thanks in advance for any insight.

FYI - here is the OTN Forum link for this topic: http://forums.oracle.com/forums/thread.jspa?messageID=1648185

I have posted my comments there as well.

Thanks,

- Scott -

Anonymous said...

...and I replied on the OTN Forum (I've started that thread) as well... so let's move the discussion over there, as it's slightly off topic here.

Vojin

Anonymous said...

I too get an empty csv output. What was the bug fix?

Anonymous said...

Scott –
I’m having difficulties. I keep getting an empty file. I’m using a multi-select list as a parameter on the report. I created a hidden item and called it P1_HID_UG. I’m using a computation on page 1 to format the multi-select list so it can be used in an IN clause. The format of the value in the hidden item is (‘2’,’22’,’7’,’5’). I have confirmed this displaying the session information on Page 1.

The code for the CSV export is:

BEGIN
-- Set the MIME type
owa_util.mime_header( 'application/octet', FALSE );

-- Set the name of the file
htp.p('Content-Disposition: attachment; filename="na_mail_list.csv"');

-- Close the HTTP Header
owa_util.http_header_close;

-- Loop through all rows
FOR x IN (SELECT DISTINCT d.agent_number, g.user_group||' - '||ug.description "User Group",
d.name, d.address1, d.address2, d.city,
d.state, d.zip_code
FROM naclhi_distribution d, naclhi_groups g, naclhi_user_group ug
WHERE d.agent_number = g.agent_number
AND g.user_group = ug.user_group
AND g.user_group IN :P1_HID_UG
ORDER BY 2)

LOOP
-- Print out a portion of a row,
-- separated by commas and ended by a CR
htp.prn('"'|| x."User Group" ||'","'
|| x.agent_number ||'","'
|| x.name ||'","'
|| x.address1 ||'","'
|| x.address2 ||'","'
|| x.city ||'","'
|| x.state ||'","'
|| x.zip_code ||'"'||
chr(13));
END LOOP;

-- Send an error code so that the
-- rest of the HTML does not render
htmldb_application.g_unrecoverable_error := true;
END;

Yes there is actually data based on that query.
Can you see or do you know why I keep getting an empty file?

Thank you,
Joe

Scott said...

Does it work if you remove this line from the query:

AND g.user_group IN :P1_HID_UG

Thanks,

- Scott -

Anonymous said...

Scott,

Yes it does. It also works if I hard code the IN clause.

Thanks,
Joe

Scott said...

You may want to put a computation on that page which sets the value of :P1_HID_UG. Otherwise, when the report is run, there is no value for this item, and thus your query does not work.

Thanks,

- Scott -

Anonymous said...

There is. It's an After Submit computation on page 1. After the Submit button is pressed I checked the Session window and it had the correct string. Here's my computation:
'('||''''||REPLACE(:P1_USER_GROUP,':', ''''||','||'''')||''''||')'

Thanks,
Joe

Scott said...

If you could set it up on apex.oracle.com, I could take a look.

You can e-mail the credentials to me directly at scott at sumnertech dot com.

Thanks,

- Scott -

Anonymous said...

Scott,

An email was sent. I must also mention that I'm on version 2.0

Thank you very much!

Chandru Kengatte said...

Hi Scott,

Really its a superb solution to export report into CSV.

Regards
Chandru

Anonymous said...

Hi Scott,

Congratulations on your impending/recent family addition.

I have a number of 'PL/SQL function body returning SQL query' reports which I need to write some sort of custom csv export for. In order to implement your method it looks as though I need to know the report sql in order to write the custom export function; Is there any way I can write an entirely generic custom export function?

Cheers

Adam

Anonymous said...

How would you refresh the calling page? I update the records being exported with a systimestamp and want them removed from the list.

by_them@yahoo.com said...

How can I do the same for *.xls files? I need to create xls file the same way but with column width set for each column. Is it possible that way?

Thank you.

Scott said...

Have a look at this link:

http://deneskubicek.blogspot.com/2008/01/export-to-excel-1500-downloads.html

Thanks,

- Scott -

sahil said...

Hi scott, im using siebel analytics,
there are 3columns in my report EIN,OUC,EIN KEY and i want to export the report to excel.
But in excel i want only 2columns EIN,OUC to be shown when imported.
How can we do dis??

Thanks in advance

Scott said...

Sahil,

I'm not that familiar with Siebel; are you calling this procedure from a Siebel page, or from APEX?

- Scott -

Anonymous said...

Scott,

Earlier (11/16/2006) there was a post regarding a need to export multiple CSV files when a button is pressed, and you stated:

"I'm not sure if this is possible, as once an HTTP stream is started & stopped, I'm not sure that you can easily - if at all - restart it.

Sorry that I don't have a better answer."

I find myself with the need to export multiple CSV files. I tried your sample a couple of different ways, and even tried putting each export into a separate process - to no avail.

Have you since found a way to export several files from the same button process?

Heck, even a button for each would work for me.

Scott said...

I have not spent much time on trying to export multiple files.

You can, however, easily have multiple buttons call different export procedures. If you create a button with a URL redirect to the page which contains the code in this blog post, you can pass in a Request parameter, and then make each process conditional based on the value of the Request parameter.

Thus, button A calls process A, button B calls process B, and so on.

Hope this helps!

- Scott -

Anonymous said...

Scott,

I have used your code successfully for each of the exports I've created when each is run individually, and have tried all sorts of variations of repeating the code for 2 separate exports run at the same time (when I get that to work I will add the third).

I've tried with both exports in the same process; each export in its own process on the same page; and each export in its own process on two separate pages - one on the 'calling' page, the other in a separate page branched to via the Target Type 'Page in this Application'. All to no avail. Bummer.

In reading your response, I am wondering if it will work if I branch via a Target Type of 'URL'??? (Yes, that is a statement and a question rolled into one) If so, what is the difference between the two that would make the URL type work? Pretty much all of my branching is via 'Page in this Application'. When would one want to use 'URL'.


TIA,

Thomas

Scott said...

Not sure if that will work, but I suspect not. You'll never know until you try it, however! :)

The only other approach that I can think of would be to ZIP up the CSV files and then download the actual ZIP. Joel Kallman blogged on how to ZIP files within the database here: http://joelkallman.blogspot.com/2008/04/zip-it.html

You'll need to get out your Java hat for that (and have it installed in the database), but it's very well documented and if you follow his steps, you should have little difficulty.

Thanks,

- Scott -

Dave said...

Scott,

Fantastic, you should sell that code back to Oracle. Using the standard interactive report export download , my application was timing out (default 5 minute Apache timeout) before it produce the csv file. Now with that code you supplied it produces the file in around 30 seconds.

Regards
David

PS.
Now for the more difficult coding exercise - to make this generic so the for loop can take it's input from the proceding calling page.

Anonymous said...

Hi,
I have an interactive report which returns more than 65,000 records.
While trying to export to an csv file ,it displays only 65000 records.Is there anyway to diaplay more than 65000 records?

Thanks and Regards,

Anonymous said...

Hi Scott,

Did you have any luck figuring out the solution to the issue with loss of leading zeros when exporting to Excel? I also have this issue and couldn't fix with double quoting the fields.

Thank you,

Michelle

Sebastian said...

Hello Scott! Great export function you have developed! I have two questions that you can hopefully answer me:

First of all, is it possible to export data into an existing *xls worksheet using your attempt?

And is it also possible to specify the cell ranges for the exported values? For example starting at cell C4? Maybe you'll find the time to reply! Would be awesome!

Thanks,

Sebastian

Scott said...

Sebastian,

Unfortunately, no and no are the answers to your questions. This solution exports to CSV, not XLS. Excel can read CSV, but there is nothing specific about the CSV format.

Thanks,

- Scott -

Dr. Yes Sir said...

on writint the following error is coming
1 error has occurred
ORA-06550: line 3, column 1: PLS-00306: wrong number or types of arguments in call to 'MIME_HEADER' ORA-06550: line 3, column 1: PL/SQL: Statement ignored

charliebrown said...

Hi Scott,
I have an interactive report. I set a control break on one column, so APEX generates correctly more "sub-report".
I need to export into csv file every single "sub-report" separately.
Is it possible? Thank you very much,

Carlo.

Anonymous said...

I have the same requirement of exporting the control break output to csv...

Greg Dickson said...

Thankyou Scott!

This provided answers to a question I have been putting off for months.

Thanks again,
Greg

wim de wet said...

Hi Scott, great work, I have just implemented it.

However I was wondering how you can add headings for csv columns (first line) in the same export csv file ?

Is this possible, and if yes, how can this be done ?

Thanks, Wim.

Scott said...

Wim,

Just put another line before the LOOP that prints out the column headers. Something like this should work:


htp.prn('ENAME,EMPNO,DNAME' || chr(13));

Thanks,

- Scott -

Arijit said...

Hi Scott,

I'm having a problem with the Export to CSV option. Whenever someone clicks on the link, he/she gets an Error 404.

Unable to understand why...??
Off late I have also seen that we get too many unexplained Error 404 from APEX.

So any idea why the export to CSV doesn't work?

Do I need to explicitly specify a delimiter in the report options??

Regards,
Arijit

Deep Bhoj said...

Hi Scott

Thanks a lot. It solved one of the issue mine where I was facing the problem of downloading huge (more than 1 lakh)records in csv format using the normal FLOW_EXCEL_OUTPUT... method.

Anonymous said...

Hey Scott, is there anyway to add/embed the .CSV file into an email generated by APEX? Using APEX_MAIL.SEND and APEX_MAIL.ADD_ATTACHMENT?

Scott said...

You can easily do that. Rather than using htp.p to "print" the file, simply concatenate it into a cariable, and then pass that variable to APEX_MAIL.ADD_ATTACHMENT.

Thanks,

- Scott -

Jeremy said...

Hey Scott, I asked about adding the CSV as an email attachment. Can you take a look at my code and provide any insight into saving the csv as an attachment?

https://forums.oracle.com/forums/thread.jspa?messageID=10719624

mohannad said...

Hey Scott,

Thanks for your valuable post, I have a question when I am trying to export report that contains Arabic data it gives me unknown characters,I have tried the standard and yours methods,can you please advise on this?


Best regards
Mohannad

Anonymous said...

Thank for simple and working example. To create binary xlxs Excel file and format it through PL/SQL there is package called Ora_Excel www.oraexcel.com which has API to Excel formatting functions like, borders, colors, fonts, formulas and so on

Anonymous said...

Hey Scott i would like to know if its possible to produce the name of the file name.csv to have it as the name of a date. Im exporting a report that generates date for a specific date that an user can enter in a text fiel. I want the date from the text fiel to be the name of my csv file. Thanks

Scott said...

Yes - simply create a page or application item, and set that value to the name of the file that you want to use when downloading the file. Next, set the Filename attribute in the Report Export region to that item, using the &ITEM. notation. For example, if you used P1_FILENAME to store the filename, then enter &P1_FILENAME.

Thanks,

- Scott -

Anonymous said...

Hi Scott,

I had a requirement to add custom header row in a Export to Excel file in Apex.
Previously i successfully used your above code in 1 application which was in Apex version 4.1.0.00.32.

Recently i am facing problem to implement this in an application which have apex version 4.0.2.00.06. It is creating the .csv file and opening it in the browser instead of downloading it. Can you please help me out..

I am using the following code :-

begin
-- Set the MIME type
owa_util.mime_header( 'application/octet', FALSE );
-- Set the name of the file
htp.p('Content-Disposition: attachment; filename="emp.csv"');
-- Close the HTTP Header
owa_util.http_header_close;
htmldb_application.g_unrecoverable_error := true;
-- Loop through all rows in EMP
for x in (select e.ename, e.empno, d.dname
from emp e, dept d where e.deptno = d.deptno
)
loop
-- Print out a portion of a row,
-- separated by commas and ended by a CR
htp.prn(x.ename ||','|| x.empno ||','||
x.dname || chr(13));
end loop;
-- Send an error code so that the
-- rest of the HTML does not render
htmldb_application.g_unrecoverable_error := true;
end;

Or is there any other easy way to add 1 custom row when export to excel from apex?

Thanks
Subhojit

Scott said...

Subhojit,

If you need a custom header row, simple write that before the start of the LOOP.

Not sure why APEX 4.0 is opening the file in the browser vs. downloading it. Typically this is a browser setting that you can set to determine how to handle a specific file type.

Thanks,

- Scott -

Anonymous said...

Thanks for your reply.

Is there any other way to add 1 custom row when export to excel from apex?

Thanks
Subhojit

Scott said...

You can alter your SQL a bit to include a custom header row, but APEX should already display the column headers in the Export file.

This snipper will allow you to put an additional row at the top of a standard report:

SELECT
*
FROM
(
SELECT
TO_CHAR(empno) empno,
ename,
TO_CHAR(deptno) deptno,
1 id
FROM
emp
UNION
SELECT
'empno' empno,
'ename' ename,
'deptno' deptno,
0 id
FROM
dual
)
WHERE
1 = CASE
WHEN :REQUEST LIKE 'FLOW_EXCEL_OUTPUT%' AND id = 0 THEN 1
WHEN id = 1 THEN 1
END
ORDER BY
id

And this one will work for an Interactive Report (CSV only):

SELECT
*
FROM
(
SELECT
TO_CHAR(empno) empno,
ename,
TO_CHAR(deptno) deptno,
1 id
FROM
emp
UNION
SELECT
'empno' empno,
'ename' ename,
'deptno' deptno,
0 id
FROM
dual
)
WHERE
1 = CASE
WHEN :REQUEST LIKE 'CSV' AND id = 0 THEN 1
WHEN id = 1 THEN 1
END
ORDER BY
id

The issue here is that you'll have to convert all columns to VARCHARs, so that your custom row will be the same datatype as the data itself. You'll also want to set the ID column to not display when exported, as it serves no other purpose than to ensure that your custom row appears first.

Thanks,

- Scott -

Anonymous said...

Hi Scott,

Thanks for your suggestion. I successfully implemented that.

I have another apex classic report. I added custom column group header using jquery. Is there any way to include that column group header to export to excel.

Thanks
Subhojit

Scott said...

Subhojit,

It's possible, but would likely require a custom export procedure.

- Scott -