Load Employees in Siebel : Reading Data from Excel File

Hello All,

In my previous post about Loading Employees in Siebel i described the process through CSV file and IO based approach.

In this post lets achieve the requirement with excel file and business service.

Prerequisite,

• Excel application installed on the machine (server), so this approach works only in Windows Environment.
• Accessible location where we need to place the Excel file

Formating the Excel File :

Here we are entering data from 2nd Row.

Now we need to write a business service that reads data row by row and insert data in siebel. Since we are reading data in terms of rows and columns, so 2nd Row 2nd column will fetch value Rahul , and 3rd Row 2nd column will fetch Ankit and this will be our base of iterating through the excel sheet.

Final Code :

• Reading Excel and Initializing BO and BO

  var ExcelApp = COMCreateObject("Excel.Application"); 

  ExcelApp.Workbooks.Open("C:/siebel/LoadEmployee.xlsx"); 

  ExcelApp.Visible = false; 

  var oBOEmp = TheApplication().GetBusObject("Employee"); 

  var oBCEmp = oBOLdVal.GetBusComp("Employee"); 

• Getting value from Excel columns 

 while(ExcelApp.ActiveSheet.Cells(i,1).Value!= null) 

   { 

// Give the fields here 

// Employee Details

   var LastName = ExcelApp.ActiveSheet.Cells(i,1).Value; 

   var FirstName = ExcelApp.ActiveSheet.Cells(i,2).Value;   

   var Email = ExcelApp.ActiveSheet.Cells(i,3).Value;  

   var PhoneNumber = ExcelApp.ActiveSheet.Cells(i,4).Value; 

   var LoginName = ExcelApp.ActiveSheet.Cells(i,5).Value; 

   //Position Details

   var Position = ExcelApp.ActiveSheet.Cells(i,6).Value; 

   

   //Responsibility Details

   var Responsibility = ExcelApp.ActiveSheet.Cells(i,7).Value;  

}

• Setting Fields Value and Invoking New Record     

   NewRecord(NewAfter); 

   SetFieldValue("Last Name",LastName); 

   SetFieldValue("First Name",FirstName);  

   SetFieldValue("Email",Email); 

   SetFieldValue("Phone Number",PhoneNumber);

   SetFieldValue("Login Name",LoginName); 

• Closing Excel File 

 ExcelApp.Quit(); 

 ExcelApp = null; 

Refer exact code and sample file here .. You can mail me for any comments or suggestions.

<<Previous Post >>

Read CSV File in Siebel : Working with Multiple Records

Hello All,

In Previous post we created a process to read CSV file and insert Employee data in Siebel Application. The data was simple with employee details, a primary position and a primary responsibility.

Suppose we have a case where we have to insert multiple positions or multiple responsibility with the record, what shall be the changes in that case in our CSV file or is there any other approach for this ?

Well we can handle the above case with our old CSV itself, we just need to make multiple entries for the records as show in screenshot below

here for the same employee data we are making multiple entries of Position and Responsibility and will use Upsert method of EAI Siebel Adapter business service.

For the first row record will be inserted and for the consecutive rows record will be updated.

Second Approach,

Using XSLT Transformation, we can write a XSL file that formats the xml (first convet CSV to XML) in the desired format to group the repeating records. Once formatted the XML can be converted to IO hierarchy and passed to EAI Siebel Adapter.

Both approach are good and depends on users comfort and skill set as second one require good XSL knowledge whereas first one just need data duplication.


for exact code you can mail me ...Happy Learning

<<Previous Post>>                                      <<Next Post>>

Exploring Siebel Vanila Business service : PRM ANI Utility Service

Hello All,

Siebel Application provides a lot of vanilla business services which make it very easy to map any type of requirement/process in the system.

Recently i was working on a Outbound web service where in i needed to provide Input as Hierarchy format based on External IO. So i needed sample IO structure and pass values in the hierarchy.

PRM ANI Utility Service's CreateEmptyPropSet method can be used for this purpose.

This BS takes in Input IO name and using Create Empty Hierarchy method generates empty container hierarchy.

Passing IO Name to Get Blank Hierarchy

Output Blank Hierarchy 

Now Since we have the Input hierarchy container, only thing left is to add nodes in the Input hierarchy (click to see detailed description add elements in the Hierarchy) for this Echo Method of Workflow Utility BS can be used.

value can be set by traversing the hierarchy using .(dot) operator
e.g. IOHierarchy.ListOfRequestReceived.RequestReceived.ACTIVITY_TYPE

Final Hierarchy after setting node values

Now this Hierarchy can be fed as input to Proxy Business service to get the required result.

Load Employees in Siebel From CSV FIle : Read CSV File in Siebel

Hello All,

Recently i got a requirement where i had to load Employees in Siebel Application from CSV/Excel file.

There are various approach to achieve this.

1. Create a Business Service and Read excel file using, click here to view example
 COMCreateObject(Excel.Application), it has limitation that it is supported only with windows

2. Create a CSV file a read data using clib() functons like  
   Clib.fputs(sHello, oFile);
   Clib.rewind(oFile);
   Clib.fgets (nLen, sHello);

3. Using Macros in Excel which in turn calls VB Script and loads data 

4. And my favourite :-), Read CSV File Business Service.

I will explain Read CSV File and its use in this post.

Business Service : Read CSV File

Method Name	Comment
CsvToPropSet	Creates an Hierarchy from CSV file.
PropSetToXml	Converts a Hierarchy to XML format.

Input/Output Parameters 

Method Name	Comment
CsvFileName	Name and location of File which needs to be loaded eg. C:\Siebel\8.1\Tools_1\TEMP\Employee.csv
DeleteFile	Deletes a file after reading
MaxErrors	Max errors allowed before error out
NumRecords	In (Out): Number of records allowed (processed).
SiebelMessage	Output Hierarchy converted from CSV file structure

Working Example:

Business Case : Need to Load Employees in Siebel from provided CSV file.

Solution : Workflow with following steps 

1. Prepare CSV file for the data format, 

# IO Name  #"IC.Field","IC.Field1","IC.Field2","IC.Field3" Field Value,Field1 Value,Field2 Value,Field3 Value

CSV file should be in the format to include IO name in Header, IC and Fields name in sub header with values following from next line.
Note: IO and IC fields must be properly mapped to CSV else workflow will through data error

2. Read CSV file ,Step to read Data from CSV

Provide the location of CSV file and call Business service, refer screenshot below for more details

3. EAI Siebel Adapter, Step to insert data in Siebel 

Pass the output of Previous step to EAI Siebel Adapter and it will insert the record in Siebel Database.

Any Data Validation/Mapping can be done before this step as this step creates the Employee in system.

for more details please refer, Import Marketing Contact vanilla workflow in Siebel tools.
For sample CSV format you can mail me...Happy Learning


<<Next Post>>                      <<Load Multiple data using CSV method>>