Exporting the data from Siebel Database to CSV file

This article has been contributed by Prashant Jha. In this article he explains scriptless solution for a seemingly complex requirement and demonstrates a great example of Siebel EAI in action.


Here is a requirement wherein we need to export data from Siebel DB to an external system in a CSV file Format. As soon as we hear this the first thing that comes into our mind is SQL developer. We can easily run a select query and extract the records and then export in any format we want. But what if the client wants only Siebel ways to do this? For exporting data into CSV file Siebel does not provide any Out-of-Box Business Service which can complete my task. To accomplish this requirement we have two ways:

  1. Using EBC to populate data into an external table from which we can schedule a job which exports data into CSV (Almost the SQL developer way. Just that we are not extracting directly from Siebel tables.)
  2. Using a Workflow that employs Out Of Box (OOB) business service to accomplish this.

Below is the screenshot and detail of the workflow created to accomplish the requirement through second approach.

clip_image002

Step 1: Query Page

Business Service: EAI Siebel Adapter

Business Service Method: QueryPage

Input Arguments:

Input Argument Type Value Property Name
NewQuery Process Property New Query
OutputIntObjectName Literal <IO Name>
PageSize Literal 20
SearchSpec Literal <Search Specification>
StartRowNum Process Property Iteration Counter

Output Arguments:

Property Name Type Value Output Argument
Number of Output Objects Output Argument NumOutputObjects
Last Record Output Argument LastPage
Siebel Message Output Argument SiebelMessage
New Query Literal false

 

Step 2: EAI Conversion

Business Service: EAI Integration Object to XML Hierarchy Converter

Business Service Method: IntObjHierToXMLHier

Input Arguments:

Input Argument Type Value Property Name
SiebelMessage Process Property Siebel Message

Output Arguments:

Property Name Type Value Output Argument
XML Hierarchy Output Argument XMLHierarchy

 

Step 3: Write to XML

Business Service: EAI XML Write to File

Business Service Method: WriteXMLHier

Input Arguments:

Input Argument Type Value Property Name
FileName Literal <FileName>
XMLHierarchy Process Property XML Hierarchy

 

Step 4: Convert to CSV

Business Service: EAI XSLT Service

Business Service Method: Transform

Input Arguments:

Input Argument Type Value Property Name
XMLFileLocation Literal <FileName>
XSLTFileLocation Literal <FileName>

Output Arguments:

Property Name Type Value Output Argument
XML Hierarchy1 Output Argument <Value>

 

Step 5: Write CSV

Business Service: EAI File Transport

Business Service Method: Send

Input Arguments:

Input Argument Type Value Property Name
<Value> Process Property XML Hierarchy1
AppendToFile Literal True
FileName Literal <File Name>

 

Step 6: Decision

Condition: If (Last Record== ‘true’) then END else Step 7

 

Step 7: Increment Counter

Business Service: Workflow Utilities

Business Service Method: Echo

Output Arguments:

Property Name Type Value Output Argument
Iteration Counter Expression [&Iteration Counter] + [&Number of Output Objects]

Below is the XSLT used for the modification of an XML file to a pipe delimited CSV file

XML2CSV-Contact

Note: Please rename the file extension from txt to xsl.

For achieving this we need to have a BC created which contains all the fields that we require in the CSV export. Create a Business object and attach this BC to the newly created BO.

Now Build an Integration Object based on the newly created BO. This IO will contain the Integration Component which is based on the New BC that we created.

In the EAI Siebel Adapter QueryPage Method mention this IO.

15 Responses to Exporting the data from Siebel Database to CSV file

  1. Good one, Neel! I was working on a similar problem (write a property set to CSV) and the really hard part is in denormalizing parent-child hierarchies (e.g. repeat the account name for each contact etc.). The XSL seems to support only “flat” BC data . Cheers, @lex.

    • @alex:disqus The formulation of xsl is the main roadblock. The one i posted is crafted jst to support an export of flat BC for client requirement. If we have an XSL converter we can very well play with any type of heirarchy structure. prashant Jha

  2. Good one, Neel! I was working on a similar problem (write a property set to CSV) and the really hard part is in denormalizing parent-child hierarchies (e.g. repeat the account name for each contact etc.). The XSL seems to support only “flat” BC data . Cheers, @lex.

  3. Good post. but is there an alternative way to convert it to CSV as we dont have the methods XMLFileLocation, XSLTFileLocation under EAI XSLT Service for our version of Siebel ie 7.8 efinance. Thanks Karthik

  4. hi guys, i’m having trouble in the transform step, the output csv still look like an xml and the xsl seems not to be taking effect, i tried manually adding the xsl-stylesheet tag in my xml and it’s working so the xsl shouldn’t be the issue. is there anything that i may have missed? your help is very much appreciated

  5. Hi, If anybody could help me know if there is a difference between a CSV exported from Siebel and a Normal CSV created file from notepad++?

  6. HI.. i successfully created a file… but when i am opening the file and clicking the option “save” then only saving as .csv file….. is there a way to do the work of save option through siebel…???

  7. when i am reading a line of generated csv file from java code using readline function… it is giving spaces between all the letters…. how to get words without spaces????

  8. hi.. i got the solution for my problem….
    1. I created xml file using business service-EAI XML Converter, with method name-IntObjHierToXMLDoc
    2. I used xsl buffer argument of XSLT business service instead of reading xsl stylesheet from a file….
    3. Use XMLCharEncoding argument of IntObjHierToXMLDoc for proper encoding for file going to generate using Business Service: EAI File Transport……Business Service Method: Send

Leave a Reply

Contribute