CSV Anyone?

CSV, a dead format? With today's abilities to generate Excel output (Im working on some posts for the shiny new Excel templates) and the sophistication of the eText outputs, do you really need CSV?

I guess so seeing as it was added after the base product was released as an enhancement. But I'd be interested in hearing use cases?

I used to think the same of text output. But now Im working in the public sector where older technologies abound I can see a use for text output. Its also used as a machine readable format, etc. BIP still does not support a text format from its RTF templates but its very doable using XSL templates.

Back to CSV, I noticed an enhancement in the big list of new stuff in the 10.1.3.4.1 rollup. You can now generate CSV from structured data ie a data template or a SQL XML query. This piqued my interest so I ran off a data template to CSV. Its interesting, in a geeky kinda way, I guess Im that way inclined.

Here's my data structure:

<EMPLOYEES>
 <LIST_G_DEPT>
 <G_DEPT>
  <DEPARTMENT_ID>10</DEPARTMENT_ID>
  <DEPARTMENT_NAME>Administration</DEPARTMENT_NAME>
  <LIST_G_EMP>
   <G_EMP>
     <EMPLOYEE_ID>200</EMPLOYEE_ID>
     <EMP_NAME>Jennifer Whalen</EMP_NAME>
     <EMAIL>JWHALEN</EMAIL>
     <PHONE_NUMBER>515.123.4444</PHONE_NUMBER>
     <HIRE_DATE>1987-09-17T00:00:00.000-06:00</HIRE_DATE>
     <SALARY>4400</SALARY>
    </G_EMP>
   </LIST_G_EMP>
  <TOTAL_EMPS>1</TOTAL_EMPS>
  <TOTAL_SALARY>4400</TOTAL_SALARY>
  <AVG_SALARY>4400</AVG_SALARY>
  <MAX_SALARY>4400</MAX_SALARY>
  <MIN_SALARY>4400</MIN_SALARY>
 </G_DEPT>

Poor ol Jennifer, she needs some help in the Admin department :0)

Pushing this to CSV and BP completely flattens the data out so you get a completely denormalized data output in the CSV.

TOTAL_SALARY,DEPARTMENT_ID,DEPARTMENT_NAME,MIN_SALARY,AVG_SALARY,MAX_SALARY,EMPLOYEE_ID,SALARY,PHONE_NUMBER,HIRE_DATE,EMP_NAME,EMAIL,TOTAL_EMPS
4400,10,Administration,4400,4400,4400,200,4400,515.123.4444,1987-09-17T00:00:00.000-06:00,"Jennifer Whalen",JWHALEN,1
19000,20,Marketing,6000,9500,13000,201,13000,515.123.5555,1996-02-17T00:00:00.000-07:00,"Michael Hartstein",MHARTSTE,2
19000,20,Marketing,6000,9500,13000,202,6000,603.123.6666,1997-08-17T00:00:00.000-06:00,"Pat Fay",PFAY,2


Useful? Its a little confusing to start with but it is completely de-normalized so there is lots of repetition. But if it floats your boat, you got it!

Comments:

This post is great. Thank you for this post. I like these type of people who share knowledge with others, free of charge too ;)

Posted by Tom May on April 27, 2010 at 01:35 PM MDT #

Hi, It looks like in the 3 last Oracle BIP versions, CSV output needs requires data structure to represent your data rows with an XML TAG named ROW otherwise you get flatten data (which is good for MS Excel but not for CSV). For each of my reports I needed to specify ROW as group name in data template dataStructure xml subelement. Now BIP is not happy wet so additionnaly but independently it also looks like when your query does not contain data (eg. NO ROW TAG is generated) then CSV fails with an exception (Both for RUNNING and SCHEDULING report and BOTH Manually or using BIP SOAP API) It looks like "adapting the dataStructure puzzle" also resolves this 2nd problem. I hope Oracle will provide soon a Web GUI (in Flex ?) for DataTemplate Data Model Type...................... Thank you for your post (sorry for my bad English, I used to speak French)

Posted by Cedric on May 12, 2010 at 12:24 AM MDT #

can u please send me xls template?

Posted by guest on November 26, 2012 at 05:12 AM MST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Follow bipublisher on Twitter Find Us on Facebook BI Publisher Youtube ChannelDiscussion Forum

Join our BI Publisher community to get the most and keep updated with the latest news, How-to, Solutions! Share your feedback and let us hear your voice @bipublisher on Twitter, on our official Facebook page, and Youtube!

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today