Load Employees in Siebel : Reading Data from Excel File using Clib Methods

Hello All,

In my previous posts about Loading Employees in Siebel i described the process through CSV file and IO based approach and Excel based approach. Now in this post lets try to load employees using Clib methods by reading a csv file.

In this post lets achieve the requirement using Clib methods like ,

• Clib.fgets() Method
• Clib.fopen() Method
• Clib.feof() Method

Prerequisite,

• Accessible location where we need to place the csv file
• BS should be Server Side as Clib methods works only on server side BS from IP13 onwards(click to know more)

Sample Code,

   var str;

   var usageAry = new Array();

   var filePath = "C:\\Employee.CSV";  //file path to place CSV file

   var oFile = Clib.fopen(filePath,"rt");  // Open file in read Mode

   var out;

   if(oFile != null)

   {

   while(!Clib.feof(oFile))                  //check for end of file

   {

   str = Clib.fgets(oFile);

   usageAry = str.split(",");               // Delimiter to separate data from the file

   var FirstName = usageAry[0];

   var LastName = usageAry[1];

   var PhoneNumber = usageAry[2];

   var Email = usageAry[3];

   var LoginName = usageAry[4];

   var Position = usageAry[5];

   var Responsibility = usageAry[6];

   }

Now we have values in variables which we can pass to siebel for inserting record, you can use below script.

//Initialize BC and Insert data

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

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

   with(oBCEmp) 

   { 

   ActivateField("Last Name"); ActivateField("First Name"); ActivateField("Email"); 

   ActivateField("Phone Number");  ActivateField("Login Name");   

   ClearToQuery();   

   InvokeMethod("SetAdminMode","TRUE"); 

   NewRecord(NewAfter);    

   SetFieldValue("Last Name",LastName); SetFieldValue("First Name",FirstName);       SetFieldValue("Email",Email); 

   SetFieldValue("Phone Number",PhoneNumber); SetFieldValue("Login Name",LoginName); 

   

   //Insert Responsibility Now 

   

var RespBC = GetMVGBusComp("Responsibility");

var AssocBC = RespBC.GetAssocBusComp();

//   TheApplication().RaiseErrorText(PrRecord + AssocBC);

  with(AssocBC)

  {

    SetViewMode(3);

ClearToQuery();

ActivateField("Name");

//search for the responsibility 

SetSearchSpec("Name", Responsibility);

ExecuteQuery(ForwardBackward);

var Respfound = FirstRecord();

if(Respfound) 

{

AssocBC.Associate(NewAfter);

} 

  } 

//Insert Position Now

var PostnBC = GetMVGBusComp("Position");

var AssocPostnBC = PostnBC.GetAssocBusComp();

  with(AssocPostnBC)

  {

    SetViewMode(3);

ClearToQuery();

ActivateField("Name");

//search for the position 

SetSearchSpec("Name", Position);

ExecuteQuery(ForwardBackward);

var PosFound = FirstRecord();

if(Respfound) 

{

AssocBC.Associate(NewAfter);

} 

  }

   WriteRecord(); //save record

   }

sample csv file looks like, 

your suggestions and comments are always welcome :-) ,for exact code you can mail me.

<< 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>>

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>>